Portal Home > Knowledgebase > Articles Database > MySQL config optimization help
MySQL config optimization help
| Posted by zoli, 09-29-2009, 03:13 AM |
| I have a 13 GB mysql database and I would like to optimize my.cnf.
The db has 150 tables, 140 MyISAM tables and 10 InnoDB. The server now has 8GB RAM but I plan to increase it to 16 GB.
Mytop says that the key efficiency is 100%:
MySQL on localhost (5.0.22) up 0+04:28:26 [19:47:35]
Queries: 5.4M qps: 351 Slow: 0.0 Se/In/Up/De(%): 38/00/00/00
qps now: 382 Slow qps: 0.0 Threads: 23 ( 2/ 2) 45/00/00/00
Key Efficiency: 100.0% Bps in/out: 0.2/ 58.4 Now in/out: 8.4/ 1.5k
phpMyAdmin shows that I have 56 million rows on that table.
My current my.cnf is this:
[mysqld]
datadir=/var/lib/mysql
socket=/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=1
[mysql.server]
user=mysql
basedir=/var/lib
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[mysqld]
safe-show-database
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 4608M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 8M
read_buffer_size = 8M
read_rnd_buffer_size = 128M
myisam_sort_buffer_size = 1024M
thread_cache_size = 8
query_cache_size = 512M
ft_min_word_len = 2
tmp_table_size = 512M
thread_concurrency = 16
max_connections = 1000
max_connect_errors = 10000
ssl-ca=/etc/my-ca-cert.pem
ssl-cert=/etc/my-server-cert.pem
ssl-key=/etc/my-server-key.pem
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
Most of the times the Mysql is very fast, but some select queries are slow and slowing down other selects from the same table.
My question is: what should be my optimal my.cnf if I keep the existing 8GB RAM? What if I raise the RAM to 16 GB? Would it be OK to increase memory to 16 GB, this way, I could use the entire database from memory? Am I wrong? The server is only a MySQL server.
Here is my existing processor info:
Motherboard SuperMicro X7DBR-E Intel Xeon QuadCore DualProc SATA [2Proc]
Processor Intel Xeon-Harpertown 5430-Quadcore [2.66GHz] Hardware upgrade
Processor Intel Xeon-Harpertown 5430-Quadcore [2.66GHz] Hardware upgrade
Please advise what should I do to optimize the server. Thank you in advance.
|
| Posted by eth10, 09-29-2009, 09:03 AM |
| I guess all the optimization are in place from the parameters you have given.
|
| Posted by relichost, 09-29-2009, 09:31 AM |
| Hi
that my.cnf looks pretty good already, but these queries have you optimized the tables (by adding indexing) or using the explain function within mysql.
Maybe there is a join that does a full table scan. I would start looking at those queries before spending more money.
The only other thing it could be if the above has been done is the disk speed, you could be getting io wait.
Thanks
Andrew
|
| Posted by zoli, 09-29-2009, 10:25 AM |
| How can I check harddisk usage to measure io wait?
Thanks for all the suggestions.
|
| Posted by relichost, 09-29-2009, 10:28 AM |
| Hi
use top (in putty or what ever you use to SSH in to the server)whilst the load is heavy and you'll see there is a field for wait.
or you can use a command called sar or vmstat
Thanks
Andrew
|
| Posted by zoli, 09-29-2009, 10:44 AM |
| With top, I can not find any iowait:
top - 14:43:50 up 51 days, 6:55, 1 user, load average: 4.55, 3.55, 3.04
Tasks: 169 total, 1 running, 168 sleeping, 0 stopped, 0 zombie
Cpu(s): 31.5%us, 14.5%sy, 0.0%ni, 38.7%id, 14.9%wa, 0.0%hi, 0.4%si, 0.0%st
Mem: 8311004k total, 8016816k used, 294188k free, 72204k buffers
Swap: 1052248k total, 92k used, 1052156k free, 5782908k cached
Where should I check?
|
| Posted by relichost, 09-29-2009, 10:47 AM |
| Hi
14.9%wa
Thanks
|
| Posted by zoli, 09-29-2009, 01:45 PM |
| Is this good or bad? I assume this number should be low, right?
|
| Posted by relichost, 09-29-2009, 01:45 PM |
| The lower the better.
Faster disks will help like SAS 15K drives.
Andrew
|
| Posted by zoli, 09-29-2009, 01:56 PM |
| Thanks. How can I check the speed of my harddisk? I see it is a Seagate Cheetah ST373455SS.
|
Add to Favourites 
Print this Article
Also Read
Hostgator (Views: 569)
Remote MySQL (Views: 558)