0

I'm trying to optimize our MySQL configuration for our server. The reason I believe that MySQL needs to be configured further is because my log has shown that our SELECT queries are taking a long time (10,000+ ms) in some categories.

Our server is a Linux 64-bit running Ubuntu 14.04 on Amazon EC2 t2.small instance (with 2 GB of RAM).

I ran MySQLTuner 1.6.4 and got the following results

-------- Performance Metrics -------------------------------------------------
[--] Up for: 9d 2h 35m 38s (8M q [11.226 qps], 234K conn, TX: 63B, RX: 1B)
[--] Reads / Writes: 95% / 5%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 540.0M global + 1.0M per thread (100 max threads)
[OK] Maximum reached memory usage: 583.6M (29.18% of installed RAM)
[OK] Maximum possible memory usage: 643.9M (32.19% of installed RAM)
[OK] Slow queries: 0% (25/8M)
[OK] Highest usage of available connections: 42% (42/100)
[OK] Aborted connections: 0.00%  (2/234145)
[OK] Query cache efficiency: 57.9% (4M cached / 8M selects)
[!!] Query cache prunes per day: 11915
[OK] Sorts requiring temporary tables: 0% (877 temp sorts / 288K sorts)
[!!] Joins performed without indexes: 7884
[!!] Temporary tables created on disk: 39% (86K on disk / 221K total)
[OK] Thread cache hit rate: 99% (42 created / 234K connections)
[OK] Table cache hit rate: 67% (984 open / 1K opened)
[OK] Open file limit used: 11% (250/2K)
[OK] Table locks acquired immediately: 100% (5M immediate / 5M locks)
[OK] Binlog cache memory access: 99.77% ( 178406 Memory / 178825 Total)

-------- MyISAM Metrics ------------------------------------------------------
[!!] Key buffer used: 18.3% (12M used / 67M cache)
[OK] Key buffer size / total MyISAM indexes: 64.0M/2.3M
[OK] Read Key buffer hit rate: 100.0% (6M cached / 14 reads)
[OK] Write Key buffer hit rate: 100.0% (431K cached / 73 writes)

-------- InnoDB Metrics ------------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 384.0M/202.2M
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 62.73% (15416 used/ 24575 total)
[OK] InnoDB Read buffer efficiency: 100.00% (38241101927 hits/ 38241112309 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 343581 writes)

-------- ThreadPool Metrics --------------------------------------------------
[--] ThreadPool stat is disabled.

-------- AriaDB Metrics ------------------------------------------------------
[--] AriaDB is disabled.

-------- TokuDB Metrics ------------------------------------------------------
[--] TokuDB is disabled.

-------- Galera Metrics ------------------------------------------------------
[--] Galera is disabled.

-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
Variables to adjust:
    query_cache_size (> 40M)
    join_buffer_size (> 296.0K, or always use indexes with joins)
    tmp_table_size (> 40M)
    max_heap_table_size (> 130M)

My my.cnf configuration is as follows

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

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

[mysqld]
# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
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

# MyISAM #
key_buffer_size                = 64M
myisam_recover                 = FORCE,BACKUP

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

# BINARY LOGGING #
log_bin                        = /var/lib/mysql/mysql-bin
expire_logs_days               = 14

# CACHES AND LIMITS #
tmp_table_size                 = 40M
max_heap_table_size            = 130M
query_cache_type               = 1
query_cache_size               = 40M
query_cache_limit              = 10M
thread_cache_size              = 50
open_files_limit               = 1500
table_definition_cache         = 400
table_open_cache               = 1000
max_allowed_packet             = 16M
thread_cache_size              = 50
max_connections                = 100
join_buffer_size               = 296k
sort_buffer_size               = 256k
read_buffer_size               = 128k
read_rnd_buffer_size           = 128k

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 64M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 384M
innodb_buffer_pool_instances   = 1
innodb_log_buffer_size         = 4M
innodb_read_io_threads         = 16
innodb_write_io_threads        = 16

# LOGGING #
log_error                      = /var/log/mysql/error.log
log_slow_queries               = /var/log/mysql/mysql-slow.log
log_warnings                   = 0
long_query_time                = 10

expire_logs_days               = 10
max_binlog_size                = 100M
binlog_format                  = MIXED

[mysqldump]
quick
quote-names
max_allowed_packet             = 16M

[mysql]

[isamchk]
key_buffer                     = 16M

!includedir /etc/mysql/conf.d/

I'm just wondering if I'm missing anything that I can configure further.

What's the best tuning for our server?

  • 1
    I think you should first try to optimize your slow SELECT queries, than trying to optimize your server configuration. What says the execution plan for your select queries? Can you post an execution plan for a slow query? – user743414 Jun 14 '16 at 12:26
  • 1
    Set long_query_time to 1 or 2s. Then add them to your question or aks separate questions about them. Use EXPLAIN to get the query plan. By configuration tuning you can gain 10-20% of performance in good case. Fixing your queries may give you thousand fold speedup or more. – jkavalik Jun 14 '16 at 19:13
  • Temporary tables created on disk: 39% sounds especially bad; let's see your slowest query. – Rick James Sep 12 '16 at 3:39
0

You can increase the innodb_buffer_pool_size and innodb_log_file_size for faster query processing.

Also you can use https://tools.percona.com/wizard to find out the best configuration for Mysql engine depending upon the H/W configuration and workload.

I would recommend increase cache limit and related parameters for processing faster select operations.

Though Mysqltuner script is efficient enough to provide you the best recommendation for parameters adjustments based on server config.

  • as you suggested I tried to set higher innodb_log_file_size and I got this error InnoDB: Error: log file ./ib_logfile0 is of different size 0 67108864 bytes InnoDB: than specified in the .cnf file 0 98566144 bytes! – NineCattoRules Jun 15 '16 at 11:22
  • 1
    you can backup old log files (ib_logfile0) somewhere else, remove them from mysql data directory and restart mysql if possible to resolve this error. It will automatically create new log files of size as specified in my.cnf..hope this helps. – Sachin Singh Jun 15 '16 at 13:05
  • Don't bother changing the log file size unless Uptime / 60 * innodb_log_file_size / Innodb_os_log_written < 10 minutes. – Rick James Sep 12 '16 at 3:38
-1

If you have a server is necessary to increase the number of authorized packages and that will serve you for many applications together puedad send information, if they want to use disconnected programs and then send 'replication' need to modify this option. max_allowed_packet = 512M

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.