Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to optimize our mySQL configuration for our large Magento website. The reason I believe that mySQL needs to be configured further is because New Relic has shown that our SELECT queries are taking a long time (20,000+ ms) in some categories.

I ran MySQLTuner 1.3.0 and got the following results... (Disclaimer: I restarted mySQL earlier after tweaking some settings, and so the results here may not be 100% accurate):

>>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
[OK] Currently running supported MySQL version 5.5.37-35.0
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM 
[--] Data in MyISAM tables: 7G (Tables: 332)
[--] Data in InnoDB tables: 213G (Tables: 8714)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 353)
[!!] Total fragmented tables: 5492

-------- Security Recommendations  -------------------------------------------
[!!] User '@host5.server1.autopartsnetwork.com' has no password set.
[!!] User '@localhost' has no password set.
[!!] User 'root@%' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5h 3m 4s (5M q [317.443 qps], 42K conn, TX: 18B, RX: 2B)
[--] Reads / Writes: 95% / 5%
[--] Total buffers: 35.5G global + 184.5M per thread (1024 max threads)
[!!] Maximum possible memory usage: 220.0G (174% of installed RAM)
[OK] Slow queries: 0% (6K/5M)
[OK] Highest usage of available connections: 5% (61/1024)
[OK] Key buffer size / total MyISAM indexes: 512.0M/3.1G
[OK] Key buffer hit rate: 100.0% (102M cached / 45K reads)
[OK] Query cache efficiency: 66.9% (3M cached / 5M selects)
[!!] Query cache prunes per day: 3486361
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 812K sorts)
[!!] Joins performed without indexes: 1328
[OK] Temporary tables created on disk: 11% (126K on disk / 1M total)
[OK] Thread cache hit rate: 99% (61 created / 42K connections)
[!!] Table cache hit rate: 19% (9K open / 49K opened)
[OK] Open file limit used: 2% (712/25K)
[OK] Table locks acquired immediately: 100% (5M immediate / 5M locks)
[!!] InnoDB  buffer pool / data size: 32.0G/213.4G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    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
    Increasing the query_cache size over 128M may reduce performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (> 512M) [see warning above]
    join_buffer_size (> 128.0M, or always use indexes with joins)
    table_cache (> 12288)
    innodb_buffer_pool_size (>= 213G)

My my.cnf configuration is as follows...

[client]
port            = 3306

[mysqld_safe]
nice            = 0

[mysqld]
tmpdir = /var/lib/mysql/tmp
user            = mysql
port            = 3306
skip-external-locking
character-set-server = utf8
collation-server     = utf8_general_ci
event_scheduler      = 0

key_buffer              = 512M
max_allowed_packet      = 64M
thread_stack            = 512K
thread_cache_size       = 512
sort_buffer_size        = 24M
read_buffer_size        = 8M
read_rnd_buffer_size    = 24M
join_buffer_size        = 128M
# for some nightly processes client sessions set the join buffer to 8 GB

auto-increment-increment = 1
auto-increment-offset    = 1

myisam-recover          = BACKUP
max_connections         = 1024
# max connect errors artificially high to support behaviors of NetScaler monitors
max_connect_errors      = 999999
concurrent_insert       = 2
connect_timeout         = 5
wait_timeout            = 180
net_read_timeout        = 120
net_write_timeout       = 120
back_log                = 128
# this table_open_cache might be too low because of MySQL bugs #16244691 and #65384)
table_open_cache        = 12288
tmp_table_size          = 512M
max_heap_table_size     = 512M
bulk_insert_buffer_size = 512M
open-files-limit        = 8192
open-files              = 1024

query_cache_type        = 1
# large query limit supports SOAP and REST API integrations
query_cache_limit       = 4M
# larger than 512 MB query cache size is problematic; this is typically ~60% full
query_cache_size        = 512M

# set to true on read slaves
read_only = false

slow_query_log_file     = /var/log/mysql/slow.log
slow_query_log          = 0
long_query_time         = 0.2

expire_logs_days        = 10
max_binlog_size         = 1024M
binlog_cache_size       = 32K

sync_binlog             = 0

# SSD RAID10 technically has a write capacity of 10000 IOPS
innodb_io_capacity              = 400
innodb_file_per_table
innodb_table_locks              = true
innodb_lock_wait_timeout        = 30
# These servers have 80 CPU threads; match 1:1
innodb_thread_concurrency       = 48
innodb_commit_concurrency       = 2
innodb_support_xa               = true

innodb_buffer_pool_size = 32G
innodb_file_per_table
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size  = 2G

skip-federated

[mysqldump]
quick
quote-names
single-transaction
max_allowed_packet      = 64M

I have a monster of a server here to power our site because our catalog is very large (300,000 simple SKUs), and I'm just wondering if I'm missing anything that I can configure further. :-)

Thanks!

share|improve this question

migrated from serverfault.com May 29 '14 at 14:50

This question came from our site for system and network administrators.

    
Have you run a full reindex on your catalog? –  Michael Hampton May 28 '14 at 21:04
    
Hey Michael. I have -- thank you! –  Brian Schroeter May 28 '14 at 21:19

2 Answers 2

you should probably start from following:

/usr/share/mysql/my-innodb-heavy-4G.cnf

and work your way up from there, but keep in mind it's not just server that needs to get optimized, it's queries and tables (indexes) as well.

query_cache_size should not be more then 128M as it hurts performance more then you gain..

share|improve this answer

This is such a funny coincidence. I just answered a question concerning mysqltuner.pl

Recommendation for mysql please :MySQLTuner

I'll try to do the same for your question.

OBSERVATIONS FROM mysqltuner.pl

[!!] Maximum possible memory usage: 220.0G (174% of installed RAM)
[!!] InnoDB  buffer pool / data size: 32.0G/213.4G
[OK] Key buffer size / total MyISAM indexes: 512.0M/3.1G
[OK] Highest usage of available connections: 5% (61/1024)
[--] Total buffers: 35.5G global + 184.5M per thread (1024 max threads)

OBSERVATIONS FROM my.cnf

sort_buffer_size        = 24M
read_buffer_size        = 8M
read_rnd_buffer_size    = 24M
join_buffer_size        = 128M

ANALYSIS

SUGGESTIONS

SUGGESTION #1 : Set query_cache_size to 0. InnoDB performs better without it.

SUGGESTION #2 : Raise key_buffer_size to 3G if you have heavy reads against the MyISAM tables

SUGGESTION #3 : Raise innodb_buffer_pool_size to 75G

SUGGESTION #4 : Since innodb_buffer_pool_size is more than half the installed RAM, set innodb_buffer_pool_instances to the number of cores on the server. If the server is a VM, leave it at the default.

SUGGESTION #5 : Since max_connections * (join_buffer_size + sort_buffer_size + read_buffer_size + read_rnd_buffer_size), you need to decrease the max_connections.

SUGGESTION #6 : After changing the settings and restarting mysql, rerun mysqltuner.pl. If the Maximum possible memory usage: ... (.... of installed RAM) message says more than 75% of installed RAM, repeat SUGGESTION #5.

Give it a Try !!!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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