0

I have a small mariadb standalone server (RHEL9 x64), 8GB ram holding a (small) java app and a mariadb 11.4.5 database server (25GB data). If I run mysqltuner (2.7.0) it tells:

✔  InnoDB Buffer Pool size ( 3.9G ) under limit for 64 bits architecture: (17179869184.0G )
✘  InnoDB buffer pool / data size: 3.9G / 17.9G
...
✘  InnoDB Write Log efficiency: 81.81% (1183667 hits / 1446837 total)
...
General recommendations:
    Restrict Host for 'signserver'@'%' to 'signserver'@LimitedIPRangeOrLocalhost
    RENAME USER 'signserver'@'%' TO 'signserver'@LimitedIPRangeOrLocalhost;
    MySQL was started within the last 24 hours: recommendations may be inaccurate
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
    Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
    skip-name-resolve=ON
    innodb_buffer_pool_size (>= 17.9G) if possible.
    innodb_log_buffer_size (> 16M)

Needing 18GB ram to host a 25GB db is a non-sense for me (wondering how much would be needed to host 1TB db). Can someone tell me why mysqltuner tells mariadb requires so much?

Is there a way to know how much mariadb really "needs" in innodb_buffer_pool_size?

Thanks.

Improve this question

2 Answers 2

Reset to default
0

MariaDB doesn't need 18GB RAM by itself, it can run with a minimal 5MB innodb_buffer_pool_size. But if you want maximum performance, then you want to put all your data in the RAM. The difference in random access latency between SSD and RAM is 100-1000 times. You really want to put as much of your frequently accessed data as possible into the RAM.

Following that, mysqltuner recommends allocating innodb_buffer_pool_size equal to your entire data set. Yes, with a 1 TB database, it is going to recommend allocating 1TB innodb_buffer_pool_size.

In practice, when your actual active working data set is smaller than the entire data set, it is overkill to allocate that much memory. You still want to allocate as much memory as possible, but check that Innodb_buffer_pool_pages_free is close to or equal to '0', so there is no memory wasted. But keep in mind that the buffer pool can be filled up by a one-time job or a backup process and MariaDB won't free the pages after that, so it is better to monitor it over time.

It is also recommended to monitor the ratio between innodb_buffer_pool_reads and innodb_buffer_pool_read_requests. It is shown as InnoDB Read buffer efficiency in mysqltuner output but you skipped it. MariaDB documentation recommends keeping it above 99% while mysqltuner warns if it drops below 90%. Keep in mind that performance degradation between 99% and 90% cache hit rate can be 5-10 times.

Overall, you need to start with monitoring and profiling your application performance first, then monitor and optimize your queries and only by having your eyes on the application performance metrics can you do meaningful server tuning.

Improve this answer
0

The simpler recommendation is: "Set innodb_buffer_pool_size to 70% of available RAM. Since you have only 8 GB of RAM and Java would like a chunk of that, I suggest that the current 3.9 GB is safe and efficient.

Bottom line: The various tuners out there are not to be trusted 100%.

If you add more RAM and increase the setting, you might get some performance improvement. But there are far too many variables to determine how much improvement there would be.

When performance is a problem, I recommend working on the queries, but that is a different story. For 1TB of data (cached or not), improving the indexes and queries is essential (with or without that much RAM.)

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.