2

I'm after suggestions on how to increase mysql performance on my server, any help would be greatly appreciated.

  • CPU - 4 core
  • RAM - 16GB
  • Databases - 14 Innodb databases with around 20 tables in each (no other databases).
  • The server is also used as an application and web server.

My my.cnf is as follows:-

[mysql]

# CLIENT #
port                            = 3306
socket                          = /var/run/mysqld/mysqld.sock

[mysqld_safe]

# SAFE #
socket                          = /var/run/mysqld/mysqld.sock
nice                            = 0

[mysqld]

# GENERAL #
user                            = mysql
default-storage-engine          = InnoDB
socket                          = /var/run/mysqld/mysqld.sock
pid-file                        = /var/run/mysqld/mysqld.pid
lc-messages-dir                 = /usr/share/mysql
tmpdir                          = /tmp
basedir                         = /usr
bind-address                    = 127.0.0.1
skip-external-locking

# MyISAM #
key_buffer_size                 = 32M
myisam-recover-options          = FORCE,BACKUP

# SAFETY #
max_allowed_packet              = 16M
max_connect_errors              = 1000000

# DATA STORAGE #
datadir                         = /var/lib/mysql/

# CACHES AND LIMITS #
tmp_table_size                  = 32M
max_heap_table_size             = 32M
query_cache_limit               = 1M
query_cache_size                = 200M
max_connections                 = 500
thread_cache_size               = 50
open_files_limit                = 65535
table_definition_cache          = 1024
table_open_cache                = 2048

# INNODB #
innodb_flush_method             = O_DIRECT
innodb_log_files_in_group       = 2
innodb_log_file_size            = 256M
innodb_flush_log_at_trx_commit  = 1
innodb_file_per_table           = 1
innodb_buffer_pool_size         = 1G

# LOGGING #
log_error                       = /var/log/mysql/error.log

[mysqldump]

max_allowed_packet              = 16M
quick
quote-names

[isamchk]

key_buffer                      = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
  • Do you have actual performance problems? If yes, then I suggest to enable slow query logging and check it after few hours to few days (depends on how much traffic do you server). You can use pt-query-digest to analyze the log. Then try to optimize the worst queries and see how that helps. If you have bad queries, no amount of my.cnf tunning can really help before you make those better. – jkavalik Jul 14 '15 at 6:06
  • 2
    What might be a good idea before doing anything else is to put your app and web servers onto (a) separate machine(s). Not only will that possibly help performance, it will give you far better visibility as to any problem areas with the database. Having said that, in my experience, in the majority of multi-tier apps, the problem is normally at the database. – Vérace Jul 14 '15 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.