My configuration file... any suggestions?

08-18-2009, 09:08 PM

Hi All,

I was hoping that someone would be kind enough to review the MySQL configuration below and let me know if there are any settings that are either out of whack (to high or to low) or any missing settings.

I took a stab at it myself, but I think I've made some "mistakes".

Looking to enhance server performance, currently running a dedicated database server (for just one website) with 8GB of RAM (can go to 16GB if necessary).

Comment

We have a SEF (Search Engine Friendly) URL lookup table that has over 300,000 records (and growing) in it. I have put this table in memory, the problem is that at 300,000 records, its approaching 500M in size. Comparatively, the same table in MyISAM is only taking approximately 35MB.

If I decrease the HEAP size it causes that memory table to not function as its larger than its size.

So perhaps putting that into a memory table is not the right thing to do... any suggestions? the table structure is below:

Comment

If you need max_write_lock_count, you encounter locking which should lead you to InnoDB.

varchar(255) is treated as char(255) in the memory engine, that is why your table is so large. Moreover, since it is utf-8, 3 bytes are reserved for every character. Try SELECT MAX(LENGTH(oldurl)),MAX(LENGTH(newurl)) FROM redirection to find out how many characters you really need, and see whether you really need utf-8. Also, do you store the urls including the 'http://host/' prefix? You could probably reduce its size by a factor 5-10.