RV, you may find that increasing the size of the key_buffer could have a negative effect on performance. You want to make sure that there's enough physical RAM for all the processes on the machine, to avoid paging/swapping to disk, which is very slow.
Here's a an interesting note: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#optvar_key_buffer_size I was surprised when I read that page as it says (for 5.0 anyway) that 4 GB is the largest size allowable for key_buffer, but you're using 6 GB. This setting is for MyISAM only I believe, which I take it is what you're using? One suggestion if you truly can't utilize more than 4 GB for the key_buffer is to look at setting up an in-memory filesystem to hold temp space for MySQL, which will be quite a bit faster than temp space on disk. That would let you really use your 16GB well. You can also look at increasing some of the other buffers, sort buffers etc., but do your homework as many of those are per-thread settings, not global settings! In other words, a 256 MB setting with 8 threads = 2 GB, not 256 MB. On the 5.0 upgrade - it's not that bad, honest! Two things to be aware of are the change in passwords that happened with 4.1, and also more strict interpretation of certain joins as has been discussed on the list a couple of time recently. The password thing is easy enough to work around if you're happy just enabling the old password scheme; the join thing could be a little more problematic but with moderate testing you should be able to iron things out ahead of time. Dan On 10/25/06, RV Tec <[EMAIL PROTECTED]> wrote:
Folks, A few months ago, I came here asking for directions on how I could improve performance of MySQL. Back then, I was using OpenBSD on a dual Opteron 248 with 2GB, LSI MegaRAID 320-1, 15k RPM SCSI discs, MySQL was (still is) 4.0.27, the database is MyISAM, reaching 50GB. After some considerations, we have decided to upgrade things in three steps: 1) Bumped the OS to Gentoo Linux, GCC 3.4.4, glibc 2.4/NPTL, deadline scheduler, xfs and kernel 2.6.16-gentoo-r7. This, by itself, was already a great performance improvement. And it is stable/reliable as well. 2) Jumped from 2GB RAM to 16GB, changed RAID card to a dual-channel (so the database have a channel of its own). This proved that memory was our greatest bottle neck. I can honestly say that now I'm happy with the performance. My question is: key_buffer seems to be the solution to all my problems. On a 16GB server, I'm using only 37.5% of it to the key_buffer (6144). If I make this larger, will be a performance improvement or a stability killer? 3) Upgrade MySQL 4.0.27 to MySQL 5.0 -- this is going to be painful, but it is already on the way. Thanks a lot! RV -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
