# MySQL Performance Help (Really stuck here..)

**URL:** <https://forums.percona.com/t/mysql-performance-help-really-stuck-here/353>\
**Category:** Other MySQL® Questions\
**Created:** [June 12, 2007, 12:39pm UTC](https://forums.percona.com/t/mysql-performance-help-really-stuck-here/353 "2007-06-12T12:39:46Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![aderae](https://avatars.discourse-cdn.com/v4/letter/a/e480ec/32.png) [@aderae](https://forums.percona.com/u/aderae)\
**Post date:** [June 12, 2007, 12:39pm UTC](https://forums.percona.com/t/mysql-performance-help-really-stuck-here/353/1 "2007-06-12T12:39:46Z")

</div>

Hello people;

I have a virtual dedicated server from godaddy with 512mb guaranteed ram. I am running a web based online strategy game which have generally 250-300 online users. My problem is, suddenly (when everything was going perfect) server started being real slow. No config and code changed but suddenly it happened and now my server is really slow. Code is really good optimized and was working perfect till that day. I have researched and worked with a lot of my.cnf configs but nothing changed really.

I am using mysql5.1 (deault install with plesk 8.1) and apache. I am on fedora core 6.

SHOW STATUS;

±----------------------------------±---------+| Variable\_name | Value |±----------------------------------±---------+| Aborted\_clients | 118 || Aborted\_connects | 7 || Binlog\_cache\_disk\_use | 0 || Binlog\_cache\_use | 15 || Bytes\_received | 101 || Bytes\_sent | 76 || Com\_admin\_commands | 0 || Com\_alter\_db | 0 || Com\_alter\_table | 0 || Com\_analyze | 0 || Com\_backup\_table | 0 || Com\_begin | 0 || Com\_change\_db | 0 || Com\_change\_master | 0 || Com\_check | 0 || Com\_checksum | 0 || Com\_commit | 0 || Com\_create\_db | 0 || Com\_create\_function | 0 || Com\_create\_index | 0 || Com\_create\_table | 0 || Com\_dealloc\_sql | 0 || Com\_delete | 0 || Com\_delete\_multi | 0 || Com\_do | 0 || Com\_drop\_db | 0 || Com\_drop\_function | 0 || Com\_drop\_index | 0 || Com\_drop\_table | 0 || Com\_drop\_user | 0 || Com\_execute\_sql | 0 || Com\_flush | 0 || Com\_grant | 0 || Com\_ha\_close | 0 || Com\_ha\_open | 0 || Com\_ha\_read | 0 || Com\_help | 0 || Com\_insert | 0 || Com\_insert\_select | 0 || Com\_kill | 0 || Com\_load | 0 || Com\_load\_master\_data | 0 || Com\_load\_master\_table | 0 || Com\_lock\_tables | 0 || Com\_optimize | 0 || Com\_preload\_keys | 0 || Com\_prepare\_sql | 0 || Com\_purge | 0 || Com\_purge\_before\_date | 0 || Com\_rename\_table | 0 || Com\_repair | 0 || Com\_replace | 0 || Com\_replace\_select | 0 || Com\_reset | 0 || Com\_restore\_table | 0 || Com\_revoke | 0 || Com\_revoke\_all | 0 || Com\_rollback | 0 || Com\_savepoint | 0 || Com\_select | 0 || Com\_set\_option | 0 || Com\_show\_binlog\_events | 0 || Com\_show\_binlogs | 0 || Com\_show\_charsets | 0 || Com\_show\_collations | 0 || Com\_show\_column\_types | 0 || Com\_show\_create\_db | 0 || Com\_show\_create\_table | 0 || Com\_show\_databases | 0 || Com\_show\_errors | 0 || Com\_show\_fields | 0 || Com\_show\_grants | 0 || Com\_show\_innodb\_status | 0 || Com\_show\_keys | 0 || Com\_show\_logs | 0 || Com\_show\_master\_status | 0 || Com\_show\_ndb\_status | 0 || Com\_show\_new\_master | 0 || Com\_show\_open\_tables | 0 || Com\_show\_privileges | 0 || Com\_show\_processlist | 0 || Com\_show\_slave\_hosts | 0 || Com\_show\_slave\_status | 0 || Com\_show\_status | 1 || Com\_show\_storage\_engines | 0 || Com\_show\_tables | 0 || Com\_show\_triggers | 0 || Com\_show\_variables | 0 || Com\_show\_warnings | 0 || Com\_slave\_start | 0 || Com\_slave\_stop | 0 || Com\_stmt\_close | 0 || Com\_stmt\_execute | 0 || Com\_stmt\_fetch | 0 || Com\_stmt\_prepare | 0 || Com\_stmt\_reset | 0 || Com\_stmt\_send\_long\_data | 0 || Com\_truncate | 0 || Com\_unlock\_tables | 0 || Com\_update | 0 || Com\_update\_multi | 0 || Com\_xa\_commit | 0 || Com\_xa\_end | 0 || Com\_xa\_prepare | 0 || Com\_xa\_recover | 0 || Com\_xa\_rollback | 0 || Com\_xa\_start | 0 || Compression | OFF || Connections | 163600 || Created\_tmp\_disk\_tables | 0 || Created\_tmp\_files | 5 || Created\_tmp\_tables | 1 || Delayed\_errors | 0 || Delayed\_insert\_threads | 0 || Delayed\_writes | 0 || Flush\_commands | 1 || Handler\_commit | 0 || Handler\_delete | 0 || Handler\_discover | 0 || Handler\_prepare | 0 || Handler\_read\_first | 0 || Handler\_read\_key | 0 || Handler\_read\_next | 0 || Handler\_read\_prev | 0 || Handler\_read\_rnd | 0 || Handler\_read\_rnd\_next | 0 || Handler\_rollback | 0 || Handler\_savepoint | 0 || Handler\_savepoint\_rollback | 0 || Handler\_update | 0 || Handler\_write | 130 || Innodb\_buffer\_pool\_pages\_data | 81 || Innodb\_buffer\_pool\_pages\_dirty | 0 || Innodb\_buffer\_pool\_pages\_flushed | 1 || Innodb\_buffer\_pool\_pages\_free | 431 || Innodb\_buffer\_pool\_pages\_latched | 0 || Innodb\_buffer\_pool\_pages\_misc | 0 || Innodb\_buffer\_pool\_pages\_total | 512 || Innodb\_buffer\_pool\_read\_ahead\_rnd | 3 || Innodb\_buffer\_pool\_read\_ahead\_seq | 0 || Innodb\_buffer\_pool\_read\_requests | 3253 || Innodb\_buffer\_pool\_reads | 56 || Innodb\_buffer\_pool\_wait\_free | 0 || Innodb\_buffer\_pool\_write\_requests | 1 || Innodb\_data\_fsyncs | 7 || Innodb\_data\_pending\_fsyncs | 0 || Innodb\_data\_pending\_reads | 0 || Innodb\_data\_pending\_writes | 0 || Innodb\_data\_read | 3510272 || Innodb\_data\_reads | 73 || Innodb\_data\_writes | 7 || Innodb\_data\_written | 35328 || Innodb\_dblwr\_pages\_written | 1 || Innodb\_dblwr\_writes | 1 || Innodb\_log\_waits | 0 || Innodb\_log\_write\_requests | 0 || Innodb\_log\_writes | 2 || Innodb\_os\_log\_fsyncs | 5 || Innodb\_os\_log\_pending\_fsyncs | 0 || Innodb\_os\_log\_pending\_writes | 0 || Innodb\_os\_log\_written | 1024 || Innodb\_page\_size | 16384 || Innodb\_pages\_created | 0 || Innodb\_pages\_read | 81 || Innodb\_pages\_written | 1 || Innodb\_row\_lock\_current\_waits | 0 || Innodb\_row\_lock\_time | 0 || Innodb\_row\_lock\_time\_avg | 0 || Innodb\_row\_lock\_time\_max | 0 || Innodb\_row\_lock\_waits | 0 || Innodb\_rows\_deleted | 0 || Innodb\_rows\_inserted | 0 || Innodb\_rows\_read | 118 || Innodb\_rows\_updated | 0 || Key\_blocks\_not\_flushed | 0 || Key\_blocks\_unused | 112945 || Key\_blocks\_used | 5535 || Key\_read\_requests | 42987471 || Key\_reads | 12991 || Key\_write\_requests | 217478 || Key\_writes | 181150 || Last\_query\_cost | 0.000000 || Max\_used\_connections | 72 || Not\_flushed\_delayed\_rows | 0 || Open\_files | 409 || Open\_streams | 0 || Open\_tables | 334 || Opened\_tables | 0 || Qcache\_free\_blocks | 1980 || Qcache\_free\_memory | 22482080 || Qcache\_hits | 15817170 || Qcache\_inserts | 4483010 || Qcache\_lowmem\_prunes | 193670 || Qcache\_not\_cached | 4696046 || Qcache\_queries\_in\_cache | 8816 || Qcache\_total\_blocks | 19725 || Questions | 26113763 || Rpl\_status | NULL || Select\_full\_join | 0 || Select\_full\_range\_join | 0 || Select\_range | 0 || Select\_range\_check | 0 || Select\_scan | 1 || Slave\_open\_temp\_tables | 0 || Slave\_retried\_transactions | 0 || Slave\_running | OFF || Slow\_launch\_threads | 0 || Slow\_queries | 0 || Sort\_merge\_passes | 0 || Sort\_range | 0 || Sort\_rows | 0 || Sort\_scan | 0 || Ssl\_accept\_renegotiates | 0 || Ssl\_accepts | 0 || Ssl\_callback\_cache\_hits | 0 || Ssl\_cipher | || Ssl\_cipher\_list | || Ssl\_client\_connects | 0 || Ssl\_connect\_renegotiates | 0 || Ssl\_ctx\_verify\_depth | 0 || Ssl\_ctx\_verify\_mode | 0 || Ssl\_default\_timeout | 0 || Ssl\_finished\_accepts | 0 || Ssl\_finished\_connects | 0 || Ssl\_session\_cache\_hits | 0 || Ssl\_session\_cache\_misses | 0 || Ssl\_session\_cache\_mode | NONE || Ssl\_session\_cache\_overflows | 0 || Ssl\_session\_cache\_size | 0 || Ssl\_session\_cache\_timeouts | 0 || Ssl\_sessions\_reused | 0 || Ssl\_used\_session\_cache\_entries | 0 || Ssl\_verify\_depth | 0 || Ssl\_verify\_mode | 0 || Ssl\_version | || Table\_locks\_immediate | 7088431 || Table\_locks\_waited | 2714074 || Tc\_log\_max\_pages\_used | 0 || Tc\_log\_page\_size | 0 || Tc\_log\_page\_waits | 0 || Threads\_cached | 3 || Threads\_connected | 55 || Threads\_created | 12662 || Threads\_running | 43 || Uptime | 13143 |±----------------------------------±---------+

As you can see already, it seems the problem lies within the table\_locks\_waited. But I worked a lot of config changes and nothing really changed. By the way my tables are all MyIsam.

Here is my.cnf

[mysqld]set-variable=local-infile=0datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package).old\_passwords=1skip-lockingquery\_cache\_type=1query\_cache\_limit=1Mquery\_cache\_size=32Mmax\_connections=200interactive\_timeout=100wait\_timeout=15connect\_timeout=10set-variable = key\_buffer=128Mset-variable = max\_allowed\_packet=1Mset-variable = table\_cache=512set-variable = sort\_buffer=1Mset-variable = record\_buffer=1Mset-variable = myisam\_sort\_buffer\_size=64Mset-variable = thread\_cache=8# Try number of CPU’s\*2 for thread\_concurrencyset-variable = thread\_concurrency=2log-binserver-id = 1sort\_buffer\_size = 16Mread\_buffer\_size = 16Mread\_rnd\_buffer\_size = 16Mlog\_slow\_queries=/var/log/mysql.slow.loglong\_query\_time=10default-character-set=latin5default-collation=latin5\_turkish\_ci[mysql.server]user=mysqlbasedir=/var/lib[mysqld\_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pidopen\_files\_limit=8192[mysqldump]quickset-variable = max\_allowed\_packet=16M[mysql]no-auto-rehash# Remove the next comment character if you are not familiar with SQL#safe-updates[isamchk]set-variable = key\_buffer=64Mset-variable = sort\_buffer=64Mset-variable = read\_buffer=16Mset-variable = write\_buffer=16M[myisamchk]set-variable = key\_buffer=64Mset-variable = sort\_buffer=64Mset-variable = read\_buffer=16Mset-variable = write\_buffer=16M[mysqlhotcopy]interactive-timeout

And the result from mysqlreport (3rd party addon that inspects show status)

MySQL 5.0.27-log uptime 0 3:42:11 Tue Jun 12 11:08:18 2007\_\_ Key \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **Buffer used 5.41M of 128.00M %Used: 4.22 Current 18.31M %Usage: 14.31Write hit 18.76%Read hit 99.97%** Questions \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **_Total 26.53M 2.0k/s QC Hits 16.06M 1.2k/s %Total: 60.54 DMS 9.97M 747.8/s 37.57 Com 335.06k 25.1/s 1.26 COM\_QUIT 166.04k 12.5/s 0.63 -Unknown 25 0.0/s 0.00Slow 0 0/s 0.00 %DMS: 0.00DMS 9.97M 747.8/s 37.57 SELECT 9.33M 700.1/s 35.18 93.62 UPDATE 523.97k 39.3/s 1.97 5.26 DELETE 69.04k 5.2/s 0.26 0.69 INSERT 42.78k 3.2/s 0.16 0.43 REPLACE 0 0/s 0.00 0.00Com_ 335.06k 25.1/s 1.26 set\_option 167.55k 12.6/s 0.63 change\_db 167.34k 12.6/s 0.63 show\_variab 116 0.0/s 0.00** SELECT and Sort \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **Scan 46.69k 3.5/s %SELECT: 0.50Range 265.70k 19.9/s 2.85Full join 0 0/s 0.00Range check 0 0/s 0.00Full rng join 0 0/s 0.00Sort scan 21.66k 1.6/sSort range 18.81k 1.4/sSort mrg pass 0 0/s** Query Cache \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **Memory usage 13.58M of 32.00M %Used: 42.44Block Fragmnt 19.46%Hits 16.06M 1.2k/sInserts 4.56M 341.9/sInsrt:Prune 23.49:1 327.3/sHit:Insert 3.52:1** Table Locks \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **Waited 2.76M 207.3/s %Total: 27.72Immediate 7.21M 540.5/s** Tables \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **Open 348 of 512 %Cache: 67.97Opened 781 0.1/s** Connections \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **Max used 72 of 200 %Max: 36.00Total 166.10k 12.5/s** Created Temp \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **Disk table 2 0.0/sTable 11.38k 0.9/sFile 5 0.0/s** Threads \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **Running 38 of 47Cached 1 of 8 %Hit: 92.24Created 12.88k 1.0/sSlow 0 0/s** Aborted \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_ **Clients 120 0.0/sConnects 7 0.0/s** Bytes \_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_Sent 1.68G 125.7k/sReceived 1.72G 129.2k/s

I am pretty sure the hardware upgrade won’t change anything because the same setup, same code and the same amount of onlien users was running very smooth for 6 months.

Here is the cat /proc/user\_beancounters in case you need

Version: 2.5 uid resource held maxheld barrier limit failcnt 4030: kmemsize 24833252 24841444 33925283 37317811 0 lockedpages 0 0 1400 1400 0 privvmpages 196620 196878 524288 524288 0 shmpages 5944 5944 131072 131072 0 dummy 0 0 0 0 0 numproc 190 190 1024 1024 0 physpages 76451 76461 0 2147483647 0 vmguarpages 0 0 128000 2147483647 0 oomguarpages 76451 76461 128000 2147483647 0 numtcpsock 260 264 820 820 0 numflock 88 95 1024 1024 0 numpty 1 1 64 64 0 numsiginfo 0 1 1024 1024 0 tcpsndbuf 987712 1173616 7916940 11308428 0 tcprcvbuf 1042144 1078600 7916940 11308428 0 othersockbuf 91912 111200 3958470 7349958 0 dgramrcvbuf 0 0 3958470 3958470 0 numothersock 112 120 820 820 0 dcachesize 0 0 7408590 7630848 0 numfile 4155 4172 10240 10240 0 dummy 0 0 0 0 0 dummy 0 0 0 0 0 dummy 0 0 0 0 0 numiptent 500 500 500 500 1674

My table indexes are set up good and tables are optimized good. Some tables like user table (holding user info like gold, food, population, land etc) is very busy with reads and updates. Nearly all tables are connected to themselves with user field and in all tables user fields are set as indexes.

Sorry for the long and detailed post but I am really stuck here. I will be very appreciated if anyone have any suggestions and solutions for my problem.

Thanks  
Ilkan

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [June 13, 2007, 2:05pm UTC](https://forums.percona.com/t/mysql-performance-help-really-stuck-here/353/2 "2007-06-13T14:05:39Z")

</div>

Very good informative post!  
Yes it was long but you got all the important information.

The problem you are having is that you have a _lot_ of SELECT’s and a few UPDATEs and that MyISAM is using _table_ level locking.  
When it comes to locking the rules are:

1. You can have a lot of read locks (SELECT) at the same time.
2. But you can _ONLY_ have _ONE_ write lock at a time.

So if whenever you issue an UPDATE/DELETE or INSERT it means that there must exist a single write lock on the table.  
While a lot of SELECT’s can be performed in parallell.

My guess is that one of the UPDATEs/DELETEs could take time to execute.  
Do all the updates have proper indexes?

The only other solution for you is to convert your tables to InnoDB (since it is using row level locking instead) but I think that you will need to tweak it a bit to get the speed you are after.