i'm working on a mariadb cluster with high performance requirements in terms of number of queries.
i can work with read-uncommited. actually, most of the app would not require more than eventual consistency. i'm well aware that app should probably be using cassandra or a similar tool, but moving from mysql is simply not currently an option. mariadb using a cassandra store neither. i'll be probably moving part of the load to memcache/redis for parts that do not need to be in an SQL store. little by little, though.
i've worked quite extensively with mysql tuning in the old days, but not that much in the past 5 to 10 years and i feel i might be quite outdated and additionally working with both an environment and a workload i'm not used to on mysql.
i can obviously add more NDB members but i'd rather switch to maria if possible.
the overall quantity of data is rather small : around 10Go but we expect about *10 growth quite shortly. actually more, but i'll probably manage to optimise many things by then.
the current NDB cluster has 2 members
it performs about 10k select queries per second on each member, and about 200 inserts/s. the rest is comparatively neglectible.
most of the select and inserts will be a single row.
there are a few bigger queries, but performance on those queries is not critical as long as running them does not produce contention.
hardware and OS requirements. i have no control over most of these.
os is ubuntu 64 xenial. i'll probably be able to grab a bionic. nothing else.
barebone machines is unfortunately not an option : but running a de facto almost dedicated esx is feasible.
current esx hosts are g8 machines with 4 disks raid 10 for the datastore. i should be able to get better hardware, but that's not the point here : i need to beat NDB on similar hardware. this is quite the challenge because the workload favors ndb a lot.
the filesystem is XFS. no specific tuning. i have full control over this choice. i will consider dedicated drives if it is significantly faster on linux.
8 Go RAM
no swap at all
6 Go buffer cache
innodb_log_file_size = 512M
innodb_log_buffer_size = 64M
doublewrite is active
innodb_flush_log_at_trx_commit = 0
8 read threads
8 write threads
8 buffer pool instances.
i used the number of cpus for the 3 last settings. not sure what is best.
i'm benchmarking by replayiing about 10mn of load on the cluster.
i'm using 3 members and 10 to 100 parallel connections per member.
currently, i manage to replay in about 3 to 4 minutes
i feel the machines are not fully loaded and i'm not giving the full power to innodb. or gallera throttles it.
i'd like to be under 2 if possible
settings i have not considered ? things to check during the bench ?
note : i want to achieve the best possible on the current hardware. please do not suggest new servers. this topis is about mysql tuning.