All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
Optimizing MySQL using Mysql Tuner; How to interpret results?
MySQL can be quite a resource hog, especially on LEBs. Therefore I am trying to tune it for performance using MySQL Tuner (https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl)
I ran it, and here's my output:
`
-------- Performance Metrics -------------------------------------------------
[--] Up for: 10h 22m 1s (1M q [33.104 qps], 259 conn, TX: 182M, RX: 184M)
[--] Reads / Writes: 67% / 33%
[--] Total buffers: 48.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 316.8M (64% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 2% (2/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/31.7M
[OK] Key buffer hit rate: 95.7% (65M cached / 2M reads)
[!!] Query cache efficiency: 3.5% (29K cached / 838K selects)
[!!] Query cache prunes per day: 709028
[OK] Sorts requiring temporary tables: 0% (88 temp sorts / 412K sorts)
[!!] Joins performed without indexes: 321
[OK] Temporary tables created on disk: 0% (132 on disk / 412K total)
[OK] Thread cache hit rate: 99% (2 created / 259 connections)
[!!] Table cache hit rate: 2% (32 open / 1K opened)
[OK] Open file limit used: 6% (63/1K)
[OK] Table locks acquired immediately: 100% (1M immediate / 1M 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
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_limit (> 1M, or use smaller result sets)
query_cache_size (> 16M)
join_buffer_size (> 128.0K, or always use indexes with joins)
table_cache (> 32)
`
Variables to adjust only says to increase my current variables. But to what should I increase them? Everything times 2 or what?
Thanks!
Comments
It gives you suggestions in the text.
Query_cache_limit should be larger then 1M according to test results.
And so on for the rest of the size settings.
You should also consider indexing your tables.
You should also rerun test after atleast 24hr production use. To see real values.
I once did optimizatiln on a mssql server but didnt monitor it during the night when there was a lot o importing data.
So daily reports were faster by 30-45%
But the nightly tasks slowed down by 18%
All because of indexing.
First of all start with reading detailed description of each configuration variable.
For me the best source is Percona blog
http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/
http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/
After reading you will get better understanding of configuration dependences.
cpanel has a nice video explaining the basics and how to use mysqltunner
videos.cpanel.net/mysql-optimization/
This
"MySQL started within last 24 hours - recommendations may be inaccurate"
So, wait
Thanks for the replies guys, I will look into it
Could use some help. I'm having a temporary table issue.
[--] Data in MyISAM tables: 85M (Tables: 40)
[--] Data in InnoDB tables: 3M (Tables: 33)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 34
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 2d 5h 14m 3s (1M q [7.944 qps], 61K conn, TX: 9B, RX: 191M)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 1.2G global + 1.2M per thread (151 max threads)
[OK] Maximum possible memory usage: 1.4G (34% of installed RAM)
[OK] Slow queries: 0% (11K/1M)
[OK] Highest usage of available connections: 13% (20/151)
[OK] Key buffer size / total MyISAM indexes: 128.0M/25.0M
[OK] Key buffer hit rate: 100.0% (4B cached / 83K reads)
[OK] Query cache efficiency: 83.8% (1M cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 62K sorts)
[!!] Temporary tables created on disk: 48% (36K on disk / 75K total)
[OK] Thread cache hit rate: 99% (20 created / 61K connections)
[!!] Table cache hit rate: 13% (166 open / 1K opened)
[OK] Open file limit used: 1% (181/13K)
[OK] Table locks acquired immediately: 99% (259K immediate / 260K locks)
[OK] InnoDB data size / buffer pool: 3.8M/64.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Temporary table size is already large - reduce result set size
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
table_cache (> 6500)
Hello! necrobumping a thread;
My result is: http://pastebin.com/CYJJ9WS3
I incremented as suggested, but I have this:
query_cache_limit (> 64M, or use smaller result sets) tmp_table_size (> 32M) max_heap_table_size (> 32M)is this normal?
Thanks!
Take the Mysql tuning primer script. It reads the my.conf and gives suggestions.
@ska thanks, I have made the changes
Log slow queries and fix them.
How many websites do you have hosted on your LEB? If you're just hosting a couple web apps whose source you can modify, you'll have far better results squeezing the last drop of performance out of your LEB by introducing a caching system such as memcached (i.e., reduce your SQL queries).
You needn't be an expert programmer to implement memcached on a few resource hog queries.
on that VPS? 1 only, but a gameserver is using MySQL.