0

It would seem to me that we have a bottleneck we just cant seem to get over.

We have a setup which contains 4 NVME drives in Raid 10

We are using mariadb 10.4

We have indexes

The workload that we have will 99% of the time be IO bound there is no way around that fact

What I have seen while watching the performance dashboard in mysql workbench is that both the SATA SSD and NVME SSD read at about 100MB for the same data set

Now if I am searching through 200M rows(or pulling 200M) I would think that the Innodb disk read would go faster then 100MB

I mean these drives should be capable of reading 3GB(s) so I would at least expect to see like 500MB(s)

The reality here is that I am seeing the exact same speed on the NVME that I see on the SATA SSD

So the question I have is how do I get these disk to be fully utilized

Here is the only config settings outside of replication

sql_mode                          = 'NO_ENGINE_SUBSTITUTION'
max_allowed_packet                = 256M
innodb_file_per_table
innodb_buffer_pool_size           = 100G
innodb_log_file_size              = 128M

innodb_write_io_threads = 16 // Not sure these 2 lines actually do anything 
innodb_read_io_threads = 16
Joséph Flames
  • 204
  • 1
  • 12

1 Answers1

0

IO bound there is no way around that fact

Unless you are very confident on the suitability of indexes this seems a little presumptuous.

Assuming your right, this would imply a 100% write workload, or a data size orders of magnitude higher that RAM available and a uniform distribution of small accesses?

innodb_io_capacity is providing a default limitation and your hardware is capable of more.

Also if you are reading so frequently, your innodb_buffer_pool_size isn't sufficient.

danblack
  • 12,130
  • 2
  • 22
  • 41
  • 1
    The use case is customers like to pull all the records for an entire month usually on the first of the month and some of the larger customers can have upwards of 100M records in a month The reason I am confident it will always be IO bound is the only time this data is accessed is once a month. So no amount of RAM or buffer_pool_size is gonna help here So knowing that the workload is IO bound I need to get mariadb to utilize the full power of the NVME drives – Joséph Flames Jan 19 '23 at 01:46
  • Makes sense. Is the customer access like a dump in PK order? [innodb_flush_method=O_DIRECT](https://mariadb.com/kb/en/innodb-system-variables/#innodb_flush_method) as this applies to all access not just flushes. Maybe increase [innodb_read_io_threads](https://mariadb.com/kb/en/innodb-system-variables/#innodb_read_io_threads). – danblack Jan 19 '23 at 01:57
  • I updated my question with the config settings maybe it shows what I may or may not be doing right. It is actually legacy code I adopted(not by choice) the original developer was doing something god awful like SELECT * WHERE customer_id=xxx LIMIT 25 OFFSET 1000 Now I have updated that so it has some better indexes that only search for records for a particular customer and also does not use that awful offset instead it uses key seeking which is far faster I think now the bottleneck is not the query,indexes,etc but instead I think it is mariadb not using all the resources it has – Joséph Flames Jan 19 '23 at 02:07