Our primary authentication database that is used for 100k+ users, and growing is a VM on a shared machine that we want to extract into its own isolated hardware. This will let us isolate resources, and help bring clarity to optimizations. However, I'm not clear on what would be an ideal machine for what we are doing, or how to determine that. So I turn to you for some advice.
- We are using MariaDB 10.1.26 (formerly mysql) on a Debian Stretch 64-bit VM with 10GB of memory, and 6 cores allocated to it. The CPU is mostly idle.
- There are approximately 320 tables across three databases, no table is very large, each under 100mb.
- Size of data on disk is 24GB, with ibdata1 being 2.2GB. Data in InnoDB tables: 1019.8M (Tables: 37).
- Reads / Writes: 98% / 2%
- Total buffers: 1.9G global + 2.9M per thread (2500 max threads)
- Maximum reached memory usage: 4.6G (47.52% of installed RAM)
This machine is replicated to a secondary (and then from the secondary to a tertiary), so relay logs take up a bunch of that space. This server is used for account authentication, it typically is doing SELECTs, and Reads on InnoDB rows so it has very little data, but it needs to be functioning and fast. On average, there are 230 connected threads, but there are periods where spikes of connections cause us to reach max_connections, and we have been increasing that as we go along, and have been tuning various parameters, but we are maxed out on the available memory we can allocate. There are also situations where some queries are taking longer than they should, this may be a network issue, or when we are reaching max connections, there is nothing that shows up in the slow query log, as they are typical queries that are usually quite fast, but sometimes take longer than they should.
- We have zero slow queries (out of 28M queries)
- extremely few aborted connections
- no sorts requiring temporary tables
- and no joins without indexes.
We do have temporary tables created on disk: 42% (1K on disk / 3K total), which is a bit high, and I'm unsure why that is happening.
- Our thread cache hit rate: 99% (735 created / 913K connections)
- table cache hit rate: 97% (253 open / 259 opened).
- open file limit used: 0% (50/16K)
- 99% of our table locks are acquired immediately (out of 29M).
We are using InnoDB for all tables. Some InnoDB stats:
- InnoDB Thread Concurrency: 0
- InnoDB File per table is activated
- InnoDB buffer pool / data size: 1.2G/1019.8M
- InnoDB buffer pool instances: 1
- InnoDB Read buffer efficiency: 99.98% (240354196 hits/ 240398682 total)
- InnoDB Write Log efficiency: 75.01% (302451 hits/ 403237 total)
- InnoDB log waits: 0.00% (0 waits / 100786 writes)
I've got munin graphs for the last year of the following, which I'm happy to provide on request. There are too many of them to individually provide: binary/relay log usage; different commands/second; threads and connection counts; table cache, open files, open tables; handler activity (write, update, delete, etc); InnoDB Buffer Pool size, pages and modified pages; InnoDB Buffer Pool activity (pages read, created, and written); InnoDB Checkpoint Age; InnoDB History List length; Number and size InnoDB Insert Buffers; InnoDB IO (file reads/writes, log writes and file syncs); InnoDB IO Pending; InnoDB Log buffer size, KB flushed and Written; InnoDB Row Operations; InnoDB Semaphores; InnoDB Transactions. I also have types of sorts; number of table locks; temporary disk tables; and number of threads.
- tps = 3.342
- qps = 270.123
based on the following:
use information_schema;
select VARIABLE_VALUE into @num_queries from GLOBAL_STATUS where VARIABLE_NAME = 'QUESTIONS';
select VARIABLE_VALUE into @uptime from GLOBAL_STATUS where VARIABLE_NAME = 'UPTIME';
select VARIABLE_VALUE into @num_com from GLOBAL_STATUS where VARIABLE_NAME = 'COM_COMMIT';
select VARIABLE_VALUE into @num_roll from GLOBAL_STATUS where VARIABLE_NAME = 'COM_ROLLBACK';
select (@num_com + @num_roll) / @uptime as tps, @num_queries / @uptime as qps;
- SHOW VARIABLES
- SHOW GLOBAL VARIABLES
- SHOW GLOBAL STATUS
- iostat -x output
- ulimit -a:
core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 39926 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 39926 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited - df -h:
Filesystem Size Used Avail Use% Mounted on udev 4.9G 0 4.9G 0% /dev tmpfs 1001M 452K 1000M 1% /run /dev/vda1 45G 28G 15G 65% / tmpfs 4.9G 0 4.9G 0% /dev/shm tmpfs 5.0M 0 5.0M 0% /run/lock tmpfs 4.9G 0 4.9G 0% /sys/fs/cgroup tmpfs 1001M 0 1001M 0% /run/user/0 - free -h:
total used free shared buff/cache available Mem: 9.8G 2.8G 4.1G 452K 2.8G 6.7G Swap: 0B 0B 0B
