MariaDB filling all my CPU cores up to 100%

K

kalleboy

Greetings.

Having mariadb105-server-10.5.13 running on FreeBSD 13 with ZFS. (A physical, dedicated server: CPU: AMD Ryzen 5 3600 6-Core 3593.32-MHz CPU and 64 GB RAM)

My WordPress website sometimes getting random-visitors attacks/DDoS (hundreds of http GET requests instantly towards randomly,
but valid URLs/posts on my website) and MariaDB fills all my CPU cores up to 100% during so.

My server has 64 GB of RAM. And whole sql dump of my website is around 220 MBs.
So it seems my DB is not really fully loaded into RAM and it forces CPU too much on such attacks (reads)?

Any idea on how to make sure MariaDB loads all my tables into RAM and it uses RAM instead of disk? (Got fast NVMe disks anyway)

In such moments, I get the IP from nginx logs and then blacklist the IP with PF, then the server gets "relaxed" back again.
But I'd love to learn the actual "solution" to this case.

Thanks in advance.
 
Install plugin "Query Monitor" and try to add indexes where needed. Also plugin "Index WP MySQL For Speed" maybe can help you.
 
kalleboy said:
My server has 64 GB of RAM. And whole sql dump of my website is around 220 MBs.
Click to expand...
Mysql dump is a text file, you can't take that as the size of the data.

I suggest you install and run databases/mysqltuner and check how big your databases really are and tweak your buffers accordingly. A 220 MB text file really can't be that much data so your whole database should easily fit in memory.
 
well if the cpu is 100% then it is probably not waiting for the disks
you may investigate sql slow log (i assume mariadb has one) and see what queries take a long time
 
If you are under a sustained DDOS, even if you make MariaDB go faster, won't you just hit another resource constraint e.g. web server program, network, etc?

Can you stop the attacks?
 
richardtoohey2 said:
Can you stop the attacks?
Click to expand...
Usually not without the cooperation of your hosting provider.

Your connection is like a funnel and someone is pouring way too much in. You could plug the bottom of the funnel (that's your end of the connection) but that's not going to stop the funnel from overflowing.

Your MySQL getting hammered is likely a side effect of lots of login attempts (those would all trigger a user lookup in the database). As you probably already have your entire database loaded in memory, it can do this fairly quickly. But there's a limit here, it also takes some CPU power to process those PHP pages, do the queries, have MySQL lookup the data, etc. Even if everything is cached in memory. A single query may not involve a lot of CPU but hundreds, maybe even thousands of login attempts concurrently are going to stack up.
 
DutchDaemon said:
https://mariadb.com/kb/en/memory-storage-engine/ maybe?
Click to expand...
I would not generally want to use the MEMORY storage engine for a Wordpress database since whatever is stored in such tables are not persisted to disk and therefore lost whenever you shut down the database :)
covacat said:
well if the cpu is 100% then it is probably not waiting for the disks
you may investigate sql slow log (i assume mariadb has one) and see what queries take a long time
Click to expand...
Yes, MariaDB has the slow_query_log and long_query_time system variables. If you clear the slow log file and then set the latter variable to 0 for a while, it will log all queries regardless of how long they take. Then set it back to whatever it was before (assuming it was a sensible number). And then install Percona's pt-query-digest and use that to analyse your slow query log file to find the top queries affecting your site. What can you do with this information? Improve your indexes. (Although if you add new indexes or remove/replace old ones, that might be confusing to a future Wordpress upgrade, e.g. can lead to duplicate indexes etc...)

(While some queries might not take as long for each individual execution, they may be more frequently run than other, slower queries. And therefore have a larger impact on your site's performance than the slower queries. This is why pt-query-digest is useful.)

Another MariaDB system variable to look at is max_connections. If it's too high, then a lot of simultaneous connections can eat up your memory. If it's too low, then legitimate visitors might see errors when visiting the site.

Anyway, as has been pointed out by others, the best solution to DDoS attacks is probably found elsewhere. That said, it's never a bad thing to optimise your database system! (As long as you know what you're doing :) )
 
Thank you all, for useful replies.

