Another run: Code: curl -sL https://gist.github.com/centminmod/01b5ec3ebd394fa9284f/raw/innodbio.sh | bash -------------------------------------------------------------------- System Info (0.1) -------------------------------------------------------------------- Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 2 On-line CPU(s) list: 0,1 Thread(s) per core: 1 Core(s) per socket: 1 Socket(s): 2 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family: 6 Model: 44 Model name: Westmere E56xx/L56xx/X56xx (Nehalem-C) Stepping: 1 CPU MHz: 2199.998 BogoMIPS: 4399.99 Hypervisor vendor: KVM Virtualization type: full L1d cache: 32K L1i cache: 32K L2 cache: 4096K NUMA node0 CPU(s): 0,1 3.10.0-327.4.4.el7.x86_64 CentOS Linux release 7.2.1511 (Core) -------------------------------------------------------------------- Filesystem Type Size Used Avail Use% Mounted on /dev/mapper/centos_sim-root xfs 50G 12G 39G 23% / devtmpfs devtmpfs 1.9G 0 1.9G 0% /dev tmpfs tmpfs 1.9G 0 1.9G 0% /dev/shm tmpfs tmpfs 1.9G 73M 1.8G 4% /run tmpfs tmpfs 1.9G 0 1.9G 0% /sys/fs/cgroup /dev/mapper/centos_sim-home xfs 96G 3.7G 92G 4% /home /dev/loop0 ext4 3.7G 20M 3.5G 1% /tmp /dev/vda1 xfs 497M 265M 233M 54% /boot tmpfs tmpfs 380M 0 380M 0% /run/user/0 tmpfs tmpfs 380M 0 380M 0% /run/user/497 -------------------------------------------------------------------- -------------------------------------------------------------------- ioping 0.9 calculated (IOPs) -------------------------------------------------------------------- innodb_io_capacity = 317 innodb_io_capacity = 297 -------------------------------------------------------------------- 426 change to 200 innodb_io_capacity = 200 458 change to 200 innodb_io_capacity = 200 -------------------------------------------------------------------- -------------------------------------------------------------------- fio-2.2.8 calculated (IOPs) -------------------------------------------------------------------- Full Reads: 3652 Full Writes: 2514 innodb_io_capacity = 83 innodb_io_capacity = 62 innodb_io_capacity = 50 innodb_io_capacity = 35 innodb_io_capacity = 502 Full Reads: 3652 Full Writes: 2578 innodb_io_capacity = 85 innodb_io_capacity = 64 innodb_io_capacity = 51 innodb_io_capacity = 36 innodb_io_capacity = 515 -------------------------------------------------------------------- innodb_read_io_threads = 2 innodb_write_io_threads = 2 -------------------------------------------------------------------- My results seem to be very slow compared to others with same RAM and # of CPU if I read the results correctly. Is there a topic where this is discussed ?
Thanks. My calculation is based on the actual disk performance of your server, so if it's low, means your disk isn't as performant as others reported here. That's why i need to perfect the formula so can dynamically tailor innodb_io_capacity based on the server's measured disk performance. working on the actual routine for centmin mod itself now. Going to use fio benchmark derived numbers to formulate the iops value for innodb_io_capacity Code: ./setio.sh set Full Reads: 3778 Full Writes: 114652 innodb_io_capacity = 4500 /etc/my.cnf adjustment existing value: innodb_io_capacity = 200 +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | innodb_io_capacity | 200 | +--------------------+-------+ new value: innodb_io_capacity = 4500 +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_io_capacity | 4500 | | innodb_io_capacity_max | 9000 | +------------------------+-------+
yes not all SSDs are created equal Remember though SATA/SAS 10k IOPs is 75-100 and 100-150 respectively and SAS 15K around 175-250 IOPs which can put SSDs in perspective for the FIO reported FULL Read/Write values
Code: -------------------------------------------------------------------- System Info (0.1) -------------------------------------------------------------------- Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 12 On-line CPU(s) list: 0-11 Thread(s) per core: 2 Core(s) per socket: 6 Socket(s): 1 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family: 6 Model: 62 Stepping: 4 CPU MHz: 3500.159 BogoMIPS: 7000.31 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 12288K NUMA node0 CPU(s): 0-11 2.6.32-573.12.1.el6.x86_64 CentOS release 6.7 (Final) -------------------------------------------------------------------- Filesystem Type Size Used Avail Use% Mounted on /dev/md2 ext4 289G 239G 35G 88% / tmpfs tmpfs 64G 0 64G 0% /dev/shm /dev/md0 ext4 488M 151M 313M 33% /boot /dev/md1 ext4 64G 157M 63G 1% /tmp tmpfs tmpfs 64G 157M 63G 1% /tmp -------------------------------------------------------------------- -------------------------------------------------------------------- ioping 0.8 calculated (IOPs) -------------------------------------------------------------------- innodb_io_capacity = 744 innodb_io_capacity = 766 -------------------------------------------------------------------- 426 change to 200 innodb_io_capacity = 200 innodb_io_capacity = 670 -------------------------------------------------------------------- -------------------------------------------------------------------- fio-2.0.13 calculated (IOPs) -------------------------------------------------------------------- Full Reads: 42772 Full Writes: 8093 innodb_io_capacity = 269 innodb_io_capacity = 202 innodb_io_capacity = 161 innodb_io_capacity = 115 innodb_io_capacity = 1011 Full Reads: 35429 Full Writes: 8363 innodb_io_capacity = 278 innodb_io_capacity = 209 innodb_io_capacity = 167 innodb_io_capacity = 119 innodb_io_capacity = 1045 -------------------------------------------------------------------- innodb_read_io_threads = 6 innodb_write_io_threads = 6 -------------------------------------------------------------------- Code: -------------------------------------------------------------------- System Info (0.1) -------------------------------------------------------------------- Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 32 On-line CPU(s) list: 0-31 Thread(s) per core: 2 Core(s) per socket: 8 Socket(s): 2 NUMA node(s): 2 Vendor ID: GenuineIntel CPU family: 6 Model: 63 Model name: Intel(R) Xeon(R) CPU E5-2640 v3 @ 2.60GHz Stepping: 2 CPU MHz: 1388.156 BogoMIPS: 5204.16 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 20480K NUMA node0 CPU(s): 0,2,4,6,8,10,12,14,16,18,20,22,24,26,28,30 NUMA node1 CPU(s): 1,3,5,7,9,11,13,15,17,19,21,23,25,27,29,31 3.10.0-327.4.5.el7.x86_64 CentOS Linux release 7.2.1511 (Core) -------------------------------------------------------------------- Filesystem Type Size Used Avail Use% Mounted on /dev/sda2 ext4 922G 3.6G 872G 1% / devtmpfs devtmpfs 95G 0 95G 0% /dev tmpfs tmpfs 95G 0 95G 0% /dev/shm tmpfs tmpfs 95G 34M 95G 1% /run tmpfs tmpfs 95G 0 95G 0% /sys/fs/cgroup /dev/sda1 ext4 477M 115M 333M 26% /boot tmpfs tmpfs 19G 0 19G 0% /run/user/0 -------------------------------------------------------------------- -------------------------------------------------------------------- ioping 0.9 calculated (IOPs) -------------------------------------------------------------------- innodb_io_capacity = 1593 innodb_io_capacity = 1541 -------------------------------------------------------------------- innodb_io_capacity = 3756 innodb_io_capacity = 3668 -------------------------------------------------------------------- -------------------------------------------------------------------- fio-2.2.8 calculated (IOPs) -------------------------------------------------------------------- Full Reads: 120470 Full Writes: 101135 innodb_io_capacity = 3371 innodb_io_capacity = 2528 innodb_io_capacity = 2022 innodb_io_capacity = 1444 innodb_io_capacity = 4045 Full Reads: 113777 Full Writes: 102400 innodb_io_capacity = 3413 innodb_io_capacity = 2560 innodb_io_capacity = 2048 innodb_io_capacity = 1462 innodb_io_capacity = 4096 -------------------------------------------------------------------- innodb_read_io_threads = 16 innodb_write_io_threads = 16 -------------------------------------------------------------------- Code: -------------------------------------------------------------------- System Info (0.1) -------------------------------------------------------------------- Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 4 On-line CPU(s) list: 0-3 Thread(s) per core: 2 Core(s) per socket: 2 Socket(s): 1 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family: 6 Model: 42 Stepping: 7 CPU MHz: 1688.976 BogoMIPS: 6784.59 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 3072K NUMA node0 CPU(s): 0-3 3.14.32-xxxx-grs-ipv6-64 CentOS release 6.7 (Final) -------------------------------------------------------------------- Filesystem Type Size Used Avail Use% Mounted on rootfs rootfs 481G 49G 408G 11% / /dev/root ext3 481G 49G 408G 11% / devtmpfs devtmpfs 3.9G 344K 3.9G 1% /dev /dev/sda3 ext4 1.4T 35G 1.3T 3% /home tmpfs tmpfs 3.9G 0 3.9G 0% /dev/shm /dev/loop0 ext4 3.7G 7.7M 3.5G 1% /tmp /dev/root ext3 481G 49G 408G 11% /var/named/chroot/etc/named /dev/root ext3 481G 49G 408G 11% /var/named/chroot/var/named /dev/root ext3 481G 49G 408G 11% /var/named/chroot/etc/named.conf /dev/root ext3 481G 49G 408G 11% /var/named/chroot/etc/named.rfc19 12.zones /dev/root ext3 481G 49G 408G 11% /var/named/chroot/etc/rndc.key /dev/root ext3 481G 49G 408G 11% /var/named/chroot/usr/lib64/bind /dev/root ext3 481G 49G 408G 11% /var/named/chroot/etc/named.iscdl v.key /dev/root ext3 481G 49G 408G 11% /var/named/chroot/etc/named.root. key -------------------------------------------------------------------- -------------------------------------------------------------------- ioping 0.8 calculated (IOPs) -------------------------------------------------------------------- innodb_io_capacity = 925 innodb_io_capacity = 1040 -------------------------------------------------------------------- innodb_io_capacity = 503 innodb_io_capacity = 1057 -------------------------------------------------------------------- -------------------------------------------------------------------- fio-2.0.13 calculated (IOPs) -------------------------------------------------------------------- Full Reads: 453 Full Writes: 477 innodb_io_capacity = 15 innodb_io_capacity = 11 innodb_io_capacity = 9 innodb_io_capacity = 6 innodb_io_capacity = 200 Full Reads: 451 Full Writes: 438 innodb_io_capacity = 14 innodb_io_capacity = 10 innodb_io_capacity = 8 innodb_io_capacity = 6 innodb_io_capacity = 200 -------------------------------------------------------------------- innodb_read_io_threads = 2 innodb_write_io_threads = 2 -------------------------------------------------------------------- Code: -------------------------------------------------------------------- System Info (0.1) -------------------------------------------------------------------- Architecture: x86_64 CPU op-mode(s): 32-bit, 64-bit Byte Order: Little Endian CPU(s): 8 On-line CPU(s) list: 0-7 Thread(s) per core: 2 Core(s) per socket: 4 Socket(s): 1 NUMA node(s): 1 Vendor ID: GenuineIntel CPU family: 6 Model: 58 Stepping: 9 CPU MHz: 1600.000 BogoMIPS: 6785.19 Virtualization: VT-x L1d cache: 32K L1i cache: 32K L2 cache: 256K L3 cache: 8192K NUMA node0 CPU(s): 0-7 2.6.32-573.12.1.el6.x86_64 CentOS release 6.7 (Final) -------------------------------------------------------------------- Filesystem Type Size Used Avail Use% Mounted on /dev/sda2 ext4 908G 34G 829G 4% / tmpfs tmpfs 7.8G 0 7.8G 0% /dev/shm /dev/sda1 ext4 772M 172M 560M 24% /boot tmpfs tmpfs 7.8G 124K 7.8G 1% /tmp -------------------------------------------------------------------- -------------------------------------------------------------------- ioping 0.8 calculated (IOPs) -------------------------------------------------------------------- innodb_io_capacity = 672 innodb_io_capacity = 766 -------------------------------------------------------------------- innodb_io_capacity = 1046 innodb_io_capacity = 1041 -------------------------------------------------------------------- -------------------------------------------------------------------- fio-2.0.13 calculated (IOPs) -------------------------------------------------------------------- Full Reads: 383 Full Writes: 290 innodb_io_capacity = 9 innodb_io_capacity = 7 innodb_io_capacity = 5 innodb_io_capacity = 4 innodb_io_capacity = 150 Full Reads: 453 Full Writes: 383 innodb_io_capacity = 12 innodb_io_capacity = 9 innodb_io_capacity = 7 innodb_io_capacity = 5 innodb_io_capacity = 200 -------------------------------------------------------------------- innodb_read_io_threads = 4 innodb_write_io_threads = 4 --------------------------------------------------------------------
Add tools/setio.sh Script Update: Feb 6th, 2016. Just added to Centmin Mod 123.09beta01 branch the new tools/setio.sh standalone tool which can set your MySQL server's /etc/my.cnf innodb_io_capacity variable optimally based on your server's measured disk write I/O performance numbers. This is based on the tests and feedback provided by other Centmin Mod users at Beta Branch - Help test innodbio.sh for MySQL Tuning | Centmin Mod Community To run setio.sh it's as easy as running this command and it will update /etc/my.cnf innodb_io_capacity variable with the new measured value and on the fly - dynamically set globally the value on the running MariaDB MySQL server. No need to restart MySQL for the setting to register SSH command after you update to latest Centmin Mod 123.09beta01 code Code: /usr/local/src/centminmod/tools/setio.sh set If on Centmin Mod 123.08stable or lower, you can just grab the tools/setio.sh file itself and download to tools directory to run it as well Code: wget -O /usr/local/src/centminmod/tools/setio.sh https://github.com/centminmod/centminmod/raw/123.09beta01/tools/setio.sh chmod +x /usr/local/src/centminmod/tools/setio.sh /usr/local/src/centminmod/tools/setio.sh set Commit add tools/setio.sh · centminmod/centminmod@0bbc9be · GitHub
Works fine ! How about the innodb_io_capacity_max variable ? Is this set automatically from innodb_io_capacity? I cannot find the max in my.cnf while the screen shows: Code: [root@dev etc]# /usr/local/src/centminmod/tools/setio.sh set Full Reads: 3655 Full Writes: 2579 innodb_io_capacity = 500 /etc/my.cnf adjustment existing value: innodb_io_capacity = 100 +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | innodb_io_capacity | 100 | +--------------------+-------+ new value: innodb_io_capacity = 500 +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_io_capacity | 500 | | innodb_io_capacity_max | 2000 | +------------------------+-------+
Just updated tools/setio.sh to specifically set innodb_io_capacity_max variable as well to 2x innodb_io_capacity derived value Code: Full Reads: 3144 Full Writes: 117928 innodb_io_capacity = 4700 /etc/my.cnf adjustment existing value: innodb_io_capacity = 4600 +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_io_capacity | 4600 | | innodb_io_capacity_max | 9000 | +------------------------+-------+ new value: innodb_io_capacity = 4700 +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_io_capacity | 4700 | | innodb_io_capacity_max | 9400 | +------------------------+-------+
Updated tools/setio.sh for io threads adjustment and prompt to restart mysql server for changes Code: Full Reads: 3533 Full Writes: 116267 innodb_io_capacity = 4600 +------------------------+-------+ /etc/my.cnf adjustment +------------------------+-------+ existing value: +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_io_capacity | 4600 | | innodb_io_capacity_max | 9200 | +------------------------+-------+ new value: +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_io_capacity | 4600 | | innodb_io_capacity_max | 9200 | +------------------------+-------+ +------------------------+-------+ innodb io threads adjustment +------------------------+-------+ existing value: +------------------------+-------+ innodb_read_io_threads = 2 innodb_write_io_threads = 2 +------------------------+-------+ new value: +------------------------+-------+ innodb_read_io_threads = 2 innodb_write_io_threads = 2 +------------------------+-------+ Restart MySQL server for io thread changes
no need to restart for innodb_io_capacity changes but need to restart for innodb io thread changes heh. I leave it up to end users when they want to restart mysql for io thread changes.
new tools/setio.sh run on new 2GB Linode KVM VPS Fremont for my Sendy.co newsletter hosted server. It's using older Xeon E5-2680v2 processor Code: tools/setio.sh set Full Reads: 28151 Full Writes: 23339 innodb_io_capacity = 2300 +------------------------+-------+ /etc/my.cnf adjustment +------------------------+-------+ existing value: +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_io_capacity | 2000 | | innodb_io_capacity_max | 4000 | +------------------------+-------+ new value: +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | innodb_io_capacity | 2300 | | innodb_io_capacity_max | 4600 | +------------------------+-------+ +------------------------+-------+ innodb io threads adjustment +------------------------+-------+ existing value: +------------------------+-------+ innodb_read_io_threads = 2 innodb_write_io_threads = 2 +------------------------+-------+ new value: +------------------------+-------+ innodb_read_io_threads = 2 innodb_write_io_threads = 2 +------------------------+-------+ Restart MySQL server for io thread changes
huge variance in disk i/o over time, same 2GB Linode from previous post might need to revise and reduce my formula as difference could be between a fresh light or busy server Code: tools/setio.sh set Full Reads: 29467 Full Writes: 68266 innodb_io_capacity = 3400 +------------------------+-------+ /etc/my.cnf adjustment +------------------------+-------+ existing value: Variable_name Value innodb_io_capacity 2300 innodb_io_capacity_max 4600 new value: Variable_name Value innodb_io_capacity 3400 innodb_io_capacity_max 6800 +------------------------+-------+ innodb io threads adjustment +------------------------+-------+ existing value: +------------------------+-------+ innodb_read_io_threads = 2 innodb_write_io_threads = 2 +------------------------+-------+ new value: +------------------------+-------+ innodb_read_io_threads = 2 innodb_write_io_threads = 2 +------------------------+-------+ Restart MySQL server for io thread changes revised down the formula for io capacity Code: tools/setio.sh set Full Reads: 68266 Full Writes: 65015 innodb_io_capacity = 2700 +------------------------+-------+ /etc/my.cnf adjustment +------------------------+-------+ existing value: Variable_name Value innodb_io_capacity 3400 innodb_io_capacity_max 6800 new value: Variable_name Value innodb_io_capacity 2700 innodb_io_capacity_max 5400 +------------------------+-------+ innodb io threads adjustment +------------------------+-------+ existing value: +------------------------+-------+ innodb_read_io_threads = 2 innodb_write_io_threads = 2 +------------------------+-------+ new value: +------------------------+-------+ innodb_read_io_threads = 2 innodb_write_io_threads = 2 +------------------------+-------+ Restart MySQL server for io thread changes
Digital Ocean, 4GB This is the last one. In past few days I run this tool several times, and mostly every time it gives new recommandations (I think it is because @eva2000 tuneup script code) Code: [root@tvor-ocean ~]# /usr/local/src/centminmod/tools/setio.sh set Full Reads: 53194 Full Writes: 7772 innodb_io_capacity = 900 +------------------------+-------+ /etc/my.cnf adjustment +------------------------+-------+ existing value: Variable_name Value innodb_io_capacity 800 innodb_io_capacity_max 1600 new value: Variable_name Value innodb_io_capacity 900 innodb_io_capacity_max 1800 +------------------------+-------+ innodb io threads adjustment +------------------------+-------+ existing value: +------------------------+-------+ innodb_read_io_threads = 2 innodb_write_io_threads = 2 +------------------------+-------+ new value: +------------------------+-------+ innodb_read_io_threads = 2 innodb_write_io_threads = 2 +------------------------+-------+ Restart MySQL server for io thread changes
disk i/o will change over time or periods due to measured disk performance at the moment in time you run it so it's normal
updated 123.09beta01's tools/setio.sh tool with a fix thanks to @Matt feedback. setio.sh measured disk i/o performance from /root/tools/fio directory, which meant you measured /root directory disk I/O. However, mysql by default runs from /var/lib/mysql and maybe mounted on separate disks or disk arrays from root partition. So setio.sh could possibly be measuring the wrong disk partition to gauge disk i/o performance. The update fixes this by finding out mysql's defined datadir path and setting up fio tool to test in a directory at /var/lib/mysql/cmsetiofiotest or where ever your mysql datadir path is i.e. if /mnt/mysql is datadir, then /mnt/mysql/cmsetiofiotest will be where fio tests. It would only change the disk iops numbers if your mysql datadir is on a separate disk or partition from root. Code: ls -lahrt /var/lib/mysql | tail -1 drwxr-xr-x 2 root root 80 Feb 11 05:56 cmsetiofiotest Code: ls -lah /var/lib/mysql/cmsetiofiotest/ total 20K drwxr-xr-x 2 root root 80 Feb 11 05:56 . drwxr-xr-x 5 mysql mysql 4.0K Feb 11 05:56 .. -rw-r--r-- 1 root root 184 Feb 11 05:56 reads-16k.ini -rw-r--r-- 1 root root 182 Feb 11 05:56 reads.ini -rw-r--r-- 1 root root 186 Feb 11 05:56 writes-16k.ini -rw-r--r-- 1 root root 184 Feb 11 05:56 writes.ini the directory will be ignored by mysql and NOT mistaken for database name when you restart mysql server due to an added entry in your /etc/my.cnf under [mysqld] group Code: [mysqld] ignore-db-dir=cmsetiofiotest Code: bash tools/setio.sh set Full Reads: 12700 Full Writes: 9764 innodb_io_capacity = 1200 +------------------------+-------+ /etc/my.cnf adjustment +------------------------+-------+ existing value: Variable_name Value innodb_io_capacity 100 innodb_io_capacity_max 2000 new value: Variable_name Value innodb_io_capacity 1200 innodb_io_capacity_max 2400 +------------------------+-------+ innodb io threads adjustment +------------------------+-------+ existing value: +------------------------+-------+ innodb_read_io_threads = 2 innodb_write_io_threads = 2 +------------------------+-------+ new value: +------------------------+-------+ innodb_read_io_threads = 2 innodb_write_io_threads = 2 +------------------------+-------+ Restart MySQL server for io thread changes
working nicely Code: tools/setio.sh set Full Reads: 3990 Full Writes: 118724 innodb_io_capacity = 3200 +------------------------+-------+ /etc/my.cnf adjustment +------------------------+-------+ existing value: Variable_name Value innodb_io_capacity 3200 innodb_io_capacity_max 6400 new value: Variable_name Value innodb_io_capacity 3200 innodb_io_capacity_max 6400 +------------------------+-------+ innodb io threads adjustment +------------------------+-------+ existing value: +------------------------+-------+ innodb_read_io_threads = 2 innodb_write_io_threads = 2 +------------------------+-------+ new value: +------------------------+-------+ innodb_read_io_threads = 2 innodb_write_io_threads = 2 +------------------------+-------+ Restart MySQL server for io thread changes