MySQL performance
I have a mysql server that has 2x quadcore XEON 5335 processors and 16GB ram.
Now, what I see is that on peak hours MySQL is using lot of disk. Code:
03:57:12 PM CPU %user %nice %system %iowait %steal %idle Code:
[mysqld] |
Is this a dedicated DB server?
|
Quote:
|
Does this database use a lot of transactions or not? What type of engine? You're not CPU bound from the looks so it's disk I/O. If you make a lot of transactions and you're using MyISAM, you may want to consider INNODB instead. Both have their pros and cons but switching to INNODB when you have transactions going on, you'll see better performance gains. You'll also see better threading across the CPU's as well. If you're already using InnoDB, I'd say from the info provided, get better hard disks. What type of disks are these by the way anyways?
|
Quote:
Code:
• HP DL 380R05 I thought about maybe increasing key_buffer would help? Because of the following: Code:
mysql> show status like '%key_read%'; |
Quote:
EDIT: Also... unfortunately for compatibility and other reasons I have to use MySQL 4.1.... |
One thing that came to my mind. The server is almost always under a very high load, yet it doesn't seem to utilize the available memory. It seems to use only a portion of what's there for it to use...
|
your memory is not properly used by mysql, how about the output of `free`? Most of your memory should be cached, and mysql configuration file indeed needs to be optimized
|
Quote:
Code:
[root@gandalf bin]# free -m Code:
[root@gandalf bin]# ps aux | grep 'mysql' |
If it's threading internally, under 2.4 each thr showed as a separate process. Under a 2.6 kernel, the thrs are all in the one process.
(I wrote a multi-thr prog in Perl that moved from a 2.4 box to a 2.6 box and noticed this) |
Quote:
1, update your kernel to hugemem version kernel-hugemem-****.EL.i686.rpm in order to use the entire 16 giga RAM. 2, Optimization the configuration file 3, a 32 GB swap space may waste a lot of harddisk space, you can decrease it to a lower value like 4 GB, or just remove it. |
I suggest use separate hard drives for the OS and serving data for a server. If the controller requires the main processor, then there is your problem. I am sure the controller is a dedicated hardware RAID controller. I assume the hard drives are Western Digital 'Raptor' 150 GB SATA. Make sure the hard drive's internal cache is off. Play around with the file system options while formatting.
I am not sure but probably the keybuffer should relate to how much data the hard drives or the array can send. I suggest play around with the sysctl settings, so the kernel does not throw to many pages that are stale or bad. Maybe the PHP developers are giving you the most problems. Their code could have intense queries. I suggest do some experiments with a small, medium, and large size databases. Run queries for each size. I think MySQL is not designed for large databases. I suggest try PostgreSQL. Quote:
|
Thanks for the answers guys!! I just want to say that I really appreciate you guys taking the time to help me sort this out...
Last night I did the following: 1) This server has a sister machine for failover, with exact same configuration. What I did was that since Quadcore XEON can handle 64 bit, I downloaded an image of 64 bit Fedora 7, and re-did the whole setup in 64 bit. Instead of using pre-made MySQL I downloaded source and recompiled the MySQL with: Code:
./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --with-mysqld-ldflags=-all-static --enable-assembler Code:
... Now I'm waiting for results... Observations so far after a few hours of online uptime: 1) Everything is definitely faster (altought not much traffic yet) 2) Key buffer size now _seems adequate, according to this: Code:
| Key_read_requests | 342357987 | Code:
| Threads_cached | 125 | OK... However real test will be higher traffic around noon or so (CET time) anyway. Until then, thinking is permitted... ------------ For Electro, you wrote: Quote:
Quote:
I will do some test as you ask. |
As a general rule (and I used to be an Oracle DBA), badly written SQL queries/badly designed tables will floor any machine. The tuning procedure (in the Oracle DBA guide) was a list of 10 (?) things to look at (in order) when tuning a system, but at the top of the list (by orders of magnitude for effect) was was fixing the SQL and/or table designs. Nothing else comes close to speeding up (unless you really are on a far too small system).
|
Sheridan, SAS has a little higher throughput than SATA, but the latency is still the same.
The default kernel sysctl settings are not always good for each setup. I suggest play around with sysctl settings. Quote:
|
All times are GMT -5. The time now is 01:06 PM. |