You may want to ensure the nic is connected to the network at the right speed and duplex. It seems that every new server I get now has to have the speed and duplex explicitly set instead of auto negotiate.
Sent from my iPad On Apr 9, 2012, at 4:00 AM, "J. Bakshi" <joydeep.bak...@infoservices.in> 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 ? > > > 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 > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
