Re: High memory usage on mysql 5.7.18 running on ubuntu server 16.04

[Peter Brawley]

I ran a "cheatsheet" analysis of the numbers you posted ...

0	Slow queries/sec	                0	Find & optimise those queries
1	Queries not using indexes	        0	Find & optimise those queries using indexes
2	Queries needing full table scans/sec	0.186	Find & optimise those queries
3	InnoDB buffer pool inefficiency	        0.0027	Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
4	InnoDB page reads and buffer pool	0.0031	Grow innodb_buffer_pool_size if > 2% or if innodb_buffer_pool_size < 70% of RAM
5	InnoDB log file thrashing	        0.375	Grow innodb_log_file_size if > 0.5
6	Mins between InnoDB log rotation	0	Grow log size if < 30 min, shrink if > 60 min, but see here.
7	InnoDB buffer pool reads/sec	        6.3731	If high, increase Innodb_buffer_pool_size up to 70% of RAM
8	MyISAM query cache spills/sec	        1.11	If high, grow query_cache_size up to 50
9	MyISAM query cache hits/sec	        0.73	If < 5, try turning query cache off
10	MyISAM cache hit rate	                7.98	If < 10, grow key_buffer_size
11	MyISAM key read rate	                0	Should be high when the buffer is full
12	MyISAM query cache hits/inserts	        0	If low, consider disabling the cache
13	Temp tables created/sec	                0	If high, grow tmp_table_size, max_heap_size. Use HEAP tables in queries
14	Database changes / connection	        0.19	Use db.table syntax
15	Processes/connection	                0	If high, grow thread_cache_size
16	Files opened/sec	                14.52	If > 5, increase table_open_cache
17	Queries/sec	                        17.94	Thousands is busy: you may need better hardware

Edited 1 time(s). Last edit at 06/15/2017 12:56PM by Peter Brawley.