Re: remote mysql is too slow
On Thu, 05 Apr 2012 11:36:38 +0100
"Karl E. Jorgensen" <karl.jorgensen@nice.com> wrote:
> 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.
>
I run the mysqltuner directly on the remote mysql server; and here is the
result
```````````````````````````````````````````````````
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-3-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 4777)
[--] Data in InnoDB tables: 3G (Tables: 5543)
[--] Data in MEMORY tables: 0B (Tables: 136)
[!!] Total fragmented tables: 5562
-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 23h 55m 27s (1M q [4.523 qps], 81K conn, TX: 23B, RX: 469M)
[--] Reads / Writes: 74% / 26%
[--] Total buffers: 2.2G global + 20.4M per thread (150 max threads)
[OK] Maximum possible memory usage: 5.2G (66% of installed RAM)
[OK] Slow queries: 2% (39K/1M)
[OK] Highest usage of available connections: 18% (28/150)
[OK] Key buffer size / total MyISAM indexes: 2.0G/268.5M
[!!] Key buffer hit rate: 78.2% (5M cached / 1M reads)
[OK] Query cache efficiency: 42.0% (327K cached / 778K selects)
[!!] Query cache prunes per day: 1993
[OK] Sorts requiring temporary tables: 0% (48 temp sorts / 9K sorts)
[!!] Temporary tables created on disk: 39% (91K on disk / 230K total)
[OK] Thread cache hit rate: 99% (28 created / 81K connections)
[!!] Table cache hit rate: 6% (16K open / 248K opened)
[OK] Open file limit used: 36% (11K/32K)
[OK] Table locks acquired immediately: 99% (695K immediate / 695K locks)
[!!] InnoDB data size / buffer pool: 3.6G/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 128M)
tmp_table_size (> 100M)
max_heap_table_size (> 100M)
table_cache (> 16000)
innodb_buffer_pool_size (>= 3G)
````````````````````````````````````````````````````
Please note, every day mysql optimization as well as repairing is done
by a cron at night. I have also tried with the suggestion multiple
times before but every time it gives some new suggestion. I have 8GB
physical RAM in this server and here is some statistics
`````````````````
# free -m
total used free shared buffers cached
Mem: 7986 7913 73 0 224 6133
-/+ buffers/cache: 1554 6431
Swap: 3813 0 3813
`````````````````````
And here is the vmstat result with 10 count and 5 sec delay
`````````````````````````````
# vmstat 5 10
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 0 56328 230440 6299676 0 0 25 74 6 6 0 0 98 2
0 0 0 55700 230440 6299744 0 0 0 22 226 272 0 0 99 1
0 0 0 55964 230440 6299856 0 0 0 314 348 388 0 0 94 5
0 0 0 55452 230440 6299956 0 0 0 61 304 364 0 0 97 2
0 1 0 55592 230440 6300424 0 0 0 271 199 257 0 0 96 4
0 0 0 54584 230440 6300908 0 0 0 338 342 428 0 0 92 8
0 0 0 54800 230440 6301072 0 0 0 77 119 133 0 0 98 2
0 0 0 53964 230440 6301532 0 0 0 617 267 327 0 0 95 4
0 0 0 54468 230440 6301544 0 0 0 296 119 116 0 0 99 0
0 0 0 54212 230440 6301648 0 0 0 183 361 435 0 0 95 4
`````````````````````````
Is it possible to suggest something to tweak the server / mysql to get
a fast remote mysql box ?
Thanks
Reply to: