I have a question regarding Mysql server 5.7, put the correct value for innodb_buffer_pool_size or increase RAM. Maybe I'm wrong, I'm not an expert, but let me explain what is it about.
Server configuration: Mysql 5.7.32 server is running on Ubuntu 20.04 server, specs are 125GB of RAM, 64 cores of CPU and it have also 3TB partition for the database. SSD is nvme and CPU is AMD EPYC 7502P, so the machine is dedicated.
Here is an mysqld.cnf file conf:
[mysqld] # # * Basic Settings # #innodb_monitor_enable = all #performance_schema = ON default-storage-engine = innodb tmpdir = /data/mysql_tmp skip-log-bin innodb_support_xa = 0 # # * innodb settings # innodb_read_io_threads = 64 innodb_write_io_threads = 64 innodb_buffer_pool_instances = 64 innodb_buffer_pool_size = 85G innodb_buffer_pool_chunk_size = 256M innodb_io_capacity = 2500 innodb_io_capacity_max = 5000 innodb_log_file_size = 13G innodb_log_buffer_size = 32M innodb_flush_log_at_trx_commit = 0 query_cache_size = 0 query_cache_type = 0 sort_buffer_size = 10M join_buffer_size = 1M read_buffer_size = 1M key_buffer_size = 16M thread_cache_size = 100 read_rnd_buffer_size = 1M max_allowed_packet = 1073741824 net_buffer_length = 1048576 #innodb_fill_factor = 50 max_connections = 150 table_open_cache = 3000 table_open_cache_instances = 55 back_log = 65535 wait_timeout = 31536000 connect_timeout = 31536000 interactive_timeout = 31536000 net_read_timeout = 10000 net_write_timeout = 10000 # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 0.0.0.0 # Procedures tunning sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION event_scheduler = on
The next thing that I can share is that on this server we have 18 different databases, a lot of them are small. The main problem is with 2 big databases. First database have around 700GB and around 100 tables. Second have 200GB of data and around 15 tables. And those two constantly expands (each day).
Let me now explain what I need. I got some ticket on my work to do make adjustments with the server regarding and adjusting innodb_buffer_pool_size , and I need to calculate how much more RAM we need to add to this server so Mysql can put data in innodb_buffer_pool_size. Because there is another service that need to pull the data in some their weird specific way and we wanna to increase speed up for them somehow. But I'm not sure how can I do that and is it possible.
Question is: Can we just somehow put specific large tables from these two databases in innodb_buffer_pool_size, like to tell Mysql Server just keep them in RAM bassed? Until we have some zero I/O ? Or Mysql can do there something or some other service can do this?
Some lead guy at my work told me that this is possible, like to subtract unneeded tables, or exclude tables? Combined with this "zero I/O" stuff !? Really don't know what to google and what exactly can I do there. I am aware of memcached, also to have the other SQL server installed, but that isn't an option right now.
I've tried to tune the server with a PERL script called mysqltuner.pl , also tried with some queries similar from this page: How large should be mysql innodb_buffer_pool_size? , and I got the info like install 2TB of ram and simillar. Which for me is insane.
Hope someone can help and suggest/guide for some solution to this or maybe I'm looking wrong on this with innodb_buffer_pool_size variable. Thank you!