Diagnosing high disk read rates

Help Workload Profiling
1

Our cluster is suffering from extremely high disk read rates, resulting in hight disk wait times.

We’re investigating how much is due to slow storage…

However, this has raised a couple of points of interest

  1. how do I work out which tables are being read – are there stats available per table for disk i/o ?

  2. Does Singlestore allow the o/s to cache database file data ? (ie: does it use O_DIRECT or…)

  3. How can I improve the caching of whatever is causing the high disk reads ?

2
  1. MV_ACTIVITIES and related management views can tell you what queries are getting run the most.
  2. Yes. Columnstore blobs get cached by the OS file system.
  3. Allocating more RAM to the OS file system can help, if the I/O bottleneck is related to columnstore reads. If you’re using unlimited storage databases, then there are variables to configure the local blob cache size – see the docs on those, in that case.
1 Like
3

If your using studio you can use the workload monitoring page to do find out what is going disk IO via the UI as well : SingleStoreDB Cloud · SingleStore Documentation.

1 Like