# My configuration file... any suggestions? **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [August 18, 2009, 8:38pm UTC](https://forums.percona.com/t/my-configuration-file-any-suggestions/1241 "2009-08-18T20:38:21Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\ **Post date:** [August 18, 2009, 8:38pm UTC](https://forums.percona.com/t/my-configuration-file-any-suggestions/1241/1 "2009-08-18T20:38:21Z")
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! ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [August 19, 2009, 10:15am UTC](https://forums.percona.com/t/my-configuration-file-any-suggestions/1241/2 "2009-08-19T10:15:37Z")
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-](http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-) in-mysql-server-after-installation/ but skip InnoDB if not appropriate. ---
**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\ **Post date:** [August 19, 2009, 11:13am UTC](https://forums.percona.com/t/my-configuration-file-any-suggestions/1241/3 "2009-08-19T11:13:52Z")
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? ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [August 19, 2009, 2:48pm UTC](https://forums.percona.com/t/my-configuration-file-any-suggestions/1241/4 "2009-08-19T14:48:17Z")
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. ---
**Author:** ![iberkner](https://avatars.discourse-cdn.com/v4/letter/i/c67d28/32.png) [@iberkner](https://forums.percona.com/u/iberkner)\ **Post date:** [August 19, 2009, 3:17pm UTC](https://forums.percona.com/t/my-configuration-file-any-suggestions/1241/5 "2009-08-19T15:17:13Z")
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](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? ---
**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\ **Post date:** [August 19, 2009, 3:56pm UTC](https://forums.percona.com/t/my-configuration-file-any-suggestions/1241/6 "2009-08-19T15:56:17Z")
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]](http://host/%5B/URL%5D)’ prefix? You could probably reduce its size by a factor 5-10.