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 ?
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
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
Apr 09
Reindl Harald Re: Remote mysql too slow
Apr 09, 2012; 11:47
Reindl Harald
Re: Remote mysql too slow
Apr 09
J. Bakshi ( ????? ???? Re: Remote mysql too slow
Apr 09, 2012; 15:26
J. Bakshi ( ????? ????
Re: Remote mysql too slow
Apr 09
Reindl Harald Re: Remote mysql too slow
Apr 09, 2012; 12:06
Reindl Harald
Re: Remote mysql too slow
Apr 09
J. Bakshi ( ????? ???? Re: Remote mysql too slow
Apr 09, 2012; 16:35
J. Bakshi ( ????? ????
Re: Remote mysql too slow
Apr 09
Johnny Withers Re: Remote mysql too slow
Apr 09, 2012; 06:14
Johnny Withers
Re: Remote mysql too slow
Apr 09
Reindl Harald Re: Remote mysql too slow
Apr 09, 2012; 13:17
Reindl Harald
Re: Remote mysql too slow
Apr 09
J. Bakshi ( ????? ???? Re: Remote mysql too slow
Apr 09, 2012; 17:23
J. Bakshi ( ????? ????
Re: Remote mysql too slow
Apr 09
Noel Butler Re: Remote mysql too slow
Apr 09, 2012; 22:20
Noel Butler
Re: Remote mysql too slow
Search
Lasso Programming
This site manages and broadcasts several email lists pertaining to Lasso Programming and technologies related and used by Lasso developers. Sign up today!