* Slow ProbeSetData SQL query ** The case We were looking to optimize a query for correlations. On Penguin2 (standard drives, RAID5) the query took 1 hour. On Tux01 (solid state NVME) it took 6 minutes. Adding an index for StrainId (see explorations below) reduced that query to 3 minutes - which is kinda acceptable. The real problem, however is that this is a quadratic search - so it will get worse quickly - and we need to solve it. So what does ProbeSetData contain? #+BEGIN_SRC SQL select * from ProbeSetData limit 5; +----+----------+-------+ | Id | StrainId | value | +----+----------+-------+ | 1 | 1 | 5.742 | | 1 | 2 | 5.006 | | 1 | 3 | 6.079 | | 1 | 4 | 6.414 | | 1 | 5 | 4.885 | +----+----------+-------+ #+END_SRC You can see Id is sectioned in the file (and there are not that many Ids) but StrainId is *distributed* through the database file and some 'StrainIds' match many data points. Id stands for Dataset and StrainId really means Measurement(!) Our query looked for 1,236,088 measurement distributed over a 53Gb file (and an even larger index file). Turns out the full table is read many many times over for one particular query pivoting on strainid... We have the following options: 1. Reorder the table 2. Use column based storage 3. Use compression 4. Use a different storage layout *** Reorder the table We could reorder the table on StrainID which would make this search much faster but it would many common (dataset) queries slower. So, that is not a great idea. One thing we could try is create a copy of the first table. *** Use column based storage Column-based storage works when you need a subset of the data in the table. In this case it won't help much because, even though the pivot itself would be faster, we still traverse all data to get IDs and values. *** Use compression Compression reduces the size on disk and may be beneficial. Real life metrics on the internet don't show that much improvement, but we could try native compression and/or ZFS. *** Use a different storage layout My prediction is that we can not get around this. ** Exploration In the following steps I reduce the complex case to a simple case explaining the performance bottleneck we are seeing today. I did not add comments, but you can see what I did. #+BEGIN_SRC SQL MariaDB [db_webqtl]> select * from ProbeSetXRef limit 2; +------------------+------------+--------+------------+--------------------+------------+------------------+---------------------+------------+------------------+--------+--------------------+------+ | ProbeSetFreezeId | ProbeSetId | DataId | Locus_old | LRS_old | pValue_old | mean | se | Locus | LRS | pValue | additive | h2 | +------------------+------------+--------+------------+--------------------+------------+------------------+---------------------+------------+------------------+--------+--------------------+------+ | 1 | 1 | 1 | 10.095.400 | 13.3971627898894 | 0.163 | 5.48794285714286 | 0.08525787814808819 | rs13480619 | 12.590069931048 | 0.269 | -0.28515625 | NULL | | 1 | 2 | 2 | D15Mit189 | 10.042057464356201 | 0.431 | 9.90165714285714 | 0.0374686634976217 | rs29535974 | 10.5970737900941 | 0.304 | -0.116783333333333 | NULL | +------------------+------------+--------+------------+--------------------+------------+------------------+---------------------+------------+------------------+--------+--------------------+------+ 2 rows in set (0.001 sec) #+END_SRC #+BEGIN_SRC SQL MariaDB [db_webqtl]> SELECT ProbeSet.Name,T4.value FROM (ProbeSet, ProbeSetXRef, ProbeSetFreeze) left join ProbeSetData as T4 on T4.Id = ProbeSetXRef.DataId -> and T4.StrainId=4 -> limit 5; +------+-------+ | Name | value | +------+-------+ | NULL | 6.414 | | NULL | 6.414 | | NULL | 6.414 | | NULL | 6.414 | | NULL | 6.414 | +------+-------+ #+END_SRC #+BEGIN_SRC SQL MariaDB [db_webqtl]> SELECT ProbeSet.Name,T4.value,T5.value FROM (ProbeSet, ProbeSetXRef, ProbeSetFreeze) left join ProbeSetData as T4 on T4.Id = ProbeSetXRef.DataId and T4.StrainId=4 left join ProbeSetData as T5 on T5.Id = ProbeSetXRef.DataId and T5.StrainId=5 WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id and ProbeSetFreeze.Name = 'UMUTAffyExon_0209_RMA' and ProbeSet.Id = ProbeSetXRef.ProbeSetId order by ProbeSet.Id limit 5; +---------+---------+ | Name | value | +---------+---------+ | 4331726 | 5.52895 | | 5054239 | 6.29465 | | 4642578 | 9.13706 | | 4398221 | 6.77672 | | 5543360 | 4.30016 | +---------+---------+ #+END_SRC #+BEGIN_SRC SQL SELECT ProbeSet.Name,T4.value FROM (ProbeSet, ProbeSetXRef, ProbeSetFreeze) left join ProbeSetData as T4 on T4.Id = ProbeSetXRef.DataId and T4.StrainId=4 WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id and ProbeSetFreeze.Name = 'UMUTAffyExon_0209_RMA' and ProbeSet.Id = ProbeSetXRef.ProbeSetId order by ProbeSet.Id ; 1236087 rows in set (19.173 sec) #+END_SRC #+BEGIN_SRC SQL SELECT ProbeSet.Name,T4.value FROM (ProbeSet, ProbeSetXRef, ProbeSetFreeze) left join ProbeSetData as T4 on T4.StrainId=4 and T4.Id = ProbeSetXRef.DataId WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id and ProbeSetFreeze.Name = 'UMUTAffyExon_0209_RMA' and ProbeSet.Id = ProbeSetXRef.ProbeSetId order by ProbeSet.Id ; 1236087 rows in set (19.173 sec) #+END_SRC #+BEGIN_SRC SQL SELECT ProbeSet.Name FROM (ProbeSet, ProbeSetXRef, ProbeSetFreeze) WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id and ProbeSetFreeze.Name = 'UMUTAffyExon_0209_RMA' and ProbeSet.Id = ProbeSetXRef.ProbeSetId order by ProbeSet.Id ; #+END_SRC Find all the probeset 'names' (probe sequence included) for one dataset: #+BEGIN_SRC SQL SELECT count(DISTINCT ProbeSet.Name) FROM (ProbeSet, ProbeSetXRef, ProbeSetFreeze) WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id and ProbeSetFreeze.Name = 'UMUTAffyExon_0209_RMA' and ProbeSet.Id = ProbeSetXRef.ProbeSetId order by ProbeSet.Id; +-------------------------------+ | count(DISTINCT ProbeSet.Name) | +-------------------------------+ | 1236087 | +-------------------------------+ #+END_SRC Now for each of those probesets: #+BEGIN_SRC SQL SELECT ProbeSet.Name,T4.value FROM (ProbeSet, ProbeSetXRef) left join ProbeSetData as T4 on T4.StrainId=4 and T4.Id = ProbeSetXRef.DataId WHERE ProbeSet.Id = ProbeSetXRef.ProbeSetId order by ProbeSet.Id limit 5; #+END_SRC ProbeSetXRef contains the p-values: #+BEGIN_SRC SQL select * from ProbeSetXRef limit 5; +------------------+------------+--------+------------+--------------------+------------+-------------------+---------------------+------------+------------------+--------+--------------------+------+ | ProbeSetFreezeId | ProbeSetId | DataId | Locus_old | LRS_old | pValue_old | mean | se | Locus | LRS | pValue | additive | h2 | +------------------+------------+--------+------------+--------------------+------------+-------------------+---------------------+------------+------------------+--------+--------------------+------+ | 1 | 1 | 1 | 10.095.400 | 13.3971627898894 | 0.163 | 5.48794285714286 | 0.08525787814808819 | rs13480619 | 12.590069931048 | 0.269 | -0.28515625 | NULL | | 1 | 2 | 2 | D15Mit189 | 10.042057464356201 | 0.431 | 9.90165714285714 | 0.0374686634976217 | rs29535974 | 10.5970737900941 | 0.304 | -0.116783333333333 | NULL | #+END_SRC #+BEGIN_SRC SQL SELECT count(T4.value) FROM (ProbeSet, ProbeSetXRef) left join ProbeSetData as T4 on T4.StrainId=4 and T4.Id = ProbeSetXRef.DataId WHERE ProbeSet.Id = ProbeSetXRef.ProbeSetId ; #+END_SRC #+BEGIN_SRC SQL SELECT count(T4.value) FROM (ProbeSet, ProbeSetXRef) left join ProbeSetData as T4 on T4.StrainId=4 limit 5; #+END_SRC #+BEGIN_SRC SQL select value from (ProbeSetData) where StrainId=4 limit 5; #+END_SRC So, this is the sloooow baby: #+BEGIN_SRC SQL select count(id) from (ProbeSetData) where StrainId=4; | ProbeSetData | 0 | DataId | 2 | StrainId | A | 4852908856 | NULL | NULL | | BTREE | | | -rw-rw---- 1 mysql mysql 53G Mar 3 23:49 ProbeSetData.MYD -rw-rw---- 1 mysql mysql 66G Mar 4 03:00 ProbeSetData.MYI #+END_SRC #+BEGIN_SRC SQL create index strainid on ProbeSetData(StrainId); Stage: 1 of 2 'Copy to tmp table' 8.77% of stage done Stage: 2 of 2 'Enabling keys' 0% of stage done #+END_SRC #+BEGIN_SRC SQL MariaDB [db_webqtl]> create index strainid on ProbeSetData(StrainId); Query OK, 5111384047 rows affected (2 hours 56 min 25.807 sec) Records: 5111384047 Duplicates: 0 Warnings: 0 #+END_SRC #+BEGIN_SRC SQL MariaDB [db_webqtl]> select count(id) from (ProbeSetData) where StrainId=4; +-----------+ | count(id) | +-----------+ | 14267545 | +-----------+ 1 row in set (19.707 sec) #+END_SRC #+BEGIN_SRC SQL MariaDB [db_webqtl]> select count(*) from ProbeSetData where strainid = 140; +----------+ | count(*) | +----------+ | 10717771 | +----------+ 1 row in set (10.161 sec) #+END_SRC #+BEGIN_SRC SQL MariaDB [db_webqtl]> select count(*) from ProbeSetData where strainid = 140 and id=4; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.000 sec) #+END_SRC #+BEGIN_SRC SQL MariaDB [db_webqtl]> select count(*) from ProbeSetData where strainid = 4 and id=4; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.000 sec) #+END_SRC #+BEGIN_SRC SQL select id from ProbeSetFreeze where id=1; WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id and ProbeSetFreeze.Name = 'UMUTAffyExon_0209_RMA' and ProbeSet.Id = ProbeSetXRef.ProbeSetId order by ProbeSet.Id limit 5; #+END_SRC #+BEGIN_SRC SQL select count(ProbeSetId) from ProbeSetXRef where ProbeSetFreezeId=1; +-------------------+ | count(ProbeSetId) | +-------------------+ | 12422 | +-------------------+ 1 row in set (0.006 sec) #+END_SRC #+BEGIN_SRC SQL select count(ProbeSetId) from (ProbeSetXRef,ProbeSetFreeze) where ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id and ProbeSetFreeze.Name = 'UMUTAffyExon_0209_RMA'; #+END_SRC #+BEGIN_SRC SQL MariaDB [db_webqtl]> select count(ProbeSetId) from (ProbeSetXRef,ProbeSetFreeze) where -> ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id -> and ProbeSetFreeze.Name = 'UMUTAffyExon_0209_RMA'; +-------------------+ | count(ProbeSetId) | +-------------------+ | 1236087 | +-------------------+ 1 row in set (0.594 sec) #+END_SRC ProbeSetXRef.ProbeSetFreezeId is 206, so #+BEGIN_SRC SQL MariaDB [db_webqtl]> select count(ProbeSetId) from (ProbeSetXRef) where ProbeSetXRef.ProbeSetFreezeId = 206; +-------------------+ | count(ProbeSetId) | +-------------------+ | 1236087 | +-------------------+ 1 row in set (0.224 sec) #+END_SRC #+BEGIN_SRC SQL MariaDB [db_webqtl]> select count(*) from ProbeSetData where strainid = 1 and id=4; +----------+ | count(*) | +----------+ | 1 | +----------+ 1 row in set (0.000 sec) #+END_SRC ** Original query This is the original query generated by GN2 that takes 1 hour on Penguin2 and 3 minutes on Tux01. Note it fetches all values for these 'traits' so essentially traverses the full 53GB database table (and even larger index) for each of them. #+BEGIN_SRC SQL SELECT ProbeSet.Name,T4.value, T5.value, T6.value, T7.value, T8.value, T9.value, T10.value, T11.value, T12.value, T13.value, T14.value, T15.value, T16.value, T17.value, T18.value, T19.value, T20.value, T21.value, T22.value, T23.value, T24.value, T25.value, T26.value, T28.value, T29.value, T30.value, T31.value, T33.value, T35.value, T36.value, T37.value, T39.value, T98.value, T100.value, T103.value FROM (ProbeSet, ProbeSetXRef, ProbeSetFreeze) left join ProbeSetData as T4 on T4.Id = ProbeSetXRef.DataId and T4.StrainId=4 left join ProbeSetData as T5 on T5.Id = ProbeSetXRef.DataId and T5.StrainId=5 left join ProbeSetData as T6 on T6.Id = ProbeSetXRef.DataId and T6.StrainId=6 left join ProbeSetData as T7 on T7.Id = ProbeSetXRef.DataId and T7.StrainId=7 left join ProbeSetData as T8 on T8.Id = ProbeSetXRef.DataId and T8.StrainId=8 left join ProbeSetData as T9 on T9.Id = ProbeSetXRef.DataId and T9.StrainId=9 left join ProbeSetData as T10 on T10.Id = ProbeSetXRef.DataId and T10.StrainId=10 left join ProbeSetData as T11 on T11.Id = ProbeSetXRef.DataId and T11.StrainId=11 left join ProbeSetData as T12 on T12.Id = ProbeSetXRef.DataId and T12.StrainId=12 left join ProbeSetData as T13 on T13.Id = ProbeSetXRef.DataId and T13.StrainId=13 left join ProbeSetData as T14 on T14.Id = ProbeSetXRef.DataId and T14.StrainId=14 left join ProbeSetData as T15 on T15.Id = ProbeSetXRef.DataId and T15.StrainId=15 left join ProbeSetData as T16 on T16.Id = ProbeSetXRef.DataId and T16.StrainId=16 left join ProbeSetData as T17 on T17.Id = ProbeSetXRef.DataId and T17.StrainId=17 left join ProbeSetData as T18 on T18.Id = ProbeSetXRef.DataId and T18.StrainId=18 left join ProbeSetData as T19 on T19.Id = ProbeSetXRef.DataId and T19.StrainId=19 left join ProbeSetData as T20 on T20.Id = ProbeSetXRef.DataId and T20.StrainId=20 left join ProbeSetData as T21 on T21.Id = ProbeSetXRef.DataId and T21.StrainId=21 left join ProbeSetData as T22 on T22.Id = ProbeSetXRef.DataId and T22.StrainId=22 left join ProbeSetData as T23 on T23.Id = ProbeSetXRef.DataId and T23.StrainId=23 left join ProbeSetData as T24 on T24.Id = ProbeSetXRef.DataId and T24.StrainId=24 left join ProbeSetData as T25 on T25.Id = ProbeSetXRef.DataId and T25.StrainId=25 left join ProbeSetData as T26 on T26.Id = ProbeSetXRef.DataId and T26.StrainId=26 left join ProbeSetData as T28 on T28.Id = ProbeSetXRef.DataId and T28.StrainId=28 left join ProbeSetData as T29 on T29.Id = ProbeSetXRef.DataId and T29.StrainId=29 left join ProbeSetData as T30 on T30.Id = ProbeSetXRef.DataId and T30.StrainId=30 left join ProbeSetData as T31 on T31.Id = ProbeSetXRef.DataId and T31.StrainId=31 left join ProbeSetData as T33 on T33.Id = ProbeSetXRef.DataId and T33.StrainId=33 left join ProbeSetData as T35 on T35.Id = ProbeSetXRef.DataId and T35.StrainId=35 left join ProbeSetData as T36 on T36.Id = ProbeSetXRef.DataId and T36.StrainId=36 left join ProbeSetData as T37 on T37.Id = ProbeSetXRef.DataId and T37.StrainId=37 left join ProbeSetData as T39 on T39.Id = ProbeSetXRef.DataId and T39.StrainId=39 left join ProbeSetData as T98 on T98.Id = ProbeSetXRef.DataId and T98.StrainId=98 left join ProbeSetData as T100 on T100.Id = ProbeSetXRef.DataId and T100.StrainId=100 left join ProbeSetData as T103 on T103.Id = ProbeSetXRef.DataId and T103.StrainId=103 WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id and ProbeSetFreeze.Name = 'UMUTAffyExon_0209_RMA' and ProbeSet.Id = ProbeSetXRef.ProbeSetId order by ProbeSet.Id #+END_SRC