Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- >> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
- >> 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.0.96
- [OK] Operating on 64-bit architecture
- -------- Storage Engine Statistics -------------------------------------------
- [--] Status: ERROR 1109 (42S02) at line 1: Unknown table 'ENGINES' in information_schema
- Use of uninitialized value in concatenation (.) or string at ./mysqltuner.pl
- line 547 (#1)
- (W uninitialized) An undefined value was used as if it were already
- defined. It was interpreted as a "" or a 0, but maybe it was a mistake.
- To suppress this warning assign a defined value to your variables.
- To help you figure out what was undefined, perl tells you what operation
- you used the undefined value in. Note, however, that perl optimizes your
- program and the operation displayed in the warning may not necessarily
- appear literally in your program. For example, "that $foo" is
- usually optimized into "that " . $foo, and the warning will refer to
- the concatenation (.) operator, even though there is no . in your
- program.
- [--] Data in MyISAM tables: 6G (Tables: 653)
- [--] Data in MEMORY tables: 249K (Tables: 3)
- [!!] Total fragmented tables: 22
- -------- Security Recommendations -------------------------------------------
- [OK] All database users have passwords assigned
- -------- Performance Metrics -------------------------------------------------
- [--] Up for: 16h 45m 38s (643K q [10.667 qps], 37K conn, TX: 6B, RX: 136M)
- [--] Reads / Writes: 79% / 21%
- [--] Total buffers: 2.9G global + 64.5M per thread (100 max threads)
- [OK] Maximum possible memory usage: 9.2G (77% of installed RAM)
- [OK] Slow queries: 0% (517/643K)
- [OK] Highest usage of available connections: 23% (23/100)
- [OK] Key buffer size / total MyISAM indexes: 1.5G/3.7G
- [OK] Key buffer hit rate: 100.0% (824M cached / 223K reads)
- [OK] Query cache efficiency: 37.2% (161K cached / 434K selects)
- [OK] Query cache prunes per day: 0
- [OK] Sorts requiring temporary tables: 9% (5K temp sorts / 60K sorts)
- [OK] Temporary tables created on disk: 18% (3K on disk / 21K total)
- [OK] Thread cache hit rate: 97% (1K created / 37K connections)
- [OK] Table cache hit rate: 98% (840 open / 855 opened)
- [OK] Open file limit used: 71% (1K/2K)
- [OK] Table locks acquired immediately: 99% (516K immediate / 521K 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
- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
- -- MYSQL PERFORMANCE TUNING PRIMER --
- - By: Matthew Montgomery -
- MySQL Version 5.0.96 x86_64
- Uptime = 0 days 16 hrs 45 min 55 sec
- Avg. qps = 10
- Total Questions = 643860
- Threads Connected = 3
- Warning: Server has not been running for at least 48hrs.
- It may not be safe to use these recommendations
- To find out more information on how each of these
- runtime variables effects performance visit:
- http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
- Visit http://www.mysql.com/products/enterprise/advisors.html
- for info about MySQL's Enterprise Monitoring and Advisory Service
- SLOW QUERIES
- The slow query log is NOT enabled.
- Current long_query_time = 10 sec.
- You have 518 out of 643916 that take longer than 10 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.0/en/point-in-time-recovery.html
- WORKER THREADS
- Current thread_cache_size = 4
- Current threads_cached = 0
- Current threads_per_sec = 1
- Historic threads_per_sec = 0
- Your thread_cache_size is fine
- MAX CONNECTIONS
- Current max_connections = 100
- Current threads_connected = 7
- Historic max_used_connections = 23
- The number of used connections is 23% of the configured maximum.
- Your max_connections variable seems to be fine.
- No InnoDB Support Enabled!
- MEMORY USAGE
- Max Memory Ever Allocated : 4.21 G
- Configured Max Per-thread Buffers : 6.29 G
- Configured Max Global Buffers : 2.76 G
- Configured Max Memory Limit : 9.06 G
- Physical Memory : 11.81 G
- Max memory limit seem to be within acceptable norms
- KEY BUFFER
- Current MyISAM index space = 3.65 G
- Current key_buffer_size = 1.51 G
- Key cache miss rate is 1 : 3685
- Key buffer free ratio = 67 %
- Your key_buffer_size seems to be fine
- QUERY CACHE
- Query cache is enabled
- Current query_cache_size = 256 M
- Current query_cache_used = 23 M
- Current query_cache_limit = 32 M
- Current Query cache Memory fill ratio = 9.14 %
- Current query_cache_min_res_unit = 4 K
- Your query_cache_size seems to be too high.
- Perhaps you can use these resources elsewhere
- MySQL won't cache query results that are larger than query_cache_limit in size
- SORT OPERATIONS
- Current sort_buffer_size = 32 M
- Current read_rnd_buffer_size = 32 M
- Sort buffer seems to be fine
- JOINS
- Current join_buffer_size = 132.00 K
- You have had 0 queries where a join could not use an index properly
- Your joins seem to be using indexes properly
- OPEN FILES LIMIT
- Current open_files_limit = 2110 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_cache value = 1000 tables
- You have a total of 673 tables
- You have 840 open tables.
- The table_cache value seems to be fine
- TEMP TABLES
- Current max_heap_table_size = 128 M
- Current tmp_table_size = 256 M
- Of 17794 temp tables, 18% were created on disk
- Effective in-memory tmp_table_size is limited to max_heap_table_size.
- Created disk tmp tables ratio seems fine
- TABLE SCANS
- Current read_buffer_size = 128 K
- Current table scan ratio = 104 : 1
- read_buffer_size seems to be fine
- TABLE LOCKING
- Current Lock Wait ratio = 1 : 101
- You may benefit from selective use of InnoDB.
- If you have long running SELECT's against MyISAM tables and perform
- frequent updates consider setting 'low_priority_updates=1'
- If you have a high concurrency of inserts on Dynamic row-length tables
- consider setting 'concurrent_insert=2'.
- +++++++++++++++++++++++++++++++++++++++++
- cat /etc/my.cnf
- [mysqld]
- skip-innodb
- skip-bdb
- datadir=/var/lib/mysql
- socket=/var/lib/mysql/mysql.sock
- user=mysql
- # Default to using old password format for compatibility with mysql 3.x
- # clients (those using the mysqlclient10 compatibility package).
- old_passwords=1
- query_cache_size=256M
- query_cache_limit=32M
- key_buffer_size=1548M
- sort_buffer_size=32M
- read_rnd_buffer_size=32M
- thread_cache_size=4
- table_cache=1000
- tmp_table_size=256M
- max_heap_table_size=128M
- open_files_limit=2000
- innodb_buffer_pool_size=1024M
- # Disabling symbolic-links is recommended to prevent assorted security risks;
- # to do so, uncomment this line:
- symbolic-links=0
- # vbulletin allow mysql 3 char. search
- ft_min_word_len=3
- [myisamchk]
- ft_min_word_len=3
- [mysqld_safe]
- log-error=/var/log/mysqld.log
- pid-file=/var/run/mysqld/mysqld.pid
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement