1

I have just made a check for my server with mysqltuner.

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster 
[--] Data in InnoDB tables: 2G (Tables: 54)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 54

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 11h 57m 48s (46K q [1.089 qps], 21K conn, TX: 12M, RX: 4M)
[--] Reads / Writes: 98% / 2%
[--] Total buffers: 7.2G global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 7.5G (63% of installed RAM)
[OK] Slow queries: 0% (8/46K)
[OK] Highest usage of available connections: 7% (7/100)
***[OK] Key buffer size / total MyISAM indexes: 2.0G/96.0K***
[OK] Query cache efficiency: 48.4% (22K cached / 46K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (4 temp sorts / 247 sorts)
[OK] Temporary tables created on disk: 17% (212 on disk / 1K total)
[OK] Thread cache hit rate: 99% (7 created / 21K connections)
[OK] Table cache hit rate: 35% (102 open / 289 opened)
[OK] Open file limit used: 4% (47/1K)
[OK] Table locks acquired immediately: 100% (2K immediate / 2K locks)
[OK] InnoDB data size / buffer pool: 2.7G/5.0G

-------- 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

Please advise me a way to increase total MyISAM indexes (96K) to an effect number. Thanks.

Improve this question

1 Answer 1

Reset to default
0

Your key_buffer_size is way too big.

Since your MyISAM indexes is that small, set it to 128K.

You need to open my.cnf and add this under the [mysqld] group header

[mysqld]
key_buffer_size = 128K;

You don't need to restart mysql. Just run this

SET GLOBAL key_buffer_size = 1024 * 128;

Give it a Try !!!

Based on the mysqltuner output, you can drop the innodb_buffer_pool_size to 3G.

For more clarification on sizing MyISAM and InnoDB caches, see my old post What are the main differences between InnoDB and MyISAM? on how to compute the ideal sizes for both MyISAM and InnoDB.

Improve this answer
4
  • I see "An important variable setting is key_buffer_size; a good value is the 20% of total RAM". How about it ?
    – Toi Lee
    Commented Sep 22, 2014 at 16:07
  • @ToiLee what good is a 2GB key_buffer_size when you have almost no MyISAM tables ? In the case of this question, there is only 96K in indexes. Why waste the RAM ? This is especially true if a DB Server has only InnoDB. You would then focus your RAM need on the InnoDB Buffer Pool.
    – RolandoMySQLDBA
    Commented Sep 22, 2014 at 16:11
  • Thanks so much for your help. So are there any ways to increase the MyISAM indexes without losing the key_buffer_size ?
    – Toi Lee
    Commented Sep 22, 2014 at 16:16
  • Increasing MyISAM indexes only comes about from creating indexes on individual tables. You create indexes to support queries so that the queries run faster. There are many posts in the DBA StackExchange on indexing tables to support queries. You can also google such indexing advice.
    – RolandoMySQLDBA
    Commented Sep 22, 2014 at 16:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.