Home / server / Questions / 231873
Accepted
NinjaCat
Asked: 2011-02-07 08:35:59 +0800 CST

Tuning up a MySQL server

  • 772

I inherited a mysql server, and so I've started with running the MySQLTuner.pl script. I am not a MySQL expert but I can see that there is definitely a mess here. I'm not looking to go after every single thing that needs fixing and tuning, but I do want to grab the major, low hanging fruit.

Total Memory on the system is: 512MB. Yes, I know it's low, but it's what we have for the time being.

Here's what the script had to say:

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
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 326M)

For the variables that it recommends that I adjust, I don't even see most of them in the mysql.cnf file. 

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
innodb_buffer_pool_size         = 220M
innodb_flush_log_at_trx_commit  = 2
innodb_file_per_table           = 1
innodb_thread_concurrency       = 32
skip-locking
big-tables
max_connections             = 50
innodb_lock_wait_timeout    = 600
slave_transaction_retries   = 10
innodb_table_locks      = 0
innodb_additional_mem_pool_size = 20M
user                = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir             = /usr
datadir         = /var/lib/mysql
tmpdir              = /tmp
skip-external-locking
bind-address        = localhost
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 4
myisam-recover          = BACKUP
query_cache_limit       = 1M
query_cache_size        = 16M
log_error               = /var/log/mysql/error.log
expire_logs_days    = 10
max_binlog_size         = 100M
skip-locking
innodb_file_per_table   = 1
big-tables

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M

!includedir /etc/mysql/conf.d/
mysql

2 Answers

  1. Best Answer

    A direct quote from a recent Percona whitepaper:

    "The two most important settings for InnoDB are the buffer pool size and the log file size. Nearly every other setting in the server can be left at its default without causing much harm—or at least, the defaults are acceptable in many cases—but these two settings cannot be left at their defaults, and are never acceptable for production servers."

    Cheers

    • 4

  2. First, the way the my.cnf file works is that if a configuration directive is not in the file, it is assumed to be the default value. (That is, if the my.cnf file is completely empty, all configuration options would be set at defaults.) So if, for example, you wanted to act on this suggestion:

    tmp_table_size (> 16M)

    ...you would just add this line to your my.cnf file:

    tmp_table_size 32M

    (I suggest checking the MySQL documentation before diverging too far from the script's suggestions, but you sound cautious enough that you were planning to do that already.)

    It sounds like some of the things you need to implement will be in your application (e.g. ensuring that connections are properly closed, optimizing queries, etc.). How you do that is going to depend on how that application is built and which language bindings you're using.

    • 0