MySQL performance

Sheridan · 02-20-2008, 08:00 AM

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
03:57:13 PM     all      0.25      0.00      0.25     75.57      0.00     23.92
03:57:13 PM       0      0.00      0.00      0.00      0.00      0.00    100.00
03:57:13 PM       1      0.00      0.00      0.00    100.00      0.00      0.00
03:57:13 PM       2      0.00      0.00      0.00    100.00      0.00      0.00
03:57:13 PM       3      0.00      0.00      0.00    100.00      0.00      0.00
03:57:13 PM       4      0.00      0.00      0.00      0.00      0.00    100.00
03:57:13 PM       5      0.99      0.00      0.00     99.01      0.00      0.00
03:57:13 PM       6      0.00      0.00      1.00     99.00      0.00      0.00
03:57:13 PM       7      0.97      0.00      1.94     97.09      0.00      0.00

Please see my.cnf bellow:

Code:

[mysqld]
#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
set-variable    = key_buffer=1024M
set-variable    = sort_buffer_size=5M
set-variable    = read_buffer_size=5M
set-variable    = max_allowed_packet=512M
set-variable    = thread_stack=3M
set-variable    = max_connections=8000
set-variable    = max_connect_errors=10000
set-variable    = ft_min_word_len=2

set-variable    = wait_timeout=10

skip-locking
table_cache = 5000
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache = 40
thread_cache_size = 8
query_cache_size = 64M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 16

[mysqld_safe]
log-error=/var/log/mysqld.log

What I have set wrong?

edenCC · 02-20-2008, 08:08 AM

Is this a dedicated DB server?

Sheridan · 02-20-2008, 08:16 AM

Quote:

Originally Posted by edenCC

Is this a dedicated DB server?

Yes, it is. It doesn't do anything else.

trickykid · 02-20-2008, 09:32 AM

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?

Sheridan · 02-20-2008, 09:51 AM

Quote:

Originally Posted by trickykid

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?

The full config of the system looks like this:

Code:

•	HP DL 380R05

•	2x Quadcore Xeon 5335

•	16GB RAM (4x 4GB FBD PC2-5300 2GB Kit)

•	8x 146GB 10K SAS Hot plug HDD

•	P400 controller, 256MB Cache, battery

•	2x NC110T PCIe Gigabit Server Adapter

•	2x Hot Plug Power supplies

•	HP iLO Advanced Nm1-Server Licence

There're MyISAM tables and lots of transactions... But the problem is that I can't switch to Inno becuase we already investigated it and our outsource development partner says it would cost a lot to rewrite necessary PHP code so that's not an option at this point...

I thought about maybe increasing key_buffer would help? Because of the following:

Code:

mysql> show status like '%key_read%';

+-------------------+------------+

| Variable_name     | Value      |

+-------------------+------------+

| Key_read_requests | 3809177251 |

| Key_reads         | 13344488   |

+-------------------+------------+

2 rows in set (0.00 sec)

If you calculate: (Key_reads/Key_read_requests)*1000, then you get that we have a miss rate of more than 3 per every 1000 requests.

Sheridan · 02-20-2008, 10:38 AM

Quote:

Originally Posted by Sheridan

The full config of the system looks like this:

Code:

•	HP DL 380R05

•	2x Quadcore Xeon 5335

•	16GB RAM (4x 4GB FBD PC2-5300 2GB Kit)

•	8x 146GB 10K SAS Hot plug HDD

•	P400 controller, 256MB Cache, battery

•	2x NC110T PCIe Gigabit Server Adapter

•	2x Hot Plug Power supplies

•	HP iLO Advanced Nm1-Server Licence

There're MyISAM tables and lots of transactions... But the problem is that I can't switch to Inno becuase we already investigated it and our outsource development partner says it would cost a lot to rewrite necessary PHP code so that's not an option at this point...

I thought about maybe increasing key_buffer would help? Because of the following:

Code:

mysql> show status like '%key_read%';

+-------------------+------------+

| Variable_name     | Value      |

+-------------------+------------+

| Key_read_requests | 3809177251 |

| Key_reads         | 13344488   |

+-------------------+------------+

2 rows in set (0.00 sec)

If you calculate: (Key_reads/Key_read_requests)*1000, then you get that we have a miss rate of more than 3 per every 1000 requests.

EDIT: Also... unfortunately for compatibility and other reasons I have to use MySQL 4.1....

Sheridan · 02-20-2008, 11:10 AM

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...

edenCC · 02-20-2008, 09:27 PM

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

