Here are some basic, but high impact ways to optimize MySQL for Drupal (there are much more sophisticated and expensive ways to speed up your database of course):

Am not sure if these tips do any good for someone on a shared hosting plan or not (do shared plans have access to a my.cnf file?). Also, I can only confirm these setting for MySQL 4.0.2 thru the latest 4.0.x version, but I think it would work for 5.x (maybe someone can confirm this and leave a comment...). Actually, it will work for below 4.0.2 I think as long as you add set-variable = before each line (see this page for more on set-variable)

1. Get this script, upload it, unzip it, and install it in your /etc folder (at the root of your server, not your Drupal install, right). Then run it from the command line by entering sh tuning-primer.sh

The script will run and what you'll be left with is an output with some info and suggestions about your MySQL settings. Was shocked to learned that on my VPS the cache was not even enabled - very helpful to know!

2. Next open your my.conf file in pico or some kind of proper code/text editor:

Depending on the memory resources you have available you'll want to paste in something like these examples (adjust up or down depending on how your system differs, of course):

For a setup with 500mb or ram paste this in your my.cnf file:

[mysqld]
max_connections = 800
max_user_connections = 800
key_buffer = 36M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 999999
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M

For a system with 256mb of ram:

[mysqld]
max_connections=500
max_user_connections = 500
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 999999
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
safe-show-database

3. Save your my.cnf file and restart mysql. This can be done via WHM or the command line (not sure what that command is - sorry)

Your new settings are now active and you can see run the script from above again and see the difference in your results.

If you'd like to read up on more about mysql tuning I suggest taking a look at these resources:

Tuning MySQL for Drupal

Tuning a MySQL server in 5 minutes

MySQL variables

Comments

sun’s picture

Sounds interesting.

Is it safe to execute that tuning primer script? What is it doing?

Daniel F. Kudwien
unleashed mind

Daniel 'sun' Kudwien
makers99

Caleb G2’s picture

...it was developed by a linux admin. Am short the link to where I got it from originally, but if I'm not mistaken you can download this on mysql.com somewhere even.

In any case, here's a link to the script with a renamed extension (from .sh to .txt - browser doesn't seem to recognize it otherwise) so you can see what's in there.

sepeck’s picture

If you add it to the handbook it won't get lost in the forums.

-Steven Peck
---------
Test site, always start with a test site.
Drupal Best Practices Guide -|- Black Mountain

-Steven Peck
---------
Test site, always start with a test site.
Drupal Best Practices Guide

Caleb G2’s picture

Was going to ask how to do it, then I remembered to search first. Found a great page. "How to add a page to the Handbook"

Updated - I've add a page to the handbook which you can find here.

sepeck’s picture

I like you already :D
As a bonus, you are now listed here: http://drupal.org/node/14205
Don't forget to check the 'Contributed documentation box on your profile too

-Steven Peck
---------
Test site, always start with a test site.
Drupal Best Practices Guide -|- Black Mountain

-Steven Peck
---------
Test site, always start with a test site.
Drupal Best Practices Guide

Caleb G2’s picture

to the handbook page for updated info on this post (which will be added as time and comments/results are accumulated). Am closing my activity on this thread for the most part.

jiten.luhar’s picture

Hi..

I have 8GB RAM on my server running Mysql-4.X version.
I have only 2 database on my server, having one database of large size data of almost 1GB size.

I have also very high interaction with database from PHP scripts.

Here is my server's configuration FYI.

Processor Information
================
Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Xeon(TM) CPU 3.06GHz
Processor #1 speed: 3066.585 MHz
Processor #1 cache size: 512 KB

Processor #2 Vendor: GenuineIntel
Processor #2 Name: Intel(R) Xeon(TM) CPU 3.06GHz
Processor #2 speed: 3066.585 MHz
Processor #2 cache size: 512 KB

Processor #3 Vendor: GenuineIntel
Processor #3 Name: Intel(R) Xeon(TM) CPU 3.06GHz
Processor #3 speed: 3066.585 MHz
Processor #3 cache size: 512 KB

Processor #4 Vendor: GenuineIntel
Processor #4 Name: Intel(R) Xeon(TM) CPU 3.06GHz
Processor #4 speed: 3066.585 MHz
Processor #4 cache size: 512 KB

Memory Information
===============
Memory: 8313984k/8519680k available (1882k kernel code, 73044k reserved, 761k data, 188k init, 7470528k highmem)

System Information
================
2.6.9-55.ELsmp #1 SMP Wed May 2 14:28:44 EDT 2007 i686 i686 i386 GNU/Linux

Current Memory Usage
==================
total used free shared buffers cached
Mem: 8315444 6749872 1565572 0 96416 1367924
-/+ buffers
/cache: 5285532 3029912
Swap: 8385920 793232 7592688
Total: 16701364 7543104 9158260

Current Mysql Configuration
======================

[mysqld]
set-variable = max_connections=800
#safe-show-database
old-passwords = 1
log-slow-queries

key_buffer = 384M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

So any one can suggest me the best MYSQL configuration to handle this??

Thanks.
Jiten.