Good Afternoon -
I use mySQL at home primarily as a database for an extensive Kodi library. It's currently hosted on mySQL 5.5 for Windows (on Windows 10 x64), but I'm in the middle of rebuilding it from scratch on the latest version on Ubuntu 16.04 x64.
Updating the database occurs hourly and requires a good amount of resources. I realize that many of these are due to processes outside of the DB, but still am wanting to try to optimize it as much as possible. As the Windows one is still being used, I downloaded and ran mySQL Tuner for Windows on it with the below results:
Note: I started lines which seem to show a potential with an "X"...
- MySQL Tuner 0.8.3 - Peter Chapman <peter@conglomo.co.nz>
- Performing tests on localhost:3306 at Friday, 30 March 2018 5:32:16 PM
- Currently running supported MySQL version 5.5.58-log
- Archive Engine Installed
- Berkeley DB Engine Not Installed
X Federated Engine Not Installed
- InnoDB Engine Installed
X ISAM Engine Not Installed
X NDBCLUSTER Engine Not Installed
- Data in InnoDB tables: 89M (Tables: 46)
X Total fragmented tables: 46
- All database users have passwords assigned
- Up for: 2h 50m 31s (433K q [42.000 qps], 9K conn, TX: 440M, RX: 44M)
- Reads / Writes: 90% / 10%
- Total buffers: 9.1G global + 960.0K per thread (100 max threads)
- Maximum possible memory usage: 9.2G (28% of installed RAM)
- Slow queries: 0% (0/433K)
- Highest usage of available connections: 8% (7/100)
- Key buffer size / total MyISAM indexes: 8.0M/102.0K
- Key buffer hit rate: 100% (39K cached / 0 reads)
X Query cache is disabled
- Sorts requiring temporary tables: 2% (67 temp sorts / 6K sorts)
- Temporary tables created on disk: 4% (927 on disk / 29K total)
- Thread cache hit rate: 99% (7 created / 9K connections)
X Table cache hit rate: 8% (25 open / 282 opened)
- Open file limit used: 0% (1/6K)
- Table locks acquired immediately: 100% (396K immediate / 396K locks)
- InnoDB data size / buffer pool: 89.7M/9.0G
- Run OPTIMIZE TABLE to defragment tables for better performance
- MySQL started within last 24 hours - recommendations may be inaccurate
X Increase table_cache gradually to avoid file descriptor limits
- query_cache_size (>= 8M)
- table_open_cache (> 2000)
- Scan Completed at Friday, 30 March 2018 5:32:17 PM
So... based on the above, would you suggest that I do anything to increase performance or improve health? I plan to run the tool on the new Linux SB once finished so if there are any suggestions for how I may optimize it on the front end based on what its being used for, that would be great too. It's a pretty plain-jane installation as after installation I simply changed the bind address and created a user.
Thank You!