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

Recommended settings Maria DB 10 + 8GB RAM

Discussion in 'Server Configuration and Hosting' started by DroidOne, Sep 10, 2014.

  1. DroidOne

    DroidOne Well-Known Member

    Recently bought a Xenforo license to migrate our vBulletin 3.8 forum currently running MySQL 5.5.

    Forum stats:
    ~1,5 millions posts
    DB size ~2GB (After importing everything to Xenforo)
    Usually ~600-700 users online during "busy hours"

    VPS
    OS: Ubuntu 14.04 + MariaDB 10 (deb http://mirror.stshosting.co.uk/mariadb/repo/10.0/ubuntu trusty main)
    DB engine: InnoDB
    CPU: 12 cores
    RAM: 8GB (can be increased if need be)

    I would very much like to keep the config as simple as possible (99,9% efficiency not required). I see some guys tuning and tweaking close to a hundred different variables and that is much too advanced for me :)

    Will this be enough as a bare minimum configuration?

    my.cnf
    # InnoDB settings
    innodb_buffer_pool_size=6G
    innodb_log_file_size=512M

    # Other
    max-connections=500
     
  2. Xon

    Xon Well-Known Member

    Is this traditionally spinning rust or SSD? Tuning advice varies depending on the two.

    Also; max-connections=500 implies you are using Apache with DSO.

    For SSDs the following is quite useful:

    innodb_flush_method=O_DIRECT
    innodb_read_io_threads=64
    innodb_write_io_threads=64
    innodb_io_capacity=2000
    #innodb_flush_neighbor_pages = none # MariaDB 5.5
    innodb_flush_neighbors=0

    Which is to instruct MySQL it can use much more disk-io and rough target disk IO capacity. You may need to adjust the sysctl tunable; "fs.aio-max-nr" to something higher than the default. ( append fs.aio-max-nr = 1048576 to /etc/sysctl.conf, and run sysctl -w fs.aio-max-nr = 1048576 )
     
    RoldanLT likes this.
  3. DroidOne

    DroidOne Well-Known Member

    dd if=/dev/zero of=test bs=64k count=16k conv=fdatasync
    (1073741824 bytes (1.1 GB) copied, 7.06295 s, 152 MB/s)

    I think it's a RAID10 array spinning old rust o_O

    The web server is running nginx+php5-fpm.

    Thanks!
     
  4. Xon

    Xon Well-Known Member

    You want to measure IOPs and likely in 4k-16k blocks, not raw throughput. This reddit has some information about the best way to test it.

    In that case, ignore my tuning advice. It is very bad idea to change the innodb_io_capacity and innodb_flush_neighbors flags much for traditional disks.

    In that case you don't need to have that many maximum connections. My forum with a peak ~400-550 logged-in users only hits about 8-14 or so DB connections and maintains good page response times. And with a far less impressive server for the database (using a 2gb DO node)
     
    Last edited: Sep 12, 2014
  5. DroidOne

    DroidOne Well-Known Member

    Code:
    4.0 KiB from . (ext4 /dev/xvda1): request=8 time=391 us
    4.0 KiB from . (ext4 /dev/xvda1): request=9 time=274 us
    4.0 KiB from . (ext4 /dev/xvda1): request=10 time=328 us
    4.0 KiB from . (ext4 /dev/xvda1): request=11 time=264 us
    ^C
    --- . (ext4 /dev/xvda1) ioping statistics ---
    11 requests completed in 10.4 s, 3.5 k iops, 13.8 MiB/s
    min/avg/max/mdev = 208 us / 282 us / 391 us / 44 us
    I tried ioping and got this. Guess they're not using SSD:s for storage...

    OK. I'll try with 100.
     

Share This Page