Optimize MySQL 4.0.12 after installation 2-Parts

source: http://forum.rackshack.net/showthread.php?s=4160f017a3b15176b497ed7e97ec8a42&threadid=20983

HOWTO: Optimize MySQL 4.0.12 after installation 2-Parts

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.

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.

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.

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.

Log in to your box as root and execute the following command;

mysql_fix_privilege_tables

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.

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;

mysqlcheck -o -u root -p --all-databases

Provide the root password when prompted then wait till optimization completes.

Its time to visit my.cnf. 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;

myisam_bulk_insert_tree_size changed to bulk_insert_buffer_size
query_cache_startup_type change to query_cache_type
record_buffer changed to read_buffer_size
record_rnd_buffer changed to read_rnd_buffer_size
sort_buffer changed to sort_buffer_size
warnings changed to log-warnings
err-log changed to --log-error (for mysqld_safe)

SQL_BIG_TABLES changed to BIG_TABLES
SQL_LOW_PRIORITY_UPDATES changed to LOW_PRIORITY_UPDATES
SQL_MAX_JOIN_SIZE changed to MAX_JOIN_SIZE
SQL_QUERY_CACHE_TYPE changed to QUERY_CACHE_TYPE

Most of us at Rackshack will not be using the second set of SQL variables, not so as much as the first set.

Pico or Vi /etc/my.cnf

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:
record_buffer to read_buffer_size
sort_buffer to sort_buffer_size.
You may find others that need changing in your configuration.

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;

query_cache_limit
query_cache_size
query_cache_type


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.

[mysqld]
skip-innodb
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1


Add it under [mysqld] as indicated above.

One other change which should be done to your my.cnf file is the removal of set-variable.

Find all occurances of set-variable and remove it from my.cnf. Its not needed!

Here is my copy of my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip-locking
skip-innodb
query_cache_limit=1M
query_cache_size=32M
query_cache_type=1
max_connections=500
interactive_timeout=100
wait_timeout=100
connect_timeout=10
thread_cache_size=128
key_buffer=16M
join_buffer=1M
max_allowed_packet=16M
table_cache=1024
record_buffer=1M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=10
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2 <--- Try 4 for dual pentiums
myisam_sort_buffer_size=64M
log-bin
server-id=1

[mysql.server]
user=mysql
basedir=/var/lib

[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/lib/mysql/mysql.pid <-- Not necessary
open_files_limit=8192

[mysqldump]
quick
max_allowed_packet=16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

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.

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.

I highly recommend that everyone view this link for more information upgrading from version 3.23 to 4.0.

http://www.mysql.com/doc/en/Upgrading-from-3.23.html

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.

Happy SQL'ing!

_________________________________________

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M




MYSQL performance is now out of this world.

HTH

__________________________________

quote:
Originally posted by Dave#
I'm using less than a quarter of real memory and doing about 18 queries a second:

http://www.cpfc.org/php/mysql.php

I think I'll keep the bufferes the way they are.




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

btw, what script is that you use for mysql info, very detailed, i'd like to get a copy.

---------------------------------------------------

If you recieve the error "Fatal error: Can't find messagefile ...." try simply removing the basedir directive from your my.cnf. In my experience MySQL does a fine job of autodetecting it.

-----------------------------------------------

srv.kznmx.com settings: /etc/my.cnf  & /usr/local/cpanel/whostmgr/my.cnf

[mysqld]
safe-show-database
max_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1500
thread_cache_size = 128
wait_timeout = 14400
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
skip-innodb
thread_concurrency=4

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M

[mysqlhotcopy]
interactive-timeout