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 ?
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
