• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

MySQL Tuning Suggestions

MattW

Well-known member
#1
Just wondering if someone would be able to take a look at my current MySQL configuration, and possibly suggest any improvements? I've been managing my own servers for the last 5 years, and have never really managed to get a properly tweaked my.cnf.

Anyway, config:
Code:
php -v
PHP 5.3.10 (cli) (built: Feb 25 2012 09:05:55)
Copyright (c) 1997-2012 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2012 Zend Technologies
    with eAccelerator v0.9.6.1, Copyright (c) 2004-2010 eAccelerator, by eAccelerator
    with the ionCube PHP Loader v4.0.12, Copyright (c) 2002-2011, by ionCube Ltd.
    with Suhosin v0.9.33, Copyright (c) 2007-2012, by SektionEins GmbH
Code:
mysql -v
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 42594
Server version: 5.5.21-cll MySQL Community Server (GPL)
current my.cnf (this was auto adjusted by cpanel when I upgraded to 11.32 and MySQL 5.5)
Code:
[myisamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M
[mysqld]
log-bin=mysql-bin
myisam_sort_buffer_size=64M
read_rnd_buffer_size=8M
expire_logs_days=7
query_cache_size=128M
thread_cache_size=8
max_allowed_packet=1M
skip-federated
table_definition_cache=2048
local-infile=0
table_cache=2048
max_connections=60
read_buffer_size=2M
slow_query_log=1
binlog-do-db=british_mods
binlog-do-db=z22se_forum
binlog-do-db=z22se_newshop
binlog-do-db=pure1_shop
server-id=1
slow_query_log_file="/var/log/slow_queries.log"
thread_concurrency=16
sort_buffer_size=2M
port=3306
join_buffer_size=3M
key_buffer=384M
query_cache_limit=4M
socket="/var/lib/mysql/mysql.sock"
skip-external-locking
query-cache-type=1
long_query_time=5
default-storage-engine=MyISAM
innodb_file_per_table=1
[client]
port=3306
socket="/var/lib/mysql/mysql.sock"
[mysqldump]
max_allowed_packet=16M
quick
[mysql]
no-auto-rehash
[isamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M
[mysqlhotcopy]
interactive-timeout
mysqltuner output
Code:
>>  MySQLTuner 1.0.0 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
 
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.21-cll
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 393M (Tables: 674)
[--] Data in InnoDB tables: 224M (Tables: 278)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 3M (Tables: 6)
[!!] Total fragmented tables: 103
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 23h 40m 25s (947K q [11.116 qps], 42K conn, TX: 2B, RX: 258M)
[--] Reads / Writes: 47% / 53%
[--] Total buffers: 672.0M global + 15.2M per thread (60 max threads)
[OK] Maximum possible memory usage: 1.5G (51% of installed RAM)
[OK] Slow queries: 0% (7/947K)
[OK] Highest usage of available connections: 15% (9/60)
[OK] Key buffer size / total MyISAM indexes: 384.0M/670.4M
[OK] Key buffer hit rate: 99.5% (18M cached / 91K reads)
[OK] Query cache efficiency: 60.0% (331K cached / 553K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (6 temp sorts / 29K sorts)
[!!] Joins performed without indexes: 1666
[OK] Temporary tables created on disk: 11% (7K on disk / 63K total)
[OK] Thread cache hit rate: 99% (9 created / 42K connections)
[!!] Table cache hit rate: 0% (1K open / 261K opened)
[OK] Open file limit used: 39% (1K/4K)
[OK] Table locks acquired immediately: 99% (692K immediate / 692K locks)
[!!] InnoDB data size / buffer pool: 224.5M/128.0M
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    join_buffer_size (> 3.0M, or always use indexes with joins)
    table_cache (> 2048)
    innodb_buffer_pool_size (>= 224M)

I'm running on a VPS with 3GB dedicated RAM, 2GB swap, Centos 5.7 32bit, WHM/CPANEL 11.32, NGINXadmin, apache 2.2.22 and memcached.

MySQL on my VPS has previously not use INNODB, as all the phpBB tables were MyISAM.

Thanks,
Matt
 

MattW

Well-known member
#2
Also, current load etc

Code:
free -m
            total      used      free    shared    buffers    cached
Mem:          3072      3024        47          0        232      2026
-/+ buffers/cache:        765      2306
Swap:        2047          0      2047
Code:
uptime
17:27:51 up 1 day, 4 min,  1 user,  load average: 0.24, 0.20, 0.18
The VPS is still running another phpBB3 installed, 3 OpenCart shops, and a WP Blog.

Oh, and I've added this into my config.php file for caching:

PHP:
$config['cache']['enabled'] = true;
$config['cache']['frontend'] = 'Core';
$config['cache']['frontendOptions']['cache_id_prefix'] = 'xf_';
$config['cache']['backend'] = 'Memcached';
$config['cache']['backendOptions'] = array(
        'compression' => false,
        'servers' => array(
                array(
                        // your memcached server IP /address
                        'host' => 'localhost',
 
                        // memcached port
                        'port' => 11211,
                )
        )
);
 

MattW

Well-known member
#3
I've made a few changes to the my.cnf file, and now this is what I'm getting from mysqltuner

Code:
root@vps [~/scripts]# ./mysqltuner.pl
 
 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
 
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.21-cll
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
[--] Data in MyISAM tables: 347M (Tables: 575)
[--] Data in InnoDB tables: 232M (Tables: 285)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 5M (Tables: 6)
[!!] Total fragmented tables: 93
 
-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 10d 13h 18m 22s (9M q [10.785 qps], 568K conn, TX: 24B, RX: 1B)
[--] Reads / Writes: 62% / 38%
[--] Total buffers: 1.7G global + 15.2M per thread (60 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.6G (85% of installed RAM)
[OK] Slow queries: 0% (64/9M)
[OK] Highest usage of available connections: 20% (12/60)
[OK] Key buffer size / total MyISAM indexes: 512.0M/512.5M
[OK] Key buffer hit rate: 100.0% (536M cached / 155K reads)
[OK] Query cache efficiency: 66.6% (4M cached / 6M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (147 temp sorts / 370K sorts)
[!!] Joins performed without indexes: 17372
[OK] Temporary tables created on disk: 11% (81K on disk / 689K total)
[OK] Thread cache hit rate: 99% (13 created / 568K connections)
[!!] Table cache hit rate: 0% (1K open / 2M opened)
[OK] Open file limit used: 38% (1K/4K)
[OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
[OK] InnoDB data size / buffer pool: 232.1M/1.0G
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    join_buffer_size (> 3.0M, or always use indexes with joins)
    table_cache (> 2048)
I'm still after some suggestions if anyone is able to offer them.

Code:
[myisamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M
[mysqld]
innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=1G
log-bin=mysql-bin
myisam_sort_buffer_size=64M
read_rnd_buffer_size=8M
expire_logs_days=7
query_cache_size=128M
thread_cache_size=8
max_allowed_packet=1M
skip-federated
table_definition_cache=2048
local-infile=0
table_cache=2048
max_connections=60
read_buffer_size=2M
slow_query_log=1
binlog-do-db=british_mods
binlog-do-db=z22se_xenforo
binlog-do-db=z22se_newshop
binlog-do-db=pure1_shop
server-id=1
slow_query_log_file="/var/log/slow_queries.log"
thread_concurrency=16
sort_buffer_size=2M
port=3306
join_buffer_size=3M
key_buffer=512M
query_cache_limit=4M
socket="/var/lib/mysql/mysql.sock"
skip-external-locking
query-cache-type=1
long_query_time=5
default-storage-engine=MyISAM
innodb_file_per_table=1
[client]
port=3306
socket="/var/lib/mysql/mysql.sock"
[mysqldump]
max_allowed_packet=16M
quick
[mysql]
no-auto-rehash
[isamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M
[mysqlhotcopy]
interactive-timeout
 

Solidus

Well-known member
#4
Code:
[!!] Joins performed without indexes: 17372
That's extremely high.
Make the following changes,

join_buffer_size = 80M
innodb_buffer_pool_size = 20M
myisam_sort_buffer_size = 32M
#read_rnd_buffer_size=8M (comment it out)
thread_cache_size = 12
max_allowed_packet = 15M
max_connections = 100
read_buffer_size = 3M
sort_buffer_size = 3M
key_buffer_size = 350M (currently yours says key_buffer=)
query_cache_limit = 10M
#default-storage-engine=MyISAM (comment it out)
#innodb_file_per_table=1 (comment it out)
Also add the following somewhere under [mysqld]
query_cache_size = 80M
tmp_table_size = 100M
max_heap_table_size = 100M
Restart MySQL and see how that works for you.
 

MattW

Well-known member
#5
Code:
[!!] Joins performed without indexes: 17372
That's extremely high.
That has always been high, and I think it's down my phpBB3 (I'm hosting another site on the same server which is still running phpBB3).

Will make those other changes and let you know
 

MattW

Well-known member
#8
Why fiddle if your server load is that low?
Fiddling is fun. Perhaps the OP is like me and wants maximum performance and hates to see errors and warnings.
This!

I always like to try and get the best performance

Have made the changes and cleaned up the config file

Code:
root@vps [/etc]# cat my.cnf
[client]
port=3306
socket="/var/lib/mysql/mysql.sock"

[mysqld]
innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=1G
innodb_file_per_table=1
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
log-bin=mysql-bin
myisam_sort_buffer_size=32M
expire_logs_days=7
query_cache_size=128M
thread_cache_size=12
max_allowed_packet=15M
skip-federated
table_definition_cache=2048
local-infile=0
table_cache=2048
max_connections=60
read_buffer_size=3M
sort_buffer_size=3M
slow_query_log=1
slow_query_log_file="/var/log/slow_queries.log"
thread_concurrency=16
port=3306
join_buffer_size=80M
key_buffer_size=350M
query_cache_limit=10M
socket="/var/lib/mysql/mysql.sock"
skip-external-locking
query-cache-type=1
long_query_time=5
query_cache_size = 80M
tmp_table_size = 100M
max_heap_table_size = 100M

[myisamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M

[mysqldump]
max_allowed_packet=16M
quick

[mysql]
no-auto-rehash

[isamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M

[mysqlhotcopy]
interactive-timeout
 

MattW

Well-known member
#10
Just to update this thread, I removed a couple of things from the suggested config settings, as maximum RAM usage went pretty high.
Code:
[client]
port=3306
socket="/var/lib/mysql/mysql.sock"
 
[mysqld]
innodb_additional_mem_pool_size=16M
innodb_buffer_pool_size=1G
innodb_file_per_table=1
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
log-bin=mysql-bin
myisam_sort_buffer_size=64M
expire_logs_days=7
query_cache_size=128M
thread_cache_size=12
max_allowed_packet=15M
skip-federated
table_definition_cache=2048
local-infile=0
table_cache=2048
max_connections=60
read_buffer_size=2M
slow_query_log=1
slow_query_log_file="/var/log/slow_queries.log"
thread_concurrency=16
sort_buffer_size=2M
port=3306
join_buffer_size=3M
key_buffer_size=600M
query_cache_limit=4M
socket="/var/lib/mysql/mysql.sock"
skip-external-locking
query-cache-type=1
long_query_time=5
default-storage-engine=InnoDB
 
[myisamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M
 
[mysqldump]
max_allowed_packet=16M
quick
 
[mysql]
no-auto-rehash
 
[isamchk]
read_buffer=2M
key_buffer=256M
sort_buffer_size=256M
write_buffer=2M
 
[mysqlhotcopy]
interactive-timeout
I've also fully replaced MySQL with Percona Server, and there is an instant speed increase using the existing my.cnf with Percona
 

CTXMedia

Formerly CyclingTribe
#11
I've been managing my own servers for the last 5 years, and have never really managed to get a properly tweaked my.cnf
Good luck - I've spent ages fiddling with mine, and in the end just settled for the best of a bunch of test configs I'd used. :D

I did enjoy the experience though and learned quite a bit from it, so it wasn't time wasted by any means.

Good luck. (y)