How to set up and check Mysql to improve performance

Question

How to set up the Mysql server for better performance in relation to memory usage, processing and cache usage where the machine has limited resources? Is there any tool that can help me set 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 queries or modeling, as it worked well in another virtual machine with the same settings, but it was corrupted and I lost the settings.

• Total of 5Gb data;
• The tables are Innodb;
• The server is currently virtualized with 512Mb of RAM and 20Gb;
• Architecture 32bits;
• Ubuntu Server 14 operating system;
• Mysql version 5.5.46;
• Tables and indexes are optimized;
• The system is done in the PHP language;
• PHP version 5.5;
• Utilizes PDO.

The file my.cnf

[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/

Answer 1

There is a Perl script called Mysqltuner which allows a quick and easy view about the state of Mysql Server. The script is recommended to assist in setting up for 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 execute:

perl mysqltuner.pl

In the official website has other use options.

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 running will ask the user and password if there is no user without password.

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

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

In the end you will have the 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.