Magento mySql optimizing

1. analyze your MySQL performance with the MySQL Performance Tuning Primer Script.

# cd /usr/local/src/
# wget http://day32.com/MySQL/tuning-primer.sh
# chmod u+x tuning-primer.sh
# ./tuning-primer.sh

It will ask for your database root user name and password and print out a list of recommendations. Was shocked to learned that on my VPS the cache was not even enabled - very helpful to know!

Another MySQL tuning script using perl:
https://github.com/major/MySQLTuner-perl

# git clone https://github.com/major/MySQLTuner-perl.git
# cd MySQLTuner-perl
# chmod u+x mysqltuner.pl
# perl mysqltuner.pl

Example of Recommendations (on dedicated server with 2GB of RAM)

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)
table_cache (> 400)
innodb_buffer_pool_size (>= 365M)

2. Next open your my.cnf file in 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 of RAM your my.cnf file may look like this:

[mysqld]
max_connections = 150
max_user_connections = 150
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 = 1000
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
innodb-flush-log-at-trx-commit=2

3. Save your my.cnf file and restart mySQL.

service mysql restart 

источник: https://drupal.org/node/85768