HOWTO: Optimize MySQL 4.0.12 after installation 2-Parts Options

[aussie]

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

Continued .....

[aussie]

Continued ...

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!

[graziano]

Hello,

great HOW TO .

However please can you tell me for what system
do you suggest the mysql config file above ?

Suppose a Pentium 4 server with 100 users , 1 GB RAM
and 512 Swap . The config file suggested above it
still ok ?

[aussie]

The configuration above should be just fine. If you find your loads are too high you may adjust the above config. Give it a whirl. I am also running the above configuration on a number of P4's.

[aussie]

*BUMP* people are asking questions about their priv tables that are clearly outlined here so by bumping this im hoping they will see it!

[Edgewize]

Some thoughts ...

I personally suggest the following my.cnf changes for people with a single-processor server and 512MB RAM:

thread_cache_size=50
key_buffer=40M
table_cache=384
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
thread_concurrency=2

For people with 1 GB ram:

thread_cache_size=80
key_buffer=150M
table_cache=512
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
thread_concurrency=2


Larger read buffer sizes are more wasteful than helpful. Under your settings, each mysql client will use 6 MB of ram. With just 80 clients and apache running, you will run out of physical memory on a 512MB server and start using swap. On a busy server, that's a one-way trip to 200+ load and a server reboot ticket.

Note that key_buffer is shared once among all clients, and a large value is recommended to improve performance on tables with unique keys (like a primary id number).

[graziano]

Originally posted by Edgewize
Some thoughts ...

I personally suggest the following my.cnf changes for people with a single-processor server and 512MB RAM:

thread_cache_size=50
key_buffer=40M
table_cache=384
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
thread_concurrency=2

For people with 1 GB ram:

thread_cache_size=80
key_buffer=150M
table_cache=512
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
thread_concurrency=2


Larger read buffer sizes are more wasteful than helpful.  Under your settings, each mysql client will use 6 MB of ram.  With just 80 clients and apache running, you will run out of physical memory on a 512MB server and start using swap.  On a busy server, that's a one-way trip to 200+ load and a server reboot ticket.

Note that key_buffer is shared once among all clients, and a large value is recommended to improve performance on tables with unique keys (like a primary id number).

uhm ... I think that we need an HOW TO calculate those MB values ....

Those parameters depends on

number of cpu (1 or 2)
quantity of phisical RAM

Anyone can write an HOW TO calculate those values ?

[ramprage]

After trying that config on a Cpanel 6.2 server - Celeron 1.3 ghz with 512 mb ram I received this:


030411 08:01:57 mysqld started
Unknown suffix ' ' used for variable 'thread_concurrency' (value '2 <--- Try 4 for dual pentiums')
/usr/sbin/mysqld: Error while setting value '2 <--- Try 4 for dual pentiums' to 'thread_concurrency'
030411 08:01:58 mysqld ended


in mysql.log



Quick edit of my.cnf and I removed the <--- Try for dual pentiums

Thanks for the tweaking tips!

[aussie]

Originally posted by ramprage
After trying that config on a Cpanel 6.2 server - Celeron 1.3 ghz with 512 mb ram I received this:


030411 08:01:57  mysqld started
Unknown suffix ' ' used for variable 'thread_concurrency' (value '2 <--- Try 4 for dual pentiums')
/usr/sbin/mysqld: Error while setting value '2 <--- Try 4 for dual pentiums' to 'thread_concurrency'
030411 08:01:58  mysqld ended


in mysql.log



Quick edit of my.cnf and I removed the <--- Try for dual pentiums

Thanks for the tweaking tips!

Its thread_concurrency=2 not

'thread_concurrency' (value '2 <--- Try 4 for dual pentiums')

Those were only commens i put in the file. I though you knew that you are supposed to remove comment.

[Networkologist]

I have been trying every HowTo I can find to try and fix a problem one of my clients is having. I just tried aussie's my.cnf on my P4 2gig with 1.5 megs and mysql failed to restart.

I removed the entries and it restarted.


:confused:

[Emrys]

Thanks aussie. Put those settings in my my.cnf and things sped up a good bit....

Now I need to figure out how to push images a little faster. On to Apache searching I guess....

[aussie]

