Results 1 to 4 of 4
Thread: MySQL Optimization
09-04-2004, 02:51 PM #1
- Join Date
- Jan 2004
MySQL 4.0.12 a vast improvement over any of its predecessors. This guide is basically some basics on how to optimize MySQL 4.0.12 after installing it on your server. Of course it varies on every machine but this is just the things I do to some of my machines.
Ok well MySQL 4.0.12 improves so much that its hard to say. You will notice improvmens in speed and performance. On the downside however there are some slight incompatibilities with some forums like Invision board, where the tables lockup. This can be fixed via phpmyadmin but it is not nice and costs a lot of time. None the less I would advise upgrading.
Ok first thing first, we need to update and add the new privileges and features to the privilege table. If you dont do this all users will be able to see everyones DBs in phpMyAdmin. This is a security yissue so we have to fix it.
Log in to your box as root and execute the following command;
Next we optimize all the DBs. This goes through all your databases and optimizes all the tables for the best performance
mysqlcheck -o -u root -p --all-databases
Ok now we need to go to my.cnf. For the best optimization you should uipgrade your STARTUP SQL VARIABLES in my.cnf with the new SQL VARIABLE names that have been added to this release. You dont have to do it. It is optional
Changes (taken from mysql website)
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
Look for any OLD SQL variable names in your my.cnf, change them to reflect the new variable names. 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;
This has dropped my load so much. Look up the meaning of them on the MySQL site.
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
ohh and remove set-variable from your my.cnf. Its not needed
Just thought id include my.cnf that I use on one of my servers. Its a good starting point so try things out and change it. Remember to monitor it.
# Try number of CPU's*2 for thread_concurrency
thread_concurrency=2 <--- Try 4 for dual pentiums
pid-file=/var/lib/mysql/mysql.pid <-- Not necessary
Well now, that is just the basics like I said. I dont want to be blamed if something goes wrong but I will try to help as much as I can.
Have fun and hope it helps
09-06-2004, 01:36 PM #2
- Join Date
- Feb 2004
Nice review of the MySQL features and how best to optimize it. Nice!The Web Hosting Show - The Voice of the Web Hosting World
Think of it as talk radio mixed with Web hosting discussion for both Web hosts and Web hosting clients! New episode every Monday!
09-06-2004, 03:06 PM #3kirukkanGuest
Also, is anyway that we can find who is using more mysql connections. Please let me know.
09-06-2004, 03:11 PM #4
Originally Posted by ananthan
- Join Date
- Jan 2004