dbdemon said:
I would not generally want to use the MEMORY storage engine for a Wordpress database since whatever is stored in such tables are not persisted to disk and therefore lost whenever you shut down the database :)
Click to expand...

This was very important, my bad, that I missed RAM is NOT "storage" at all. :/ Well, yeah, true fact on WordPress.. Then I skipped this option right now.

I checked my server with mysqltuner as suggested above by SirDice here're the (important lines) results;

(And yes, it was my 2nd bad that I was assuming the filesize of sql dump file (uncompressed) is actually the size of my DB, but no;
Code: 
[OK] InnoDB File per table is activated
[OK] InnoDB buffer pool / data size: 5.0G/547.6M
[OK] Ratio InnoDB log file size / InnoDB Buffer pool size: 1.0G * 1/5.0G should be equal to 25%
[--] Number of InnoDB Buffer Pool Chunk : 40 for 1 Buffer Pool Instance(s)
[OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & Innodb_buffer_pool_instances
[OK] InnoDB Read buffer efficiency: 100.00% (48995845189 hits/ 48995875595 total)
[!!] InnoDB Write Log efficiency: 113.08% (211995 hits/ 187470 total)
[OK] InnoDB log waits: 0.00% (0 waits / 399465 writes)
[--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE
[--] Data in InnoDB tables: 547.6M (Tables: 141)
[--] Data in MyISAM tables: 191.8M (Tables: 16)
[OK] Total fragmented tables: 0
[OK] Maximum reached memory usage: 17.9G (28.02% of installed RAM)
[!!] Maximum possible memory usage: 71.0G (111.17% of installed RAM)
[!!] Overall possible memory usage with other process exceeded memory
[!!] Joins performed without indexes: 23465
[!!] Temporary tables created on disk: 80% (970K on disk / 1M total)
[!!] Key buffer used: 18.2% (23.3M used / 128.0M cache)
[!!] InnoDB Write Log efficiency: 113.08% (211995 hits/ 187470 total)

    Reduce your overall MySQL memory footprint for system stability
    Dedicate this server to your database for highest performance.
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 3.0M, or always use indexes with JOINs)


and my server.cnf file;
Code: 
max_allowed_packet=128M
join_buffer_size=3M
innodb_log_file_size=1G
innodb_buffer_pool_size=5G
tmp_table_size=512M
max_heap_table_size=512M
skip-networking
skip-name-resolve
performance_schema=ON
max_connections=500
innodb_io_capacity=15000
innodb_doublewrite = 0
innodb_flush_method = O_DSYNC
innodb_log_write_ahead_size=16384
innodb_flush_neighbors=0
#innodb_use_atomic_writes=0
innodb_read_io_threads = 10
innodb_write_io_threads = 10
innodb_fast_shutdown = 0
Any suggestions on these?

Many thanks!
 
kalleboy said:
[OK] InnoDB buffer pool / data size: 5.0G/547.6M
Click to expand...
I would suggest decreasing your innodb_buffer_pool_size, 1GB is probably more than enough. There is such a thing as too much cache/buffers, then it's going to spend more time managing the cache/buffers than actually using it. If you do change innodb_buffer_pool_size keep in mind that innodb_log_file_size needs to be adjusted too.

kalleboy said:
[!!] Maximum possible memory usage: 71.0G (111.17% of installed RAM)
Click to expand...
This isn't good either. Worst case scenario MySQL/MariaDB is going to use up all the RAM and grind the machine to a halt. Lowering the buffer pool size should help here too.
 
Or maybe you changed a default where the change was not needed ?
I may sound like Mr Obvious, but go over each line in server.cnf and ask why you changed the value and why you have put a certain value and if that is a correct one to put.
 
Alain De Vos said:
Or maybe you changed a default where the change was not needed ?
I may sound like Mr Obvious, but go over each line in server.cnf and ask why you changed the value and why you have put a certain value and if that is a correct one to put.
Click to expand...
Well, I actually modified/added them by reading many tutorials and e-books related to SQL server performance on -specifically- ZFS systems.. And I can say those lines helped (if not all of them) to overall performance.
 
You must log in or register to reply here.
Back
Top