habe hier auf dieser Maschine ständig Probleme mit hoher CPU Load vor allem bei Mysql, hat jemand vieleicht einen Tip?
Ich hatte mit tuning-primer bisher nicht den gewünschten Erfolg, denke aber das wie unten der KeyBuffer erhöht werden sollte ...
Hat vieleicht jemand eine gute sample config für total > 14173 tables
- AMD Opteron™ 3280, 8 Cores, 8x 2.4 GHz
- 16 GB DDR3-RAM
- 2x 2.000 GB SATA II-HDD
- kein Sosftware Raid
Code
- [mysqld_safe]socket = /var/run/mysqld/mysqld.socknice = -5[mysqld]user = mysqlpid-file = /var/run/mysqld/mysqld.pidsocket = /var/run/mysqld/mysqld.sockport = 3306basedir = /usrdatadir = /var/lib/mysqltmpdir = /tmplanguage = /usr/share/mysql/englishskip-external-lockingskip_name_resolveskip_lockingbind-address = 127.0.0.1symbolic-links = 0key_buffer = 16Mmax_allowed_packet = 16Mthread_stack = 192Kthread_cache_size = 8myisam-recover = BACKUPmax_connections = 130table_cache = 30000open_files_limit = 35000max_heap_table_size = 128Mtmp_table_size = 128Msort_buffer_size = 2Mread_buffer_size = 2Mread_rnd_buffer_size = 1Mjoin_buffer_size = 4Mmax_connect_errors = 10low_priority_updates = 1concurrent_insert = 2table_definition_cache = 6384#thread_concurrency = 10query_cache_limit = 1Mquery_cache_size = 16M#log_slow_queries = /var/log/mysql/mysql-slow.log#long_query_time = 3#log-queries-not-using-indexes#server-id = 1#log_bin = /var/log/mysql/mysql-bin.logexpire_logs_days = 10max_binlog_size = 100M#binlog_do_db = include_database_name#binlog_ignore_db = include_database_name[mysqldump]quickquote-namesmax_allowed_packet = 16M[mysql]#no-auto-rehash # faster start of mysql but no tab completition[isamchk]key_buffer = 16M
Code
- SLOW QUERIES
- The slow query log is NOT enabled.
- Current long_query_time = 10.000000 sec.
- You have 0 out of 30433217 that take longer than 10.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 http://dev.mysql.com/doc/refman/5.1/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 = 130
- Current threads_connected = 1
- Historic max_used_connections = 22
- The number of used connections is 16% of the configured maximum.
- Your max_connections variable seems to be fine.
- MEMORY USAGE
- Max Memory Ever Allocated : 244 M
- Configured Max Per-thread Buffers : 1 G
- Configured Max Global Buffers : 42 M
- Configured Max Memory Limit : 1 G
- Physical Memory : 15.64 G
- Max memory limit seem to be within acceptable norms
- KEY BUFFER
- Current MyISAM index space = 129 M
- Current key_buffer_size = 16 M
- Key cache miss rate is 1 : 85
- Key buffer fill ratio = 100.00 %
- You could increase key_buffer_size
- It is safe to raise this up to 1/4 of total system memory;
- assuming this is a dedicated database server.
- QUERY CACHE
- Query cache is enabled
- Current query_cache_size = 16 M
- Current query_cache_used = 13 M
- Current query_cache_limit = 1 M
- Current Query cache Memory fill ratio = 87.23 %
- Current query_cache_min_res_unit = 4 K
- However, 1324803 queries have been removed from the query cache due to lack of memory
- Perhaps you should raise query_cache_size
- SORT OPERATIONS
- Current sort_buffer_size = 2 M
- Current read_rnd_buffer_size = 1 M
- Sort buffer seems to be fine
- JOINS
- Current join_buffer_size = 4.00 M
- You have had 20414 queries where a join could not use an index properly
- You have had 11401 joins without keys that check for key usage after each row
- 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 = 60140 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 = 30000 tables
- Current table_definition_cache = 6384 tables
- You have a total of 14173 tables
- You have 28349 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 = 128 M
- Current tmp_table_size = 128 M
- Of 581614 temp tables, 52% were created on disk
- Perhaps you should increase your tmp_table_size and/or max_heap_table_size
- to reduce the number of disk-based temporary tables
- Note! BLOB and TEXT columns are not allow in memory tables.
- If you are using these columns raising these values might not impact your
- ratio of on disk temp tables.
- TABLE SCANS
- Current read_buffer_size = 2 M
- Current table scan ratio = 156 : 1
- read_buffer_size seems to be fine
- TABLE LOCKING
- Current Lock Wait ratio = 1 : 7757
- Your table locking seems to be fine