It’s difficult to say yes or no. The CPU load depends on several things and what is “6”? (6%, 60% – of all the cores?). Anyway the CPU Load could be related to intensive queries multiplied per connections, requests, (lack of) assigned memory, etc.
Mariadb ( I checked a non recent version 10.1.29) seems to be a bit faster than mysql 5.6 but it’s a personal opinion.

I’ve read a comment that suggests that mysql 8 is faster:
“MySQL 8.0 implements a completely new data dictionary … while MariaDB has only a tiny fraction … The data dictionary guarantees higher performance and better data consistency and it is fairly well integrated with the rest of the server”
Reference: https://www.percona.com/blog/2017/11/02/mysql-vs-mariadb-reality-check/

Pay attention to upgrade correctly (It’s very easy to do it) if you are goint to move from mysql to mariadb, otherwise you are going to have spikes in cpu load and instable connections with the mariadb server. I just fixed a server with such problems.

The same goes for innodb_buffer_pool_instances. How many? I’m actually using adding 1 for each GB of innodb_buffer_pool_size.

After various tests I have a general performance degradation with 102G innodb_buffer_pool_size and any size for innodb_log_file_size (ib_logfile0 and ib_logfile1 deleted before restarting). Thank you

We currently have a server with 32 gb Ram, 4 CPU. The CPU load on is always above 6.

Can someone help with this ?

Thanks

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’) A;
+——-+
| RIBPS |
+——-+
| 8 |
+——-+
1 row in set (4.31 sec)

and set my.cnf setting like this

[mysqld]
innodb_buffer_pool_size=8G

Great Article!

Will having a huge amount of memory (such as 80 GB) allocated for the InnoDB Buffer Pool Size result in a performance problem when the cache gets invalidated? Or will any updates to a table happen directly in the buffer and then are written to the disk (instead of invalidating the cache and then recaching the table).

Thanks in advance as I can’t find any documentation on how the buffer works.

1) Larger instances are still going to take up 10+% more than the set buffer pool, largely due to internal hash tables, particularly the adaptive hash index. The adaptive hash index for some workloads can be very beneficial, but can be crippled if the buffer pool is bumped up too high.

2) workloads with higher volume are obviously going to have more connection overhead, particularly if there is a temporary build-up for whatever reason. This combined with per-connection buffer allocation, temporary tables, etc.. can chew up a lot of memory quickly.

3) your developers may decide to use the memory engine for some new application with or without your knowledge 🙁

3a) I think it’s fair to say in a memory poor situation, a mysql instance with an over-aggressive buffer pool starving the server of memory and in danger of getting killed is even worse than a system with a smaller than needed buffer pool.

3b) In normal operations, let’s say your buffer pool OK, increasing may help if your working data set is not fully in memory, however for many workloads (time dependent, action at the end of the tables), the working set is already in memory and will make no difference. The benefits may only be seen down the road as the load/working set grows.

3c) assuming this growth, your working set can sometimes be difficult to determine and track, especially with new app features your company may regularly put out, and you may have found (over the period of a couple of days) the working data set no longer fits into memory. It’s saves a lot of pain to have extra memory space available to resize immediately and be fully operational with an LRU dump/restore in 15 minutes and worry about a RAM upgrade later in a controlled situation, rather than request an emergency RAM upgrade, best case scenario a 6 hour turn around and a complete stop of at least 30 minutes, or worst case, days to find the RAM needed.

nice to see 5.7 is trying to address dynamic resizing.

Based on the workload and way we allocate systems, I found that what we actually wanted was some reserved memory for the OS, monitoring tools, and some base memory for mysqld. After that, we could allocate a percent of the remaining ram to bufferpool

We have several classes of DB nodes. 32G, 64G, and 192G.

For the most part, we allocate (N – 7G) * .9. So for a 64G node, we end up with ~51G of memory allocated to the bufferpool.

Some higher load databases we bump that system reserved number to 10G, since they tend to handle a lot more connections, and have higher need for query processing memory.

128GB RAM: innodb_buffer_pool_size = 104GB
64GB RAM: innodb_buffer_pool_size = 56G
32GB RAM: innodb_buffer_pool_size = 28G

This setup has been working flawlessly for us in the past 3 years.