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 <[email protected]>
>>  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!