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
>
>
<<attachment: face-smile.png>>
