Recommended settings Maria DB 10 + 8GB RAM

[DroidOne]

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

 

[Xon]

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 )

 

[DroidOne]

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

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

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

The web server is running nginx+php5-fpm.

Thanks!

 

[Xon]

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

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.

The web server is running nginx+php5-fpm.

Thanks!

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)

 

[DroidOne]

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.

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

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)

OK. I'll try with 100.