This project has moved. For the latest updates, please go here.

Newbie - where do I start?

Sep 24, 2014 at 8:43 PM
I'm no DBA, but I do manage a Windows server running IIS and MySQL driven website. This is a great tool but now I have a bunch of red Xs and don't know what to do about them! Any recommended websites would be greatly appreciated.


Berkeley DB Engine Not Installed
Federated Engine Not Installed
ISAM Engine Not Installed
NDBCLUSTER Engine Not Installed
Total fragmented tables: 145

Key buffer hit rate: 75% (873 cached / 215 reads)

Temporary tables created on disk: 36% (28K on disk / 79K total)

Connections aborted: 12%
InnoDB data size / buffer pool: 71.6M/256.0M

Run OPTIMIZE TABLE to defragment tables for better performance
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Your applications are not closing MySQL connections properly
tmp_table_size (> 27M)
max_heap_table_size (> 16M)
Coordinator
Sep 25, 2014 at 7:36 AM
Nothing seems to be terribly amiss with your results. I wouldn't worry too much about the red X's. MySQL Tuner is designed for experienced DBAs who need to diagnose an existing problem, and there doesn't seem to be any real problems with your configuration in the results you have posted.
Sep 25, 2014 at 7:46 PM
ok thanks. I ran this on my production machine and have some different results that may actually need addressing...?

Up for: 127d 0h 45m 55s (224M q [20.000 qps], 3M conn, TX: 6148G, RX: 50G)
  • Query cache is disabled -- that seems bad
  • Joins performed without indexes: 1528191 -- that seems like a lot
  • Temporary tables created on disk: 41% (16M on disk / 39M total)
  • Table cache hit rate: 0% (81 open / 494K opened)
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
tmp_table_size (> 27M)
max_heap_table_size (> 16M)
table_cache (> 256)
Sep 25, 2014 at 8:33 PM
Edited Sep 25, 2014 at 8:34 PM
reading up on the web, it sounds like Query caching was from years past. I have a 4 core server with 4GB RAM.


http://dba.stackexchange.com/questions/27328/how-large-should-be-mysql-innodb-buffer-pool-size

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

Suggests innodb_buffer_pool_size = 1G mine is only set at 128M, my database (exported) is 88MB


http://dba.stackexchange.com/questions/5666/possible-to-make-mysql-use-more-than-one-core/5670#5670

innodb_thread_concurrency = 0 - mine already set to 0
innodb_read_io_threads = 64 - mine is 4 now
innodb_write_io_threads = 64 - mine is 4 now

Coordinator
Sep 25, 2014 at 9:24 PM
I would suggest asking a DBA for help and advice in optimising MySQL. The scope of this discussion forum is product support for MySQL Tuner, not what to do with the results from MySQL Tuner. As I stated previously, the results are for an experienced DBA to help them diagnose and solve the issues they are experiencing.

MySQL optimisation can be a difficult task - there are many factors to take into consideration, such as server workload, the applications running on the database, what the server is shared with, etc. For example, you may not necessarily want MySQL using all of your CPU cores - this may result in a slow down in your web server.

As a final note, the results from MySQL Tuner are designed for MySQL 4.0, 4.1, 5.0, and 5.1. If you are using a later version, the results may be irrelevant. If you are keen to learn how to optimise MySQL, I would suggest reading the MySQL documentation for the version of MySQL you are using.
Marked as answer by pmachapman on 1/14/2016 at 5:31 PM