# MySQL performance **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [October 25, 2008, 1:03am UTC](https://forums.percona.com/t/mysql-performance/975 "2008-10-25T01:03:02Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![tryme](https://avatars.discourse-cdn.com/v4/letter/t/48db29/32.png) [@tryme](https://forums.percona.com/u/tryme)\ **Post date:** [October 25, 2008, 1:03am UTC](https://forums.percona.com/t/mysql-performance/975/1 "2008-10-25T01:03:02Z")
Hello, I need some help/advice for my server. Here are the server specs: 1 x Quad Core Xeon 5320 6 GB memory 2 x 73GB SAS (1 is only for MySQL databases) Linux debian 2.6.18-6-686-bigmem MySQL version 5.0.32 I’m running free forum server with ~20k databases (each 27 tables). Only MyISAM. I would like to know if my.cnf is well optimized for such server. Here is: | [B]Quote:[/B] | | skip-locking safe-show-database skip-innodb query\_cache\_limit = 1M query\_cache\_size = 48M query\_cache\_type = 1 set-variable = key\_buffer=16M set-variable = join\_buffer=1M set-variable = read\_buffer\_size=4M set-variable = sort\_buffer\_size=4M set-variable = tmp\_table\_size=128M set-variable = table\_cache=25 set-variable = max\_connections=50 set-variable = thread\_concurrency=4 set-variable = thread\_cache\_size=160 set-variable = max\_allowed\_packet=1M set-variable = connect\_timeout=3 long\_query\_time = 3 log\_slow\_queries = /var/log/mysql/mysql-slow.log [safe\_mysqld] err-log=/var/log/mysqld.log [mysqldump] quick quote-names max\_allowed\_packet = 16M [mysql] no-auto-rehash [isamchk] key\_buffer = 256M table\_cache = 2048 sort\_buffer\_size = 2M join\_buffer\_size = 2M read\_buffer\_size = 2M thread\_cache\_size = 40 thread\_concurrency = 2 tmp\_table\_size = 128M | Here is raport from mysqlreport: | [B]Quote:[/B] | | \_\_ Key \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_\_\_\_ Buffer used 14.16M of 16.00M %Used: 88.48 Current 1.87M %Usage: 11.69 Write hit 50.04% Read hit 86.00% \_\_ Questions \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Total 178.08M 211.2/s DMS 91.62M 108.7/s %Total: 51.45 QC Hits 44.89M 53.2/s 25.21 Com\_ 21.12M 25.0/s 11.86 COM\_QUIT 20.27M 24.0/s 11.38 +Unknown 185.34k 0.2/s 0.10 Slow 3 s 95 0.0/s 0.00 %DMS: 0.00 Log: ON DMS 91.62M 108.7/s 51.45 SELECT 61.89M 73.4/s 34.75 67.55 UPDATE 23.52M 27.9/s 13.21 25.67 DELETE 2.63M 3.1/s 1.48 2.88 INSERT 1.99M 2.4/s 1.12 2.17 REPLACE 1.59M 1.9/s 0.89 1.73 Com\_ 21.12M 25.0/s 11.86 change\_db 20.36M 24.2/s 11.43 optimize 691.80k 0.8/s 0.39 create\_tabl 33.71k 0.0/s 0.02 \_\_ SELECT and Sort \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Scan 19.62M 23.3/s %SELECT: 31.70 Range 1.78M 2.1/s 2.87 Full join 543.78k 0.6/s 0.88 Range check 0 0/s 0.00 Full rng join 2.49k 0.0/s 0.00 Sort scan 3.36M 4.0/s Sort range 4.83M 5.7/s Sort mrg pass 926 0.0/s \_\_ Query Cache \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Memory usage 30.91M of 48.00M %Used: 64.40 Block Fragmnt 13.13% Hits 44.89M 53.2/s Inserts 61.73M 73.2/s Insrt:Prune 6.39:1 61.8/s Hit:Insert 0.73:1 \_\_ Table Locks \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Waited 57.45k 0.1/s %Total: 0.04 Immediate 158.39M 187.9/s \_\_ Tables \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_ Open 25 of 25 %Cache: 100.00 Opened 90.58M 107.4/s \_\_ Connections \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Max used 23 of 50 %Max: 46.00 Total 20.27M 24.0/s \_\_ Created Temp \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Disk table 757.90k 0.9/s Table 1.27M 1.5/s Size: 128.0M File 1.86k 0.0/s \_\_ Threads \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_ Running 1 of 1 Cached 22 of 160 %Hit: 100 Created 23 0.0/s Slow 0 0/s \_\_ Aborted \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_ Clients 9 0.0/s Connects 0 0/s \_\_ Bytes \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_\_ Sent 3.85G 4.6k/s Received 2.38G 2.8k/s \_\_ InnoDB Buffer Pool \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Usage 0 of 0 %Used: 0.00 Read hit 0.00% Pages Free 0 %Total: 0.00 Data 0 0.00 %Drty: 0.00 Misc 0 0.00 Latched 0 0.00 Reads 0 0/s From file 0 0/s 0.00 Ahead Rnd 0 0/s Ahead Sql 0 0/s Writes 0 0/s Flushes 0 0/s Wait Free 0 0/s \_\_ InnoDB Lock \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Waits 0 0/s Current 0 Time acquiring Total 0 ms Average 0 ms Max 0 ms \_\_ InnoDB Data, Pages, Rows \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Data Reads 0 0/s Writes 0 0/s fsync 0 0/s Pending Reads 0 Writes 0 fsync 0 Pages Created 0 0/s Read 0 0/s Written 0 0/s Rows Deleted 0 0/s Inserted 0 0/s Read 0 0/s Updated 0 0/s | My big problem is the load average on peak hours. If you need more information, please tell me. Thanks for your help. Best Regards, tryme ---
**Author:** ![artur8ur](https://avatars.discourse-cdn.com/v4/letter/a/9f8e36/32.png) [@artur8ur](https://forums.percona.com/u/artur8ur)\ **Post date:** [November 4, 2008, 9:06am UTC](https://forums.percona.com/t/mysql-performance/975/2 "2008-11-04T09:06:18Z")
Hi, | [B]Quote:[/B] | | I would like to know if my.cnf is well optimized for such server. Here is: | [B]Quote:[/B] | | skip-locking set-variable = key\_buffer=16M | | Just a hint: You can remove this "set-vatiable = " part of the statements... It's needed only for "old" MySQL Server... I guess the following statistics are gathered after running for some time | [B]Quote:[/B] | | Here is report from mysqlreport: | [B]Quote:[/B] | | \_\_ Key […] \_\_ Query Cache \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ Memory usage 30.91M of 48.00M %Used: 64.40 Block Fragmnt 13.13% Hits 44.89M 53.2/s Inserts 61.73M 73.2/s Insrt:Prune 6.39:1 61.8/s Hit:Insert 0.73:1 | | Your query cache is to small... most of the inserts kick out cached queries.. Increase the size of your cache... | [B]Quote:[/B] | | | [B]Quote:[/B] | | \_\_ Tables \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_\_ Open 25 of 25 %Cache: 100.00 Opened 90.58M 107.4/s | | Your table cache is to small... increase the table\_cache variable... Set it to 1024, and have a look how many slots are used,.. you can decrease it than to a number around that... You might need to increase open\_files\_limit, too. | [B]Quote:[/B] | | | [B]Quote:[/B] | | \_\_ Threads \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ \_ Running 1 of 1 Cached 22 of 160 %Hit: 100 Created 23 0.0/s Slow 0 0/s | | Your thread cache is to big.. The tread cache is the number of "unused" threads to cache, not a limit to how many threads are allowed in total. This cache is used to decrease the number of threads which are created per second,.. to smooth the fluctuations.. I think a cache of 8 should be enough for your... max 24. Tuning of other variables like sort\_buffer\_size, read\_buffer\_size, read\_rnd\_buffer\_size, bulk\_insert\_buffer\_size… but this depends on the your data structure, queries, workload…