1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

More RAM to speed up mysql writes?

Discussion in 'Server Configuration and Hosting' started by Kintaro, Oct 15, 2016.

  1. Kintaro

    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?
  2. Pierce

    Pierce Well-Known Member

    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.
  3. Kintaro

    Kintaro Well-Known Member

    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?
  4. Sheratan

    Sheratan Well-Known Member

    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?
  5. Sim

    Sim Well-Known Member

    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.
    Kintaro likes this.
  6. eva2000

    eva2000 Well-Known Member

    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: Oct 16, 2016
    Pierce, Alfa1 and Sim like this.
  7. MattW

    MattW Well-Known Member

    eva2000 likes this.
  8. Kintaro

    Kintaro Well-Known Member

    free -m 2016-10-16.jpg

    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.
    Pierce likes this.
  9. Kintaro

    Kintaro Well-Known Member

    Now I set some mysql options as suggested from mysqltuner:

    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: Oct 16, 2016
  10. Sim

    Sim Well-Known Member

    Yes, unfortunately I think you are correct. Otherwise it would be a relatively easy fix.
  11. Kintaro

    Kintaro Well-Known Member

    How about VPS-SSD 3?

    @MattW @eva2000 Do you think that can have better IO performance? Have you ever benchmarked them too? any other (european friendly) suggestion?
  12. eva2000

    eva2000 Well-Known Member

    on OVH all plans have same capped ceph HA disk i/o AFAIK
    Kintaro likes this.
  13. Sim

    Sim Well-Known Member

    Linode have datacenters in London and Frankfurt
  14. Kintaro

    Kintaro Well-Known Member

    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.jpg
    htop Oct 19 2016.jpg

    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.

Share This Page