Sheridan · 02-21-2008, 01:43 AM

Quote:

Originally Posted by edenCC

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

Free output:

Code:

[root@gandalf bin]# free -m
             total       used       free     shared    buffers     cached
Mem:          3288       3123        165          0         11       1893
-/+ buffers/cache:       1218       2070
Swap:        32767          0      32767

Also, MySQL proces:

Code:

[root@gandalf bin]# ps aux | grep 'mysql'
root     15886  0.0  0.0   4640  1164 pts/1    S    Feb20   0:00 /bin/sh ./bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/gandalf.tech.pid
mysql    16346 74.7 28.2 3111680 952868 pts/1  Sl   Feb20 489:58 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/gandalf.tech.pid --skip-external-locking

Anyway... is it normal for MySQL to start only one "instance" of itself? I remember on my old 2.4 kernel machine the mysqld was open several times in the process list. Now I wonder: would this mean that because it's opening only one process and the server being a 32 bit system, all the memory the MySQL can use is as little as 3G instead of the available 16G?

chrism01 · 02-21-2008, 06:23 PM

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)

edenCC · 02-22-2008, 12:06 AM

Quote:

Originally Posted by Sheridan

Free output:

Code:

[root@gandalf bin]# free -m
             total       used       free     shared    buffers     cached
Mem:          3288       3123        165          0         11       1893
-/+ buffers/cache:       1218       2070
Swap:        32767          0      32767

Also, MySQL proces:

Code:

[root@gandalf bin]# ps aux | grep 'mysql'
root     15886  0.0  0.0   4640  1164 pts/1    S    Feb20   0:00 /bin/sh ./bin/mysqld_safe --datadir=/usr/local/mysql/data --pid-file=/usr/local/mysql/data/gandalf.tech.pid
mysql    16346 74.7 28.2 3111680 952868 pts/1  Sl   Feb20 489:58 /usr/local/mysql/bin/mysqld --defaults-extra-file=/usr/local/mysql/data/my.cnf --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --user=mysql --pid-file=/usr/local/mysql/data/gandalf.tech.pid --skip-external-locking

Anyway... is it normal for MySQL to start only one "instance" of itself? I remember on my old 2.4 kernel machine the mysqld was open several times in the process list. Now I wonder: would this mean that because it's opening only one process and the server being a 32 bit system, all the memory the MySQL can use is as little as 3G instead of the available 16G?

You need to:
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.

Electro · 02-22-2008, 04:00 AM

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:

Originally Posted by edenCC

You need to:
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.

It seems HIGHMEM is already set, so suggesting it will not work. Setting HIGHMEM to 64 GB instead of HIGHMEM at 4 GB will be a better suggestion. Also need to use a 64-bit setup to take advantage of all that memory.

Sheridan · 02-23-2008, 01:58 AM

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

2) Now I have access to all that memory. So I now modified following values in my.cnf compared to what I posted above:

Code:

...
...
set-variable    = key_buffer=6144M
...
set-variable    = sort_buffer_size=10M
set-variable    = read_buffer_size=10M
...
thread_cache = 8
thread_cache_size = 150
...

3) Switched over the whole thing.

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   |
| Key_reads                  | 465339      |

..(Key_reads/Key_read_requests)*1000 now equals to 1.3 and decreasing slowly. If it goes under 0 it will be fine.

3) There's still something wrong with thread_cache_size, since accordig to this:

Code:

| Threads_cached             | 125         |
| Threads_connected          | 28          |
| Threads_created            | 1883        |

Threads_created keeps increasing... What would be a good value?

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:

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.

The problem is that I posted the exact config of the server before, all drives are 10000RPM SAS drives, in RAID 6. I assume (?) they are supposed to be faster than SATA? At least they are hell of a lot more expensive, that's for sure...

Anyway. Also the is a HP SmartArray 400, should have it's own, CPU, battery and memory... I'm having hard time beliving that this is the real issue, but anything is possible

Quote:

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.

PHP devs giving a hard time is an understatement. However unfortunately until the company has money and people for some more advanced overhaul, I need to make this work as-is, without migrating... That's what makes this a deadman's task...

I will do some test as you ask.

chrism01 · 02-24-2008, 07:14 PM

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).

Electro · 02-25-2008, 04:13 AM

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:

Originally Posted by chrism01

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).

I agree. IMHO, software developers are getting lazy these days. They think that they can include any feature they want and the speed of the processor will compensate. The goal of a software developer is creating software that is efficient, stable, and reliable.