On Thu, 05 Apr 2012 11:36:38 +0100 "Karl E. Jorgensen" <karl.jorgen...@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 -- To UNSUBSCRIBE, email to debian-user-requ...@lists.debian.org with a subject of "unsubscribe". Trouble? Contact listmas...@lists.debian.org Archive: http://lists.debian.org/20120409123630.7ff39...@shiva.selfip.org