1

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!

Improve this question
10
  • Additional information request. Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) STATUS; F) complete MySQLTuner report G) SHOW ENGINE INNODB STATUS; AND Optional very helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions.
    – Wilson Hauck
    Dec 27, 2020 at 22:07
  • Take a step back, you seem to have some process that you wish to improve the performance of - start by finding out where the time is going for that and work to reduce it. Just increasing the amount of memory available is unlikely to help a loading process - it’s already going to be writing to memory first anyway.
    – Andrew Sayer
    Dec 27, 2020 at 23:34
  • 1
    Thanks for answer @WilsonHauck, here is the pastebin link pastebin.com/ceKe1Cm5
    – mejdenovac
    Dec 28, 2020 at 9:56
  • 1
    Well, you could use the memory storage engine but that would have very severe consequences to your recoverability. There is no way, that I can see, to pin a particular innodb table in ram. The engine will already be very clever about making sure that the bits that would benefit from staying in cache, stay in cache. As I’ve already said, if you want to improve the performance then you need to start by figuring out where the time goes, blindly assuming that ram is your problem is going to end up wasting time and money
    – Andrew Sayer
    Dec 28, 2020 at 12:31
  • 1
    @AndrewSayer what do you think about this: I know in front which tables are mostly used, so I will run this query for example: SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB' and TABLE_NAME in ('table1', 'table2')) A; And I get from this around 350GB. Now my server need to have 350GB of ram instead 125. Any logic? Cost are in the budget so far, and it's possible to go to 700GB of ram. But I think 300-400GB will be ok..
    – mejdenovac
    Dec 28, 2020 at 13:43

1 Answer 1

Reset to default
0

Your ulimit -a report indicates Open Files is limited by the OS to 1024.

From your OS Command Prompt, ulimit -n 32000 would increase this limit dynamically. Shutdown then restart your instance would make the additional file handles available and reduce opened_tables, opened_table_definitions, opened_files.

For this change to persist over OS restarts, follow this url - https://glassonionblog.wordpress.com/2013/01/27/increase-ulimit-and-file-descriptors-limit/

  • but use 32000 rather than 500000 used in their example.

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

innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used every second for the function
innodb_buffer_pool_dump_pct=90  # from 25 (percent) to reduce warmup bp time required
innodb_io_capacity=2000  # from 2500 to extend life of your NVME device
read_buffer_size=192K  # from 1M to reduce handler_read_rnd_next RPS of 160,774

REMOVE innodb_io_capacity_max to allow MySQL to auto calc the max as innodb_io_capacity*2

Many more opportunities exist to improve your configuration. View profile, Network profile for contact and free downloadable Utility Scripts to assist with performance tuning, specifically our findfragtables.sql will be helpful.

Monitoring SHOW GLOBAL STATUS LIKE 'innodb_data_reads'; divided by uptime for read rate per second should be your primary consideration for when you need more RAM to support your innodb_buffer_pool. The provided SHOW GLOBAL STATUS data indicates RPS of 16. Many people are OK with up to 100 RPS. Less RPS is desirable, when funds are available. 0 RPS is likely unnecessary due to the way innodb only ages out of RAM the least recently used data.

Improve this answer
3
  • This query will likely be helpful to monitor with a single query. SELECT * FROM information_schema.global_status WHERE variable_name = 'innodb_data_reads' OR variable_name = 'uptime';
    – Wilson Hauck
    Dec 29, 2020 at 13:48
  • This is a great answer except for the raising innodb_io_capacity part. Raising innodb_io_capacity makes background flushing of the buffer pool more aggressive, reducing it's efficiency, and will ultimately lead to bad performance and reduced SSD life. For solid state devices innodb_flush_neighbors should be turned off, because seek-time is essentially zero, so this is wasted IO, and innodb_io_capacity should be set as low as possible without inducing stalls. See tinyurl.com/y7ekk8qh for a great explanation of what innodb_io_capacity really does and how to tune it.
    – Eljuan
    Dec 29, 2020 at 23:48
  • @mejdenovac Please review my updated answer for innodb_io_capacity, please.
    – Wilson Hauck
    Dec 30, 2020 at 15:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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