MySQL Forums
Forum List  »  Performance

Re: Mysqltuner output - advice
Posted by: Rick James
Date: April 04, 2014 11:13PM

This example of MysqlTuner output is especially bad...

[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
No problem

[--] Data in MyISAM tables: 2G (Tables: 21)
No problem

[--] Data in InnoDB tables: 113G (Tables: 915)
113G - No problem. Why do you have so many tables? (Discuss your app.)

[--] Data in MEMORY tables: 0B (Tables: 2)
No problem. It's unclear why you have two empty ENGINE=MEMORY tables, but they won't hurt.

[!!] Total fragmented tables: 485
Bogus complaint; probably no action needed. (MysqlTuner likes to complain about this. I don't think it matters enough to take any action.)

-------- Performance Metrics -------------------------------------------------
[--] Up for: 31d 3h 53m 41s (302M q [112.267 qps], 70M conn, TX: 3882B, RX: 255B)
Mostly no problem. But... 70 million connections per month? That's about 25/second; rather high. Describe your application.

[--] Reads / Writes: 40% / 60%
No problem.

[--] Total buffers: 11.1G global + 3.1M per thread (1263 max threads)
Not enough info (see below)

[!!] Maximum possible memory usage: 15.0G (102% of installed RAM)
Probably bogus -- There is no simple formula for memory usage. However, it does imply that you have 15GB; is that how much you are provisioned for? The main flaw in this formula is that you _probably_ do not have they full 3.1MB allocated for each thread.

[OK] Slow queries: 0% (130K/302M)
Not so OK... Look at the slow query. To me, this is the best clue for slow performance.

[!!] Highest connection usage: 100% (1266/1263)
That is bad -- Why are the connections not finishing? Do you really have that many users? Or do you have lots of "clients" with persistent connections?

[OK] Key buffer size / total MyISAM indexes: 16.0M/2.8G
[OK] Key buffer hit rate: 99.0% (12B cached / 123M reads)
OK.

[!!] Query cache is disabled
Disabled is usually best -- especially if your tables are written often (as they seem to be).

[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 66M sorts)
OK.

[!!] Joins performed without indexes: 22671697
Do you have any indexes? Let's see SHOW CREATE TABLE for some of your more important tables. This may be the most important thing to pursue.

[OK] Temporary tables created on disk: 1% (1M on disk / 63M total)
Not enough info.

[!!] Thread cache is disabled
Are you running Windows (in which case 0 is ok), or Unix, in which case set thread_cache_size = 20.

[!!] Table cache hit rate: 0% (400 open / 1M opened)
Yikes! Tell us about your application.

[OK] Open file limit used: 0% (47/65K)
Well... 65K is a huge open file limit, especially if you have only 47 files open. Having an unrealistic limit _may_ be eating into RAM (and the 102%). (see below)

[OK] Table locks acquired immediately: 99% (700M immediate / 700M locks)
Good. I think this is only for MyISAM tables.

[!!] InnoDB data size / buffer pool: 113.0G/11.1G
11.1G is a good size for innodb_buffer_pool_size, assuming 15GB of RAM. (I suspect Amazon configured that for you?) Since the buffer_pool is a _cache_, it is reasonable (and common) to have much bigger data than cache.

-------- Recommendations -----------------------------------------------------
General recommendations:
(truncated to just show what i'm confused about)

I say: Fix the things I suggest; then let's test again.

("below")
To get another opinion, please provide output from
SHOW GLOBAL STATUS;
SHOW VARIABLES;
And tell us how much RAM in that RWS instance.

Lag between Master and Slave -- there are many possible issues; let's get past some of the above; then it will be easier to dig into that issue.

Options: ReplyQuote


Subject
Views
Written By
Posted
2941
April 01, 2014 01:16PM
Re: Mysqltuner output - advice
1416
April 04, 2014 11:13PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.