# Horrible Performance, Needs some tips **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [July 30, 2010, 12:11pm UTC](https://forums.percona.com/t/horrible-performance-needs-some-tips/1461 "2010-07-30T12:11:41Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![micromatikal](https://avatars.discourse-cdn.com/v4/letter/m/49beb7/32.png) [@micromatikal](https://forums.percona.com/u/micromatikal)\ **Post date:** [July 30, 2010, 12:11pm UTC](https://forums.percona.com/t/horrible-performance-needs-some-tips/1461/1 "2010-07-30T12:11:41Z")
Hello there, thanks for taking the time to read my post! I have been brought in to help consult with a local .com here. They wanted hardware consultation for upgrading / adding to the MySQL server environment. I am quite certain they actually have optimization issues with their server that could greatly improve performance. However I will need some help from experts such as yourselves, where I don’t have a ton of experience tweaking mysql. I am going to include some pictures here : [http://micromatikal.dlinkddns.com:8000/cust/](http://micromatikal.dlinkddns.com:8000/cust/) Please let me know if you have any input, I would greatly appreciate any suggestions. We are seeing very slow queries, I’m sure a lot of it is due to lack of query optimization, but I’m sure there are some server settings that would help. If you need me to post anything from the configuration file please let me know. It looks like they are currently using Myisam, would it be advantageous to use InnoDB? At this time though I need to tweak it as best as can be for what they have set up. Also, some interesting settings from the my.cnf file are: # cat /etc/my.cnf | grep -i buffer key\_buffer\_size = 2G sort\_buffer\_size = 12M # MUST BE SAME SIZE AS read\_buffer\_size read\_buffer\_size = 8M read\_rnd\_buffer\_size = 12M myisam\_sort\_buffer\_size = 256M join\_buffer\_size = 4M search\_cache.key\_buffer\_size = 4G # You can set ..\_buffer\_pool\_size up to 50 - 80 % #innodb\_buffer\_pool\_size = 384M # Set ..\_log\_file\_size to 25 % of buffer pool size #innodb\_log\_buffer\_size = 8M key\_buffer = 256M sort\_buffer\_size = 256M read\_buffer = 2M write\_buffer = 2M key\_buffer = 256M sort\_buffer\_size = 256M read\_buffer = 2M write\_buffer = 2M Thank you so much for any advice you can give Tim ---
**Author:** ![micromatikal](https://avatars.discourse-cdn.com/v4/letter/m/49beb7/32.png) [@micromatikal](https://forums.percona.com/u/micromatikal)\ **Post date:** [July 30, 2010, 12:13pm UTC](https://forums.percona.com/t/horrible-performance-needs-some-tips/1461/2 "2010-07-30T12:13:51Z")
By the way, the server is a 3.4 GHZ x 4 CPU 32 GB of RAM 400 GB RAID 5 SCSI, I believe 15K SAS The iowait on the server is getting killed, I think some of the queries and indexes need some serious attention, but in the mean time something seems strange to me in the configuration, I’m sure you folks can help. Thanks again Also, when I do: du -ch /var/lib/mysql/\*/\*MYI | tail -n1 97G total That seems insanely large to me, is that going to kill the performance regardless? I’m seeing things like making the key\_buffer\_size half of the ram, and others say it should be enough to cover the indexes, but no way is that possible. ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [July 30, 2010, 3:27pm UTC](https://forums.percona.com/t/horrible-performance-needs-some-tips/1461/3 "2010-07-30T15:27:34Z")
Your key buffer is an issue. 97G is a lot, find out which part is actively used. Increasing table\_cache will also help some. ---
**Author:** ![micromatikal](https://avatars.discourse-cdn.com/v4/letter/m/49beb7/32.png) [@micromatikal](https://forums.percona.com/u/micromatikal)\ **Post date:** [July 30, 2010, 3:30pm UTC](https://forums.percona.com/t/horrible-performance-needs-some-tips/1461/4 "2010-07-30T15:30:45Z")
Thank you so so much for the reply, I had just adjusted the table\_cache to be larger, and it seems to be helping greatly. However, we just restarted the mysql process and already it has: Created\_tmp\_disk\_tables 8,351 Created\_tmp\_files 72 Created\_tmp\_tables 11 k Also, how can I tell how much of the indexes are actually used? Again I really appreciate your input, thank you so much for any and all help. Tim G ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [July 30, 2010, 3:33pm UTC](https://forums.percona.com/t/horrible-performance-needs-some-tips/1461/5 "2010-07-30T15:33:00Z")
You will have to analyse queries, or use a special mysql build that keeps track of index usage. ---
**Author:** ![micromatikal](https://avatars.discourse-cdn.com/v4/letter/m/49beb7/32.png) [@micromatikal](https://forums.percona.com/u/micromatikal)\ **Post date:** [July 30, 2010, 3:34pm UTC](https://forums.percona.com/t/horrible-performance-needs-some-tips/1461/6 "2010-07-30T15:34:37Z")
Great, I will look into how to do that this week! Thank so much, any other tuning tips would be greatly appreciated. Tim