Форум пользователей MySQL

Доброго дня, будьте добры - поправьте что делаю не так ...

Статистика из скрипта tuning-primer.sh


MySQL Version 5.5.31-1~dotdeb.0-log x86_64

Uptime = 17 days 14 hrs 56 min 23 sec
Avg. qps = 118
Total Questions = 180921410
Threads Connected = 2

SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 2.000000 sec.
You have 4470 out of 180921498 that take longer than 2.000000 sec. to complete
Your long_query_time seems to be fine

BINARY UPDATE LOG
The binary update log is NOT enabled.
You will not be able to do point in time recovery
See dev.mysql.com/doc/refman/5.5/en/point-in-time-recovery.html

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 7
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 400
Current threads_connected = 1
Historic max_used_connections = 15
The number of used connections is 3% of the configured maximum.
You are using less than 10% of your configured max_connections.
Lowering max_connections could help to avoid an over-allocation of memory
See "MEMORY USAGE" section to make sure you are not over-allocating

INNODB STATUS
Current InnoDB index space = 2.26 G
Current InnoDB data space = 2.13 G
Current InnoDB buffer pool free = 0 %
Current innodb_buffer_pool_size = 512 M
Depending on how much space your innodb indexes take up it may be safe
to increase this value to up to 2 / 3 of total system memory

MEMORY USAGE
Max Memory Ever Allocated : 1.06 G
Configured Max Per-thread Buffers : 4.76 G
Configured Max Global Buffers : 912 M
Configured Max Memory Limit : 5.65 G
Physical Memory : 23.59 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 96 M
Current key_buffer_size = 256 M
Key cache miss rate is 1 : 149231
Key buffer free ratio = 67 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 104 M
Current query_cache_limit = 8 M
Current Query cache Memory fill ratio = 81.67 %
Current query_cache_min_res_unit = 4 K
However, 13705814 queries have been removed from the query cache due to lack of memory
Perhaps you should raise query_cache_size
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 4 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 4.00 M
You have had 3458774 queries where a join could not use an index properly
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.

OPEN FILES LIMIT
Current open_files_limit = 4506 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_open_cache = 2048 tables
Current table_definition_cache = 400 tables
You have a total of 1273 tables
You have 1797 open tables.
The table_cache value seems to be fine
You should probably increase your table_definition_cache value.

TEMP TABLES
Current max_heap_table_size = 256 M
Current tmp_table_size = 256 M
Of 6221653 temp tables, 11% were created on disk
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 338 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 0 : 180922358
Your table locking seems to be fine



И статистика из MySQLTuner 1.2.0


-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.31-1~dotdeb.0-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 119M (Tables: 37)
[--] Data in InnoDB tables: 2G (Tables: 1144)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 51)
[!!] Total fragmented tables: 1144

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 17d 14h 48m 3s (180M q [118.845 qps], 624K conn, TX: 376B, RX: 45B)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 1.1G global + 12.2M per thread (400 max threads)
[OK] Maximum possible memory usage: 5.9G (25% of installed RAM)
[OK] Slow queries: 0% (4K/180M)
[OK] Highest usage of available connections: 3% (15/400)
[OK] Key buffer size / total MyISAM indexes: 256.0M/96.4M
[OK] Key buffer hit rate: 100.0% (374M cached / 2K reads)
[OK] Query cache efficiency: 89.4% (154M cached / 172M selects)
[!!] Query cache prunes per day: 778001
[OK] Sorts requiring temporary tables: 0% (25 temp sorts / 4M sorts)
[!!] Joins performed without indexes: 3458234
[OK] Temporary tables created on disk: 11% (838K on disk / 7M total)
[OK] Thread cache hit rate: 99% (23 created / 624K connections)
[!!] Table cache hit rate: 0% (1K open / 346K opened)
[OK] Open file limit used: 3% (139/4K)
[OK] Table locks acquired immediately: 100% (59M immediate / 59M locks)
[!!] InnoDB data size / buffer pool: 2.1G/512.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (> 128M)
    join_buffer_size (> 4.0M, or always use indexes with joins)
    table_cache (> 2048)
    innodb_buffer_pool_size (>= 2G)



Большое спасибо за внимание!

Отредактированно zloypk (30.04.2014 12:21:43)