• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Reduce temporary tables

#1
after many tests to find the optimal setting, I reached the current my.cnf
skip-innodb
skip-federated
skip-archive
skip-name-resolve
skip-networking
back_log = 100
max_connections = 250
key_buffer_size = 512MB
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 4096M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
open_files_limit=65535
table_definition_cache = 8000
table_open_cache = 8000
thread_cache_size = 16
wait_timeout = 60
connect_timeout = 10
tmp_table_size = 256M
max_heap_table_size = 256M
max_allowed_packet = 64M
max_seeks_for_key=1000
group_concat_max_len = 1024
max_length_for_sort_data = 1024
net_buffer_length = 16384
max_connect_errors = 100000
concurrent_insert = 2
read_rnd_buffer_size = 1M
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size =128M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
range_alloc_block_size = 4096
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 4
thread_concurrency=4
after many tests to find the optimal setting, I reached the current my.cnf
>> MySQLTuner 1.2.1 mod - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Modified by George Liu (eva2000) at http://vbtechsupport.com/
>> Run with '--help' for additional options and output filtering
[!!] Successfully authenticated with no password - SECURITY RISK!
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.2.14-MariaDB-mariadb122
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 158)
[--] Data in MEMORY tables: 2M (Tables: 3)
[!!] Total fragmented tables: 12
-------- Security Recommendations -------------------------------------------
[!!] User 'root@127.0.0.1' has no password set.
[!!] User 'root@localhost' has no password set.
-------- Performance Metrics -------------------------------------------------
[--] Up for: 11h 47m 24s (1M q [44.346 qps], 174K conn, TX: 11B, RX: 428M)
[--] Reads / Writes: 67% / 33%
[--] Total buffers: 896.0M global + 5.3M per thread (250 max threads)
[OK] Maximum possible memory usage: 2.2G (27% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 5% (14/250)
[OK] Key buffer size / total MyISAM indexes: 512.0M/309.9M
[OK] Key buffer hit rate: 99.7% (32M cached / 86K reads)
[OK] Query cache efficiency: 76.4% (929K cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (200 temp sorts / 103K sorts)
[!!] Joins performed without indexes: 35860
[!!] Temporary tables created on disk: 49% (42K on disk / 85K total)
[OK] Thread cache hit rate: 99% (14 created / 174K connections)
[OK] Table cache hit rate: 75% (173 open / 229 opened)
[OK] Open file limit used: 1% (276/16K)
[OK] Table locks acquired immediately: 99% (936K immediate / 936K locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries.
Don't forget to disable slow query logging after troubleshooting
- For MySQL 5.0 http://dev.mysql.com/doc/refman/5.0/en/slow-query-log.html
- For MySQL 5.1 http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html
- For MySQL 5.5 http://dev.mysql.com/doc/refman/5.5/en/slow-query-log.html
- For MySQL 5.6 http://dev.mysql.com/doc/refman/5.6/en/slow-query-log.html
Adjust your join queries to always utilize indexes
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
join_buffer_size (> 1.0M, or always use indexes with joins)
The server has 8GB of Ram, running nginx and php-fpm with no admin panel .. tableas are MyIsam..
Any suggestions to reduce the number of temporary tables?