Re: remote mysql is too slow
On Thu, 2012-04-05 at 08:13 +0100, J. Bakshi wrote:
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
` ` ` ` `
` ` ` ` ` `
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.