Limit CPU usage in MariaDB

I use an SEO plugin called Yoast. I’m not exactly enamored with it, but I’m also not sure I can switch to something better. One reason I’m not enamored with it is its high CPU usage. I had to fix it from the MariaDB side. Here’s how I figured out how to limit CPU usage in MariaDB.

mysqld using 100% of CPU during internal linking analysis

LImit CPU usage in MariaDB
The thread_pool_size limits MariaDB to 3 CPU cores. The query cache size settings came from MySQLTune’s suggestions. I’ve had problems in the past with MySQL and MariaDB using all my RAM, but on systems with 8 GB or more, I haven’t had memory issues.

One of the main reasons to get Yoast is its internal linking analysis. As you write or edit content, it analyzes your other content looking for similarities. Then it presents you a list, suggesting you link to them.

This is great for your SEO. It’s better when someone else links your stuff of course, but you get about half the SEO benefit from linking to your own stuff as you do from someone else linking. With Bing I think it’s even more. Plus it makes your site much stickier, because people click on those links if they’re into what they’re reading. It’s a huge boon. It wasn’t the easiest thing for me to get working, but once I did, it made a difference. I saw an increase in traffic because I drew more readers and they stayed longer. It was great. Especially in my case. I’ve been blogging more than 20 years, so I have piles and piles of legacy content. A lot of it has limited value at best, but several times a year, Yoast unearths something with potential in that pile of forgotten content. I can link to that content, revisit it and give it a bit of rework for the current era, and I end up with something that’s viable today.

How Yoast 15 ruined it

The problem is, after Yoast version 15 came out, my CPU usage would spike to 100% while Yoast did that analysis. That left nothing for my webserver, so the whole site slowed to a crawl or even became unresponsive. There’s no point in having a website if it can’t serve the readers.

At first I wasn’t sure what was causing it, but when I disabled the link suggestions, the problem went away. And I could easily make the problem come back by editing a large post.

I even upgraded my hardware (arguably I was due anyway), and I hoped that in combination with upgrading to a new Linux distribution with a modern PHP would fix it. Everything else about the system ran like lightning after that. But Yoast just soaked up all the power and asked for more.

Limiting mysqld to three CPU cores

I found a setting that seems to limit mysqld to three CPU cores. It doesn’t provide very fine control. Sometimes it uses two cores and sometimes it will spill over and use some of my fourth. But it does seem to do an adequate job of keeping Yoast and MariaDB from chewing up all the available CPU time.

On my Debian system, the MariaDB config is in /etc/mysql/mariadb.conf.d/50-server.cnf. In the section labeled Fine Tuning, I added a single line:


thread_pool_size         = 3

The default setting for thread pool size is your number of CPU cores. Since I have four cores, I chose 3 for this setting. If I had a CPU with hyperthreading, I might be able to push this higher, say, to 7, and leave just one hyperthreaded core in reserve for my webserver under heavy load. But I’m not sure, since I don’t have a CPU with hyperthreading in this webserver. And if I did, I’d be inclined to try a setting of 5 before I tried 7.

While I’m not sure this setting is optimal, it does make the system usable, which it wasn’t before. I have a day job, so I write on Saturdays and Sundays. Those are also the days my traffic is heaviest. This setting lets my traffic and my writing stay out of each other’s way, which is important.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: