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.bakshi@stripped> 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
>