Originally posted by Networkologist
I have been trying every HowTo I can find to try and fix a problem one of my clients is having. I just tried aussie's my.cnf on my P4 2gig with 1.5 megs and mysql failed to restart.

I removed the entries and it restarted.


:confused:

The problem could be anything as simple and a typo or invalid startup variable. What does you /var/log/mysql.log say? Have you looked? Your problem is mysql was not shutdown properly therefore the pids left over and still running did not allow mysql to restart after your changes. Also, you should shutdown mysql from the command line, not from WHM because WHM does not shut it down but rather tries to restart it and thats not what you want to do. After you make the changes to my.cnf you need to shutdown mysql from the command line /etc/rc.d/init.d/mysql stop then you need to execute killall -9 mysqld to make sure there are no old processes running. Do a ps aux to make sure all your [mysqld] prcess have been removed. If there are old processes running, your never going to be able to restart mysql with the new changes. Then you need to load your mysql log file so that you can monitor the restart and watch for errors. If you execute tail -f /var/log/mysqld.log you can watch the log in real time while you restart mysql from WHM. The tail command loads the log file and places it to the last record so you can watch the status of error msgs during the restart. While watching the log restart mysql from WHM. If there are any errors then you will know what those are and you can correct them in my.cnf. If there are other reasons why it fails to restart, you will also know the reasons why. To exit the log file type cntrl-x or [cntrl-c[/b] whichever works.

[eXplosive]

Works great!

[stlracing]

On an ENSIM 3.1.10 box, is this file known as /etc/my.cnf.admin_appl

or do I need a separate /etc/my.cnf

[aussie]

On Ensim its also in /etc/my.cnf.

[Networkologist]

The problem could be anything as simple and a typo or invalid startup variable.

Sorry for the dealy in responding. Exim went south for a few days and although I couldn't figure out why, it magically healed itself. (may have had something to do with my trying to use your RBL HowTo )

Anyway, now that my clients have stopped screaming at me, I'll have another run at it.

[serverdummy]

Originally posted by Edgewize
Some thoughts ...

I personally suggest the following my.cnf changes for people with a single-processor server and 512MB RAM:

thread_cache_size=50
key_buffer=40M
table_cache=384
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
thread_concurrency=2

What would you suggest for someone with 256 MB ram?

[Proetorian]

Excellent 'How to Optimize' for MySQL4.

Thanks aussie.

[Guest_LangTuDaTinh_*]

mysql_fix_privilege_tables



This scripts updates the mysql.user, mysql.db, mysql.host and the
mysql.func tables to MySQL 3.22.14 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION or want to use the more secure passwords in 3.23

If you get 'Access denied' errors, you should run this script again
and give the MySQL root user password as an argument!
Converting all privilege tables to MyISAM format
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

If your tables are already up to date or partially up to date you will
get some warnings about 'Duplicated column name'. You can safely ignore these!
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Creating Grant Alter and Index privileges if they don't exists
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Adding columns needed by GRANT .. REQUIRE (openssl)
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Creating the new table and column privilege tables
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
Changing name of columns_priv.Type -> columns_priv.Column_priv
You can ignore any Unknown column errors from this
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Fixing the func table
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Adding new fields used by MySQL 4.0.2 to the privilege tables
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)



PLEASE HELP

[Proetorian]

Originally posted by LangTuDaTinh
mysql_fix_privilege_tables  



This scripts updates the mysql.user, mysql.db, mysql.host and the
mysql.func tables to MySQL 3.22.14 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION or want to use the more secure passwords in 3.23

If you get 'Access denied' errors, you should run this script again
and give the MySQL root user password as an argument!
Converting all privilege tables to MyISAM format
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

If your tables are already up to date or partially up to date you will
get some warnings about 'Duplicated column name'. You can safely ignore these!
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Creating Grant Alter and Index privileges if they don't exists
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Adding columns needed by GRANT .. REQUIRE (openssl)
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Creating the new table and column privilege tables
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
Changing name of columns_priv.Type -> columns_priv.Column_priv
You can ignore any Unknown column errors from this
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Fixing the func table
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

Adding new fields used by MySQL 4.0.2 to the privilege tables
You can ignore any Duplicate column errors
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)



PLEASE HELP

You need to add your root password to the end of the script as follows:

mysql_fix_privilege_tables 'password'