I'm currently testing a new MySQL server, but I'm new in this so I'm not sure about my parameters, because the RAM is always above 90% of usage.
The server will be setup for a CRM environment of 100 people.
Server specs:
- MySQL version 5.7.28-0ubuntu0.18.04.4-log
- 16GB of RAM
Changes to mysqld.conf:
key_buffer_size = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
# My changes
innodb_buffer_pool_instances = 12
innodb_buffer_pool_size = 12G
innodb_flush_method = O_DIRECT
innodb_log_file_size = 1G
innodb_page_cleaners = 4
innodb_purge_threads = 4
max_connections = 151
open_files_limit = 112274
table_open_cache = 56137
log_output = FILE
default-storage-engine = INNODB
character-set-server = utf8
innodb_lock_wait_timeout = 300
read_buffer_size = 64K
# Recommendations [mysqltuner]
long_query_time = 10
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 2M
join_buffer_size = 512K
table_definition_cache = 28468
log_bin = /var/log/mysql/mysql-bin.log
What mysqltuner says in InnoDB Metrics:
-------- InnoDB Metrics ----------------------------------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Thread Concurrency: 0
[OK] InnoDB File per table is activated
[!!] InnoDB buffer pool / data size: 12.0G/22.2G
[!!] Ratio InnoDB log file size / InnoDB Buffer pool size (66.6666666666667 %): 4.0G * 2/12.0G should be equal to 25%
[!!] InnoDB buffer pool instances: 8
[--] Number of InnoDB Buffer Pool Chunk : 96 for 8 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 99.99% (5004553901 hits/ 5004951598 total)
[OK] InnoDB Write log efficiency: 96.96% (8250637 hits/ 8509152 total)
[OK] InnoDB log waits: 0.00% (0 waits / 258515 writes)
Can you help me with this? Should I change anything else in the conf file? Should I upgrade the hardware?
EDIT:
GLOBAL STATUS: https://pastebin.com/1kbHTGkL
GLOBAL VARIABLES: https://pastebin.com/KUrBh9wr
FULL PROCESSLIST: https://pastebin.com/39GZdeWB
MySQLTuner report: https://pastebin.com/Khdgf7GP
EDIT 2:
I'm using SSD Storage
ulimit: https://pastebin.com/aUAtjnUN
iostat: https://pastebin.com/kjr9GaLB
Best Answer
In general, you want to utilize as much RAM as possible (leaving enough for the O/S, etc), since that means data is in-memory instead of being on disk.
For a CRM system serving 100 people, you may find 16GB of RAM dedicated to the server is not enough. My phone has 8GB of RAM, for instance, and it's just a phone.
In-memory access times for data is a couple of orders of magnitude faster than accessing data on disk.