Horrible Performance, Needs some tips

MySQL & MariaDB Other MySQL® Questions
1

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 :

[URL]http://micromatikal.dlinkddns.com:8000/cust/[/URL]

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

2

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.

3

Your key buffer is an issue. 97G is a lot, find out which part is actively used.

Increasing table_cache will also help some.

4

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

5

You will have to analyse queries, or use a special mysql build that keeps track of index usage.

6

Great, I will look into how to do that this week! Thank so much, any other tuning tips would be greatly appreciated.

Tim