How to configure and verify MySQL to improve perfomance

Navigation
1

How to configure the MySQL server for better performance in terms of memory usage, processing, and cache usage where the machine has limited resources? Is there a tool that can help me define the best settings for my server?

I am having problems with the response time of SQL queries due to the high number of data in the database, and the problem is not in the queries or modeling, because it worked well in another virtual machine with the same settings, but it was corrupted and lost settings.

  • Total 5GB of data;
  • The tables are InnoDB;
  • The server is currently virtualized with 512Mb of RAM and 20Gb;
  • 32bit architecture;
  • Ubuntu Server 14 operating system;
  • MySQL 5.5.46 release;
  • Tables and indexes are optimized;
  • The system is made in the PHP language;
  • PHP 5.5 version;
  • Use PDO.

The my.cnf file 

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

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

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#bind-address           = 127.0.0.1
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
query_cache_limit       = 1M
query_cache_size        = 16M

#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

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

#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes

#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name

# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[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/
    
asked by anonymous 18.11.2015 / 04:50

1 answer

2

There is a Perl script called MySQLTuner that allows you to quickly and easily view the status of MySQL Server. The script is recommended to assist in configuration to have a performance gain.

To get the script: 

wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt

To run: 

perl mysqltuner.pl

In the official website you have other options to use.

It is necessary to have some Perl modules installed to run, in my attempt it was necessary to install Text::Template 

sudo apt-get install libtext-template-perl

When executing, it will ask for the user and password if there are no users without a password. 

Please enter your MySQL administrative login:
Please enter your MySQL administrative password:

After entering the password will generate a report with the failures and improvement you can perform to improve performance and security.

At the end you will get recommendations for improvements. 

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    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 which have no LIMIT clause
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (1024) variable 
    should be greater than table_open_cache ( 420)
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_limit (> 5M, or use smaller result sets)
    tmp_table_size (> 20M)
    max_heap_table_size (> 20M)
    table_open_cache (> 420)
    innodb_buffer_pool_size (>= 895M) if possible.
    
18.11.2015 / 05:17
Selecting elements by classes with jQuery How to use didSelectRowAtIndex?