Re: remote mysql is too slow

[Juan Sierra Pons <juan@elsotanillo.net>]

2012/4/5 J. Bakshi <bakshi12@gmail.com>:
> 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 ?
>
> The server has very little load as observer from top and iostat
> even during peak hours.
>
> Thanks
>
>
> --
> To UNSUBSCRIBE, email to debian-user-REQUEST@lists.debian.org
> with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org
> Archive: [🔎] 20120405124336.73d6c311@shiva.selfip.org">http://lists.debian.org/[🔎] 20120405124336.73d6c311@shiva.selfip.org
>
Hi

My 5 cents: :)

         * Do you have anything in your mysql's slow query log?
entries look like (see below) and can point you in the right
direction. As maybe is a SQL bad programming problem, lock between
tables, etc
.
# Wed Apr  4 13:08:27 2012
# Query_time: 1.494178  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
use elsotani_XXXXX;
SET timestamp=1333566507;
CREATE TABLE IF NOT EXISTS `wp_translate_langs` (
                `id` int(11) NOT NULL AUTO_INCREMENT,
                `name` varchar(25) NOT NULL,
                `main` tinyint(1) NOT NULL default 0,
                `order` tinyint(1) NOT NULL default 0,
                `icon` varchar(20) NOT NULL default '',
                PRIMARY KEY  (`id`)
        )

         * Is your DNS server responding quickly? Just a quick check.
add the IPs & server names to /etc/hosts in mysql server and clients
and see if performance is better. I have seen that you have
"skip_name_resolve" and  "skip_host_cache" parameters set, but this
solves (workaround) only the servers' side not the clients

         * Anything wrong in your apache's logs?

Keep us informed about your progresses.

I hope it helps :)

Best regards

--------------------------------------------------------------------------------------
Juan Sierra Pons                                 juan@elsotanillo.net
Linux User Registered: #257202       http://www.elsotanillo.net
GPG key = 0xA110F4FE
Key Fingerprint = DF53 7415 0936 244E 9B00  6E66 E934 3406 A110 F4FE
--------------------------------------------------------------------------------------