Optimize MySQL 4.0.12 after installation 2-Parts

<p>source: <a href="http://forum.rackshack.net/showthread.php?s=4160f017a3b15176b497ed7e97ec8a42&threadid=20983">http://forum.rackshack.net/showthread.php?s=4160f017a3b15176b497ed7e97ec8a42&amp;threadid=20983</a></p><p><strong><font face="Verdana" size="1">HOWTO: Optimize MySQL 4.0.12 after installation 2-Parts</font></strong> </p><p><font face="verdana, arial, helvetica" size="2">MySQL 4.0.12 has been released as stable, a vast improvement over any of its predecessors. This how-to outlines some of the basic steps necessary to upgrade successfully to MySQL 4.0.12 from 3.23, and how to optimize MySQL 4.0.12 after installing it on your server. If you have already upgraded, you should have noticed a great improvement in speed and performance. MySQL 4.0.xx coupled with phpMyAdmin 2.4.0 makes it a nice transition that our users have really appreciated. Performance has increased dramatically and our system loads are way down compared to other versions.<br /><br />Pros and cons: Vast improvement in speed and performance, on the downside, we have noted slight incompatibilities with boards such as Invisions and phpbb. One site in particular that relies on Invisions did experience a problem where one of the db tables suddenly locked up. We spent the latter part of 1hr trying to correct the problem. By entering the site though phpMyAdmin, we were able to repair the table. Some users have complained about table lockups especially amongst Invision board users, and a repair of the table has corrected the problem so far. We have only experienced one such case among many sites. We don’t know if this was just a coincidence or not but if your going to upgrade, you should consider some of the facts before doing so.<br /><br />After installing MySQL 4.0.12 there are some very important configuration changes that need to be performed and I will go through those in this HOWTO. Don’t even think about upgrading to 4.0.12 if your intention is to go back a version if you decide you don’t like it. You cannot go back a version if you get into trouble because the basic structure of MySQL 4.0.12 is vastly different then 3.23. Doing so without doing it properly could render all your databases useless. There is a way to go back a version however its really a big deal and this HOWTO does not cover that.<br /><br />The first action we need to perform after installation of MySQL 4.0.12, is to update and add the new privileges and features to the MySQL privilege tables. As many saw with the installation of MySQL 4.0.12 on cPanel, there was a problem after the upgrade where all users on the server were able to view everyone else’s databases though phpMyAdmin. This could be have been a security issue in itself, and occurred because so many didn’t update the privileges tables after installation.<br /><br />Log in to your box as root and execute the following command;<br /><br /><em>mysql_fix_privilege_tables</em><br /><br />This command will upgrade the MySQL privilege tables and set all permissions properly. Ignore the errors and warnings. Those are normal and you will see these messages after executing the command.<br /><br />Next, you should optimize all your databases. If you have never done this before, now is the time. This command will go through all your databases and optimize all your database tables for optimal performance;<br /><br /><em>mysqlcheck -o -u root -p --all-databases</em><br /><br />Provide the root password when prompted then wait till optimization completes.<br /><br />Its time to visit <b>my.cnf</b>. For best optimization, it is recommended that you upgrade your existing STARUP SQL VARIABLES in my.cnf with the new SQL VARIABLE names that have been added with this release. You don’t have to do that if you dont want however by not doing so, the old names still work in MySQL 4.0, but are deprecated. Why not spend a few minutes changing those? The following SQL VARIABLES have changed with this release;<br /><br />myisam_bulk_insert_tree_size changed to bulk_insert_buffer_size <br />query_cache_startup_type change to query_cache_type <br />record_buffer changed to read_buffer_size <br />record_rnd_buffer changed to read_rnd_buffer_size <br />sort_buffer changed to sort_buffer_size <br />warnings changed to log-warnings <br />err-log changed to --log-error (for mysqld_safe)<br /><br />SQL_BIG_TABLES changed to BIG_TABLES <br />SQL_LOW_PRIORITY_UPDATES changed to LOW_PRIORITY_UPDATES <br />SQL_MAX_JOIN_SIZE changed to MAX_JOIN_SIZE <br />SQL_QUERY_CACHE_TYPE changed to QUERY_CACHE_TYPE<br /><br />Most of us at Rackshack will not be using the second set of SQL variables, not so as much as the first set.<br /><br />Pico or Vi <strong><font color="#993300">/etc/my.cnf</font></strong><br /><br />Look for any OLD SQL variable names in your my.cnf, compare and change them to reflect the new variable names. In my set, I found i had to change:<br />record_buffer to read_buffer_size <br />sort_buffer to sort_buffer_size. <br />You may find others that need changing in your configuration.<br /><br />The most important addition for optimal performance is the addition of three new SQL variables. I HIGHLY recommend that you add them to my.cnf if your running any type of busy server. Those SQL variables are;<br /><br /><em>query_cache_limit<br />query_cache_size<br />query_cache_type</em><br /><br />These additions have dropped my server loads dramatically. You can look up the meaning of these variable on the MySQL site and i will not attempt to explain them here. All i know is they work wonders.<br /><br /><em>[mysqld]<br />skip-innodb <br />query_cache_limit = 1M<br />query_cache_size = 32M<br />query_cache_type = 1</em><br /><br />Add it under [mysqld] as indicated above.<br /><br />One other change which should be done to your my.cnf file is the removal of <b>set-variable</b>.<br /><br />Find all occurances of set-variable and remove it from my.cnf. Its not needed!<br /><br />Here is my copy of my.cnf<br /><br /><em>[mysqld]<br />datadir=/var/lib/mysql<br />socket=/var/lib/mysql/mysql.sock<br />skip-locking<br />skip-innodb<br />query_cache_limit=1M<br />query_cache_size=32M<br />query_cache_type=1<br />max_connections=500<br />interactive_timeout=100<br />wait_timeout=100<br />connect_timeout=10<br />thread_cache_size=128<br />key_buffer=16M<br />join_buffer=1M<br />max_allowed_packet=16M<br />table_cache=1024<br />record_buffer=1M<br />sort_buffer_size=2M<br />read_buffer_size=2M<br />max_connect_errors=10<br /># Try number of CPU's*2 for thread_concurrency<br />thread_concurrency=2 &lt;--- Try 4 for dual pentiums<br />myisam_sort_buffer_size=64M<br />log-bin<br />server-id=1<br /><br />[mysql.server] <br />user=mysql <br />basedir=/var/lib <br /><br />[safe_mysqld] <br />err-log=/var/log/mysqld.log <br />pid-file=/var/lib/mysql/mysql.pid &lt;-- Not necessary<br />open_files_limit=8192 <br /><br />[mysqldump] <br />quick <br />max_allowed_packet=16M<br /><br />[mysql]<br />no-auto-rehash <br />#safe-updates<br /><br />[isamchk] <br />key_buffer=64M<br />sort_buffer=64M<br />read_buffer=16M<br />write_buffer=16M <br /><br />[myisamchk]<br />key_buffer=64M<br />sort_buffer=64M<br />read_buffer=16M <br />write_buffer=16M<br /><br />[mysqlhotcopy] <br />interactive-timeout <br /></em><br />This may be good starting point for you if you dont have your my.cnf setup. You can try mine out on your server or modify it to suit your own server and memory requirements.<br /><br />Once you have made all of the above changes its time to save the file and restart MySQL. I recommend that you view your mysqld.log file after restarting MySQL to make sure there are no errors being logged.<br /><br />I highly recommend that everyone view this link for more information upgrading from version 3.23 to 4.0.<br /><br /><a href="http://www.mysql.com/doc/en/Upgrading-from-3.23.html" target="_blank"><font color="#1d40f5">http://www.mysql.com/doc/en/Upgrading-from-3.23.html</font></a><br /><br />I have certainly not covered everthing and there may be other information here that may benefit you. Depending on the type of database you host there are additional commands to run for innodb type db's and other optimization methods to consider in preparation for the release of MySQL 4.1 and 5.0.<br /><br />Happy SQL'ing! </font></p><p>_________________________________________</p><p /><p><em>[mysqld_safe]<br />open_files_limit = 8192<br /><br />[mysqldump]<br />quick<br />max_allowed_packet = 16M<br /><br />[myisamchk]<br />key_buffer = 64M<br />sort_buffer = 64M<br />read_buffer = 16M<br />write_buffer = 16M<br /></em></p><hr /><font face="verdana, arial, helvetica" size="2"><p><br /><br />MYSQL performance is now out of this world.<br /><br />HTH</p><p /><p>__________________________________</p><p /><p /><blockquote><font face="verdana,arial,helvetica" size="1">quote:</font> <hr /><i>Originally posted by Dave# </i><br /><b>I'm using less than a quarter of real memory and doing about 18 queries a second:<br /><br /><a href="http://www.cpfc.org/php/mysql.php" target="_blank"><font color="#1d40f5">http://www.cpfc.org/php/mysql.php</font></a><br /><br />I think I'll keep the bufferes the way they are. </b><hr /></blockquote><p><br /><br /><br />Well I reduced those 3 buffers (primarilily the read buffer) on one server that was going out of control at peak. After reducing the read and sort buffers the server has not gone out of control, and all the sites load faster. Anyway though, I am just throwing this out there in case anyone runs into any issues, so they know whats eating up all their ram. If yours is running well, then I'm gald for you and hopes it stays that way lol.. I'm not out to prove anyone wrong <br /><br />btw, what script is that you use for mysql info, very detailed, i'd like to get a copy.</p><p /><p>---------------------------------------------------</p><p><font color="#ff0000">If you recieve the error &quot;Fatal error: Can't find messagefile ....&quot; try simply removing the basedir directive from your my.cnf. In my experience MySQL does a fine job of autodetecting it.</font></p><p><font color="#ff0000"></font></p><p><font color="#000000">-----------------------------------------------</font></p><p><strong>srv.kznmx.com</strong> settings: <em>/etc/my.cnf</em>  &amp; <em>/usr/local/cpanel/whostmgr/my.cnf</em></p><p /><p><em>[mysqld]<br />safe-show-database<br />max_connections = 500<br />key_buffer = 16M<br />myisam_sort_buffer_size = 64M<br />join_buffer_size = 2M<br />read_buffer_size = 2M<br />sort_buffer_size = 3M<br />table_cache = 1500<br />thread_cache_size = 128<br />wait_timeout = 14400<br />connect_timeout = 10<br />max_allowed_packet = 16M<br />max_connect_errors = 10<br />query_cache_limit = 1M<br />query_cache_size = 32M<br />query_cache_type = 1<br />skip-innodb<br />thread_concurrency=4</em></p><p><em>[mysqld_safe]<br />open_files_limit = 8192</em></p><p><em>[mysqldump]<br />quick<br />max_allowed_packet = 16M</em></p><p><em>[myisamchk]<br />key_buffer = 64M<br />sort_buffer = 64M<br />read_buffer = 16M<br />write_buffer = 16M</em></p><p><em>[isamchk]<br />key_buffer=64M<br />sort_buffer=64M<br />read_buffer=16M</em></p><p><em>[mysqlhotcopy]<br />interactive-timeout</em></p></font>