Re: remote mysql is too slow

["Karl E. Jorgensen" <karl.jorgensen@nice.com>]

Hi

On Thu, 2012-04-05 at 08:13 +0100, J. Bakshi wrote:
> Hello,
>
> I have been provided a muscular linux server to use as a Mysql server
> in our organization. The server is located just beside the web server
> and within the same network. This dedicated server has 8GB RAM, i5 processors
> and running mysql as service. No apache, php ..... nothing. All resources are
> dedicated to mysql only.
>
> Mysql version - mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64)
>
> The BIG hitch is; when we connect with this box the web sites become too slow.
> I have added the following at my.cnf under [mysqld] section
>
> ` ` ` ` `
> skip_external_locking
> skip_name_resolve
> skip_host_cach
>
> ` ` ` ` ` `
>
>
> The sql connection becomes little faster but still it is considerably
> slow; specially with such a muscular dedicated linx box just for Mysql.
> Is there anything else which I can add/configure to make the network latecy
> small or any such mechanism to make the query fast ?

Although you say "all resources are dedicated to mysql only", I guess they're not.

First step: run mysqltuner - this will give some rough recommendations which will usually point people in the right direction.  Beware that mysqltuner will always recommend setting innodb_buffer_pool_size big enough to keep all InnoDB tables in memory - which is usually overkill.

if "vmstat" reports that CPU usage is low, and disk IO is low, this points towards locking contention - which is entirely application dependent. Hope you haven't used MyISAM tables when you intended to use InnoDB tables

if "vmstat" reports high cpu usage, and low disk IO, then the database is probably in memory, and you need to look at one or more of:

• queries scanning lots of rows (e.g. missing indexes)
• for InnoDB tables: if innodb_buffer_pool is too small, but the server has "lots" of memory, the linux kernel will cache disk blocks rather than MySQL doing it. Which translates to high CPU load and lots of cache hits. But in this case it is more efficient to let MySQL do the caching rather than the kernel.

> The server has very little load as observer from top and iostat
> even during peak hours.
>
> Thanks