More RAM to speed up mysql writes?

Kintaro

Well-known member
I run 10 websites on a OVH Cloud VPS 2 (https://www.ovh.com/us/vps/vps-cloud.xml)
xenforo is the website more active.

I think that I have some problem with my db (mariadb).
I can see it slow down on writes.

HTOP say that I have often VDA busy 100%.

Do you think that double up the RAM with OVH Cloud VPS 3 (4GB=>8GB) and change MariaDB configuration accordingly can give me a speed boost preventing this kind of slowdowns lowering the disk writes rate?
 
Ultimately, writes go to memory then to disk.

That is assuming that your page cache is not being used, in which case more memory would help. You can see it in the top command of course.

10 websites.. that's a really low cost at $17 a month.
 
Many those websites are really low traffic (like max 100/200 pageviews/day).

The only one with "real traffic" (but not that heavy) is a xenforo installation with 10k pageviews/day.

Top say this:
KiB Mem: 3962564 total, 3602616 used, 359948 free, 211280 buffers
KiB Swap: 1023996 total, 16972 used, 1007024 free. 2185160 cached Mem

Maybe I need a more serious server instead of adding memory?
 
using ssd will speed up your mysql 3-5x you only need big ram for mysql buffer, tmp, and for how many mysql connection you serve.

what is your free -m said?
 
InnoDB works best when you can give it at least as much RAM as you have data in the database - essentially it caches everything in RAM.

If your tables are using InnoDB, and you have, say, 2.6GB of data in your InnoDB tables, then assigning something like 3GB RAM to InnoDB caching will really improve performance.

That is of course assuming you have sufficient RAM available on the machine - if you allocate too much, you'll cause instability on the machine - I accidentally allocated too much to the InnoDB cache last week and the Linux OOM (Out Of Memory) manager started killing the MySQL process occasionally to manage it. I dropped the allocation back a bit (and moved one of my largest databases to its own server) and it's been stable ever since.

Have you been running http://mysqltuner.com/ ?

It's worth going back to default settings, letting it run for 24 hours and then running mysqltuner, changing the settings it suggests, restart the server and let it run for another 24 hours or so before repeating the process until you either run out of RAM to allocate, or it has no more suggestions.

Also, don't over-allocate your key buffer or join buffer - these are per-connection settings and so you'll end up using a heap of RAM for not much performance benefit if you allocate too much, especially if you have a lot of connections allocated. If connection usage is fairly constant like mine is, I generally try and allocate sufficient connections so that the max connections used is never more than 80% of what I allocated. Too much more and you're just wasting RAM that is allocated for those unused connections.

Most importantly - check out how much IO activity there is on your swap drive. Having swap allocated isn't a problem - it's how much it gets used which will really slow your machine down. If your machine is constantly swapping, it probably means you've over-allocated memory (or just need more of it), and your disk IO is suffering because the machine is spending too much time doing IO to the swap drive. This is especially bad if you aren't using SSDs.

I have a separate database server serving 25 websites running on an 8GB Linode (4 CPU cores, SSD drives), it has 4.6GB of data in InnoDB tables, so I've got 5GB allocated to the InnoDB buffer pool.

Most of the sites are low traffic, but I have a couple of XenForo sites getting 20K page views per day and one XenForo site getting 40K page views per day - all being served from the same database server.
 
I run 10 websites on a OVH Cloud VPS 2 (https://www.ovh.com/us/vps/vps-cloud.xml)
xenforo is the website more active.

I think that I have some problem with my db (mariadb).
I can see it slow down on writes.

HTOP say that I have often VDA busy 100%.

Do you think that double up the RAM with OVH Cloud VPS 3 (4GB=>8GB) and change MariaDB configuration accordingly can give me a speed boost preventing this kind of slowdowns lowering the disk writes rate?
OVH Public VPS Cloud has very limited disk I/O performance

benchmarks I posted at https://community.centminmod.com/posts/23292/ and https://community.centminmod.com/posts/23312/

@MattW also reported disk I/O performance issues too https://community.centminmod.com/posts/30198/ and no wonder if they're capped at 2000 IOPs

while you're using different VPS plan at OVH, it's using same Cepth HA storage as OVH Public cloud VPS i benchmarked so probably same disk I/O caps.
 
Last edited:
using ssd will speed up your mysql 3-5x you only need big ram for mysql buffer, tmp, and for how many mysql connection you serve.

what is your free -m said?
free -m 2016-10-16.webp

I added a swap file some days ago because there wasn't a swap partition, and as you can see there's only a little use of the it.

@Sim thank you for your post but I think that the problem is what @eva2000 and @MattW posted, limited disk I/O performance.
 
Now I set some mysql options as suggested from mysqltuner:

skip-name-resolve=1
join_buffer_size = 512k (suggested more than 256k)
innodb_buffer_pool_size = 1GB (suggested 1GB or more)
innodb_buffer_pool_instances = 1
query_cache_size = 32MB (suggested more than 16MB)

but as the IO are the problem I don't expect better performance.
 
Last edited:
uhmmmmm stop the press!!!
I adjusted again my.cnf with suggestion from mysqltuner and now it is fast and stable... I noticed that the cache (in htop) is much lower now and it used some swap.

free -m Oct 19 2016.webp
htop Oct 19 2016.webp

All of this with more users connected than last days... Maybe I'm still on the edge but now I have a little more time to choose the new server.
 
Top Bottom