0

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

10
  • 1
    Would you rather memory be wasted? Is there anything else apart from MySQL running on that server?
    – mustaccio
    Nov 28 '19 at 16:02
  • 1
    Then what's wrong with MySQL using 90% of memory that's dedicated for the database?
    – mustaccio
    Nov 28 '19 at 16:06
  • 1
    I was just wondering if that's normal for that environment, of a maximum of 100 users. Nov 28 '19 at 16:07
  • 1
    @WilsonHauck I've updated the question with some of that information Dec 4 '19 at 15:57
  • 1
    @WilsonHauck added new information. Dec 5 '19 at 9:49
5

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.

0

Rate Per Second = RPS - Suggestions to consider for your my.cnf [mysqld] section

innodb_io_capacity=1900  # from 200 to use more of your SSD iops capacity
thread_cache_size=100  # from 8 to reduce threads_created count of 12,926 in 2 days
read_rnd_buffer_size=128K  # from 256K to reduce handler_read_rnd_next RPS of 35,547
read_buffer_size=128K  # from 64K to reduce handler_read_next RPS of 12,296

You will find these configuration changes will REDUCE CPU busy significantly. To calculate effectively busy, % busy/cpu count is about 45% busy with your 2 cpu's.

Other observations, There are more changes needed, this is the tip of the iceberg, but a good start. We do not normally see com_admin_changes and com_change_db rates above 80 per second. We do not normally see com_rollback_to_savepoint averaging 1 every 10 seconds. Select_scan indicates you are doing 61 table scans per second. NDX's are needed.

1
  • @jose-m-martin Were the suggestions above of any value to you? Please comment on either positive or negative results. Dec 11 '19 at 18:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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