My configuration file... any suggestions?

MySQL & MariaDB Other MySQL® Questions
1

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

[mysqld]wait_timeout = 15long_query_time = 2log-slow-queries = /var/lib/mysql/slow_queries.log#log = /var/lib/mysql/mysql.logquery_cache_size = 300Mthread_cache_size = 128 max_connections = 500key_buffer_size = 256Msort_buffer_size = 100Mread_rnd_buffer_size = 100M open_files_limit = 4096table_cache = 2028max_heap_table_size = 800Mtmp_table_size = 800Mread_buffer_size = 100Mquery_cache_limit = 100Mquery_cache_type = 1datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sockuser=mysqlmax_connect_errors = 20join_buffer_size = 100Minteractive_timeout = 10[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

Many thanks!

2

Cannot say much about this since I have no idea what you do with this server. MyISAM only?

Some buffers seem ridiculously large.

Read http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune- in-mysql-server-after-installation/ but skip InnoDB if not appropriate.

3

This is a dedicated server for a high-traffic single website.

I think that the various buffer setting were added by me to try and resolve some issues… any suggestions as what the normal values should be set to or rather for high performance website?

All tables are MyISAM as we have no need at this time for transactions level stuff (from my understand that’s the primary reason to switch a table to InnoDB, but I can’t tell for sure).

What about max_write_count = 1?

4

Suggestions (changes only):

query_cache_size = 10M
key_buffer_size = 256M ← read article I posted earlier
sort_buffer_size = 1M
read_rnd_buffer_size = 1M
max_heap_table_size = 16M
tmp_table_size = 16M
read_buffer_size = 1M
query_cache_limit = 1M
join_buffer_size = 1M

What about max_write_count = 1?
you tell me.

5

Regarding:

max_heap_table_size = 16M

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:

CREATE TABLE redirection ( id int(11) NOT NULL auto_increment, cpt int(11) NOT NULL default ‘0’, rank int(11) NOT NULL default ‘0’, oldurl varchar(255) NOT NULL default ‘’, newurl varchar(255) NOT NULL default ‘’, dateadd date NOT NULL default ‘0000-00-00’, PRIMARY KEY (id), KEY newurl (newurl), KEY rank (rank), KEY oldurl (oldurl)) ENGINE=MEMORY AUTO_INCREMENT=332959 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

Also, regarding the max_write_lock_count = 1 value, I’m looking at this article http://blog.taragana.com/index.php/archive/one-mysql-configu ration-tip-that-can-dramatically-improve-mysql-performance/ which was suggesting that this may be a good setting to use. Your thoughts?

6

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 ‘[URL]http://host/[/URL]’ prefix? You could probably reduce its size by a factor 5-10.