Our site runs some pretty large earnings queries for our reports feature of the user side. The larger accounts are taking anywhere from 5-20 seconds to load on some pages. When these queries run they are calculating the earnings with the MYSQL SUM() function and I believe this is creating large amounts of temporary tables, and it looks like some are being created on the disk. I've been running MYSQL Tuner but it no longer has any variable adjustments for my settings. Here is a sample output:
-------- Performance Metrics -------------------------------------------------
[--] Up for: 5h 14m 29s (1M q [98.277 qps], 143K conn, TX: 2B, RX: 212M)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 4.1G global + 3.8M per thread (600 max threads)
[!!] Maximum possible memory usage: 6.3G (89% of installed RAM)
[OK] Slow queries: 0% (7K/1M)
[OK] Highest usage of available connections: 2% (14/600)
[OK] Key buffer size / total MyISAM indexes: 640.0M/1.0G
[OK] Key buffer hit rate: 99.9% (237M cached / 135K reads)
[OK] Query cache efficiency: 62.5% (756K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (429 temp sorts / 134K sorts)
[OK] Temporary tables created on disk: 21% (25K on disk / 120K total)
[OK] Thread cache hit rate: 99% (14 created / 143K connections)
[OK] Table cache hit rate: 70% (247 open / 348 opened)
[OK] Open file limit used: 0% (440/760K)
[OK] Table locks acquired immediately: 99% (577K immediate / 578K locks)
[OK] InnoDB data size / buffer pool: 144.0K/8.0M
-------- 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
Also here are my MY.CNF settings:
[mysqld]
safe-show-database
skip-locking
skip-networking
max_connections = 600
key_buffer = 640M
table_cache = 380000
query_cache_size = 1024M
query_cache_limit = 12M
query_cache_type = 1
local-infile=0
long_query_time=1
myisam_sort_buffer_size = 64M
max_heap_table_size = 2560M
tmp_table_size = 2560M
max_allowed_packet = 8M
thread_cache_size = 70
thread_stack = 256K
sort_buffer_size = 512K
read_buffer_size = 512K
read_rnd_buffer_size = 512K
binlog_cache_size=64K
long_query_time=1
log-slow-queries=/var/log/slow-query.log
low_priority_updates=1
concurrent_insert=2
log-queries-not-using-indexes
join_buffer_size=2M
thread_concurrency = 16
Any help would be appreciated. I need to get these pages to load faster. The server is a quad core x2 (8 CPU) with 8 GB RAM. The tables are fully indexed. Thannks.
**Solved: I was able to solve this issue by creating a multi-column index for the WHERE columns. Looks like this had nothing to do with slowness created by temporary tables created on disk. QPS jumped from 98 to 212.
You might also try setting query cache type to 2 (on demand) and then change queries that will really benefit to SELECT SQL_CACHE ...blah... This will eliminate overhead for caching queries that will really have no benefit from caching