From 5787a6ec5a54e2c98aed38f4c68291cb65b7f609 Mon Sep 17 00:00:00 2001 From: Pjotr Prins Date: Tue, 13 Apr 2021 02:37:13 -0500 Subject: SQL notes --- doc/database.org | 156 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 156 insertions(+) (limited to 'doc') diff --git a/doc/database.org b/doc/database.org index 5107b660..f3d4d92e 100644 --- a/doc/database.org +++ b/doc/database.org @@ -1368,3 +1368,159 @@ mysql> select * from SnpSource limit 5; Empty set (0.00 sec) Hmmm. This is the test database. Then there are the plink files and VCF files. + +* Optimize SQL? + +We were facing some issues with slow queries. A query +was really slow on Penguin2: + +: time mysql -u webqtlout -pwebqtlout db_webqtl < ~/chunk.sql > /dev/null +: real 0m13.082s +: user 0m0.292s +: sys 0m0.032s + +Runs in 1s on Tux01 and 13s on P2, why is that? The gist of it +was increasing an InnoDB cache size(!) + +Interestingly, Penguin2 is running InnoDB on a much slower storage. +It has more indices that Tux01(?!). Probably due to things we have +been trying to make the datatables faster. + +Meanwhile the query is one with many joins: + +#+begin_src sql +SELECT ProbeSet.Name,ProbeSetXRef.DataId, T4.value, T5.value, T6.value, T7.value, T8.value, T9.value, T10.value, T11.value, T12.value, T14.value, T15.value, T17.value, T18.value, T19.value, T20.value, T21.value, T22.value, T24.value, T25.value, T26.value, T28.value, T29.value, T30.value, T31.value, T35.value, T36.value, T37.value, T39.value, T98.value, T99.value, T100.value, T103.value, T487.value, T105.value, T106.value, T110.value FROM (ProbeSet, ProbeSetXRef, ProbeSetFreeze) + left join ProbeSetData as T4 on T4.Id = ProbeSetXRef.DataId + and T4.StrainId=4 + (...) + left join ProbeSetData as T110 on T110.Id = ProbeSetXRef.DataId + and T110.StrainId=110 + WHERE ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id + and ProbeSetFreeze.Name = 'HC_M2_0606_P' + and ProbeSet.Id = ProbeSetXRef.ProbeSetId + order by ProbeSet.Id +#+end_src + +And is blazingly fast on Tux01 and (now) fast enough on Penguin2. + +First I checked the tables for indices and storage type. Next I +checked the difference in configuration. + +** Check tables + +Tables (ProbeSetData, ProbeSet, ProbeSetXRef, ProbeSetFreeze) + +*** ProbeSetData + +Same on Tux01 and P2: + +: show indexes from ProbeSetData ; + ++--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | ++--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +| ProbeSetData | 0 | DataId | 1 | Id | A | 47769944 | NULL | NULL | | BTREE | | | +| ProbeSetData | 0 | DataId | 2 | StrainId | A | 5111384047 | NULL | NULL | | BTREE | | | ++--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ + +*** ProbeSetFreeze + +Tux01 has less indexes than P2(!): + ++----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | ++----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +| ProbeSetFreeze | 0 | PRIMARY | 1 | Id | A | 911 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 0 | FullName | 1 | FullName | A | 911 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 0 | Name | 1 | Name | A | 911 | NULL | NULL | YES | BTREE | | | +| ProbeSetFreeze | 1 | NameIndex | 1 | Name2 | A | 911 | NULL | NULL | | BTREE | | | ++----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +4 rows in set (0.000 sec) + ++----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | ++----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +| ProbeSetFreeze | 0 | PRIMARY | 1 | Id | A | 883 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 0 | FullName | 1 | FullName | A | 883 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 0 | Name | 1 | Name | A | 883 | NULL | NULL | YES | BTREE | | | +| ProbeSetFreeze | 1 | NameIndex | 1 | Name2 | A | 883 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 1 | ShortName | 1 | ShortName | A | 883 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 1 | ProbeFreezeId | 1 | ProbeFreezeId | A | 441 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 1 | conf_and_public | 1 | confidentiality | A | 3 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 1 | conf_and_public | 2 | public | A | 4 | NULL | NULL | | BTREE | | | ++----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +8 rows in set (0.00 sec) + +*** ProbeSet + +Identical indexes + +*** ProbeSetXRef + +Tux01 has less indexes than P2(!): + +MariaDB [db_webqtl]> show indexes from ProbeSetXRef ; ++--------------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | ++--------------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +| ProbeSetXRef | 0 | ProbeSetId | 1 | ProbeSetFreezeId | A | 885 | NULL | NULL | | BTREE | | | +| ProbeSetXRef | 0 | ProbeSetId | 2 | ProbeSetId | A | 47713039 | NULL | NULL | | BTREE | | | +| ProbeSetXRef | 0 | DataId_IDX | 1 | DataId | A | 47713039 | NULL | NULL | | BTREE | | | +| ProbeSetXRef | 1 | Locus_IDX | 1 | Locus | A | 15904346 | NULL | NULL | YES | BTREE | | | ++--------------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +4 rows in set (0.000 sec) + + +MariaDB [db_webqtl]> show indexes from ProbeSetXRef ; ++--------------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | ++--------------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +| ProbeSetXRef | 0 | ProbeSetId | 1 | ProbeSetFreezeId | A | 856 | NULL | NULL | | BTREE | | | +| ProbeSetXRef | 0 | ProbeSetId | 2 | ProbeSetId | A | 46412145 | NULL | NULL | | BTREE | | | +| ProbeSetXRef | 0 | DataId_IDX | 1 | DataId | A | 46412145 | NULL | NULL | | BTREE | | | +| ProbeSetXRef | 1 | ProbeSetId1 | 1 | ProbeSetId | A | 5156905 | NULL | NULL | | BTREE | | | +| ProbeSetXRef | 1 | Locus | 1 | Locus | A | 23206072 | NULL | NULL | YES | BTREE | | | ++--------------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +5 rows in set (0.00 sec) + +** Check storage + +The database in Tux01 is mounted on NVME. On Penguin2 it +is slower SATA with RAID5. + +Also on Penguin2 the following tables are using InnoDB instead of +MyISAM + +#+begin_src sh +-rw-rw---- 1 mysql mysql 79691776 Oct 15 2019 AccessLog.ibd +-rw-rw---- 1 mysql mysql 196608 Oct 24 2019 Docs.ibd +-rw-rw---- 1 mysql mysql 63673729024 Jul 10 2020 GenoData.ibd +-rw-rw---- 1 mysql mysql 34787557376 Jul 9 2020 ProbeData.ibd +-rw-rw---- 1 mysql mysql 254690721792 Jul 10 2020 ProbeSetData.ibd +-rw-rw---- 1 mysql mysql 32103202816 Jul 9 2020 SnpAll.ibd +-rw-rw---- 1 mysql mysql 98304 May 6 2020 TraitMetadata.ibd +#+end_src + +This [[https://www.liquidweb.com/kb/mysql-performance-myisam-vs-innodb/][article]] suggests that myISAM will be faster for our use case. + +** Configuration + +There was one setting on Tux01 missing on P2 + +: +innodb_buffer_pool_size=1024M + +Running the same query twice (so you can see the warmup after +a restart of MariaDB) + +#+begin_src sh +penguin2:/etc$ time mysql -u webqtlout -pwebqtlout db_webqtl < ~/chunk.sql > ~/test.out +real 0m4.253s +user 0m0.276s +sys 0m0.040s +penguin2:/etc$ time mysql -u webqtlout -pwebqtlout db_webqtl < ~/chunk.sql > ~/test.out +real 0m2.633s +user 0m0.296s +sys 0m0.028s +#+end_src + +That is much better :) -- cgit v1.2.3 From f628f14bae508ec1d86606cfb9fdad6096f8b29f Mon Sep 17 00:00:00 2001 From: Pjotr Prins Date: Tue, 13 Apr 2021 02:41:20 -0500 Subject: SQL notes --- doc/database.org | 15 ++++++++------- 1 file changed, 8 insertions(+), 7 deletions(-) (limited to 'doc') diff --git a/doc/database.org b/doc/database.org index f3d4d92e..cd833b83 100644 --- a/doc/database.org +++ b/doc/database.org @@ -1339,7 +1339,8 @@ The SNP count info for the BXD is calculated like this startMb += stepMb #+end_src -select * from BXDSnpPosition limit 5; +: select * from BXDSnpPosition limit 5; + +------+-----------+-----------+----------+ | Chr | StrainId1 | StrainId2 | Mb | +------+-----------+-----------+----------+ @@ -1435,7 +1436,7 @@ Tux01 has less indexes than P2(!): | ProbeSetFreeze | 0 | Name | 1 | Name | A | 911 | NULL | NULL | YES | BTREE | | | | ProbeSetFreeze | 1 | NameIndex | 1 | Name2 | A | 911 | NULL | NULL | | BTREE | | | +----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ -4 rows in set (0.000 sec) +: 4 rows in set (0.000 sec) +----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | @@ -1449,7 +1450,7 @@ Tux01 has less indexes than P2(!): | ProbeSetFreeze | 1 | conf_and_public | 1 | confidentiality | A | 3 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 1 | conf_and_public | 2 | public | A | 4 | NULL | NULL | | BTREE | | | +----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ -8 rows in set (0.00 sec) +: 8 rows in set (0.00 sec) *** ProbeSet @@ -1459,7 +1460,7 @@ Identical indexes Tux01 has less indexes than P2(!): -MariaDB [db_webqtl]> show indexes from ProbeSetXRef ; +: MariaDB [db_webqtl]> show indexes from ProbeSetXRef ; +--------------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ @@ -1468,10 +1469,10 @@ MariaDB [db_webqtl]> show indexes from ProbeSetXRef ; | ProbeSetXRef | 0 | DataId_IDX | 1 | DataId | A | 47713039 | NULL | NULL | | BTREE | | | | ProbeSetXRef | 1 | Locus_IDX | 1 | Locus | A | 15904346 | NULL | NULL | YES | BTREE | | | +--------------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ -4 rows in set (0.000 sec) +: 4 rows in set (0.000 sec) -MariaDB [db_webqtl]> show indexes from ProbeSetXRef ; +: MariaDB [db_webqtl]> show indexes from ProbeSetXRef ; +--------------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ @@ -1481,7 +1482,7 @@ MariaDB [db_webqtl]> show indexes from ProbeSetXRef ; | ProbeSetXRef | 1 | ProbeSetId1 | 1 | ProbeSetId | A | 5156905 | NULL | NULL | | BTREE | | | | ProbeSetXRef | 1 | Locus | 1 | Locus | A | 23206072 | NULL | NULL | YES | BTREE | | | +--------------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ -5 rows in set (0.00 sec) +: 5 rows in set (0.00 sec) ** Check storage -- cgit v1.2.3 From d6d339adbbdbf18e5acabaa180092184cabacec8 Mon Sep 17 00:00:00 2001 From: Pjotr Prins Date: Tue, 13 Apr 2021 02:43:03 -0500 Subject: SQL notes - table layout --- doc/database.org | 11 +---------- 1 file changed, 1 insertion(+), 10 deletions(-) (limited to 'doc') diff --git a/doc/database.org b/doc/database.org index cd833b83..dd068d71 100644 --- a/doc/database.org +++ b/doc/database.org @@ -1417,28 +1417,23 @@ Same on Tux01 and P2: : show indexes from ProbeSetData ; -+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | ProbeSetData | 0 | DataId | 1 | Id | A | 47769944 | NULL | NULL | | BTREE | | | | ProbeSetData | 0 | DataId | 2 | StrainId | A | 5111384047 | NULL | NULL | | BTREE | | | -+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ *** ProbeSetFreeze Tux01 has less indexes than P2(!): -+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | ProbeSetFreeze | 0 | PRIMARY | 1 | Id | A | 911 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 0 | FullName | 1 | FullName | A | 911 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 0 | Name | 1 | Name | A | 911 | NULL | NULL | YES | BTREE | | | | ProbeSetFreeze | 1 | NameIndex | 1 | Name2 | A | 911 | NULL | NULL | | BTREE | | | -+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ : 4 rows in set (0.000 sec) -+----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | ProbeSetFreeze | 0 | PRIMARY | 1 | Id | A | 883 | NULL | NULL | | BTREE | | | @@ -1449,7 +1444,6 @@ Tux01 has less indexes than P2(!): | ProbeSetFreeze | 1 | ProbeFreezeId | 1 | ProbeFreezeId | A | 441 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 1 | conf_and_public | 1 | confidentiality | A | 3 | NULL | NULL | | BTREE | | | | ProbeSetFreeze | 1 | conf_and_public | 2 | public | A | 4 | NULL | NULL | | BTREE | | | -+----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ : 8 rows in set (0.00 sec) *** ProbeSet @@ -1461,19 +1455,17 @@ Identical indexes Tux01 has less indexes than P2(!): : MariaDB [db_webqtl]> show indexes from ProbeSetXRef ; -+--------------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | ProbeSetXRef | 0 | ProbeSetId | 1 | ProbeSetFreezeId | A | 885 | NULL | NULL | | BTREE | | | | ProbeSetXRef | 0 | ProbeSetId | 2 | ProbeSetId | A | 47713039 | NULL | NULL | | BTREE | | | | ProbeSetXRef | 0 | DataId_IDX | 1 | DataId | A | 47713039 | NULL | NULL | | BTREE | | | | ProbeSetXRef | 1 | Locus_IDX | 1 | Locus | A | 15904346 | NULL | NULL | YES | BTREE | | | -+--------------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ : 4 rows in set (0.000 sec) + : MariaDB [db_webqtl]> show indexes from ProbeSetXRef ; -+--------------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | ProbeSetXRef | 0 | ProbeSetId | 1 | ProbeSetFreezeId | A | 856 | NULL | NULL | | BTREE | | | @@ -1481,7 +1473,6 @@ Tux01 has less indexes than P2(!): | ProbeSetXRef | 0 | DataId_IDX | 1 | DataId | A | 46412145 | NULL | NULL | | BTREE | | | | ProbeSetXRef | 1 | ProbeSetId1 | 1 | ProbeSetId | A | 5156905 | NULL | NULL | | BTREE | | | | ProbeSetXRef | 1 | Locus | 1 | Locus | A | 23206072 | NULL | NULL | YES | BTREE | | | -+--------------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ : 5 rows in set (0.00 sec) ** Check storage -- cgit v1.2.3 From 0815b6004a167c5c25f6994aeb31ec3161619c6d Mon Sep 17 00:00:00 2001 From: Pjotr Prins Date: Tue, 13 Apr 2021 02:44:03 -0500 Subject: SQL notes - table layout --- doc/database.org | 56 ++++++++++++++++++++++++++++---------------------------- 1 file changed, 28 insertions(+), 28 deletions(-) (limited to 'doc') diff --git a/doc/database.org b/doc/database.org index dd068d71..d5462d4e 100644 --- a/doc/database.org +++ b/doc/database.org @@ -1418,32 +1418,32 @@ Same on Tux01 and P2: : show indexes from ProbeSetData ; | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | -+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ -| ProbeSetData | 0 | DataId | 1 | Id | A | 47769944 | NULL | NULL | | BTREE | | | -| ProbeSetData | 0 | DataId | 2 | StrainId | A | 5111384047 | NULL | NULL | | BTREE | | | +|--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------| +| ProbeSetData | 0 | DataId | 1 | Id | A | 47769944 | NULL | NULL | | BTREE | | | +| ProbeSetData | 0 | DataId | 2 | StrainId | A | 5111384047 | NULL | NULL | | BTREE | | | *** ProbeSetFreeze Tux01 has less indexes than P2(!): | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | -+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ -| ProbeSetFreeze | 0 | PRIMARY | 1 | Id | A | 911 | NULL | NULL | | BTREE | | | -| ProbeSetFreeze | 0 | FullName | 1 | FullName | A | 911 | NULL | NULL | | BTREE | | | -| ProbeSetFreeze | 0 | Name | 1 | Name | A | 911 | NULL | NULL | YES | BTREE | | | -| ProbeSetFreeze | 1 | NameIndex | 1 | Name2 | A | 911 | NULL | NULL | | BTREE | | | +|----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------| +| ProbeSetFreeze | 0 | PRIMARY | 1 | Id | A | 911 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 0 | FullName | 1 | FullName | A | 911 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 0 | Name | 1 | Name | A | 911 | NULL | NULL | YES | BTREE | | | +| ProbeSetFreeze | 1 | NameIndex | 1 | Name2 | A | 911 | NULL | NULL | | BTREE | | | : 4 rows in set (0.000 sec) | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | -+----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ -| ProbeSetFreeze | 0 | PRIMARY | 1 | Id | A | 883 | NULL | NULL | | BTREE | | | -| ProbeSetFreeze | 0 | FullName | 1 | FullName | A | 883 | NULL | NULL | | BTREE | | | -| ProbeSetFreeze | 0 | Name | 1 | Name | A | 883 | NULL | NULL | YES | BTREE | | | -| ProbeSetFreeze | 1 | NameIndex | 1 | Name2 | A | 883 | NULL | NULL | | BTREE | | | -| ProbeSetFreeze | 1 | ShortName | 1 | ShortName | A | 883 | NULL | NULL | | BTREE | | | -| ProbeSetFreeze | 1 | ProbeFreezeId | 1 | ProbeFreezeId | A | 441 | NULL | NULL | | BTREE | | | -| ProbeSetFreeze | 1 | conf_and_public | 1 | confidentiality | A | 3 | NULL | NULL | | BTREE | | | -| ProbeSetFreeze | 1 | conf_and_public | 2 | public | A | 4 | NULL | NULL | | BTREE | | | +|----------------+------------+-----------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------| +| ProbeSetFreeze | 0 | PRIMARY | 1 | Id | A | 883 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 0 | FullName | 1 | FullName | A | 883 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 0 | Name | 1 | Name | A | 883 | NULL | NULL | YES | BTREE | | | +| ProbeSetFreeze | 1 | NameIndex | 1 | Name2 | A | 883 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 1 | ShortName | 1 | ShortName | A | 883 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 1 | ProbeFreezeId | 1 | ProbeFreezeId | A | 441 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 1 | conf_and_public | 1 | confidentiality | A | 3 | NULL | NULL | | BTREE | | | +| ProbeSetFreeze | 1 | conf_and_public | 2 | public | A | 4 | NULL | NULL | | BTREE | | | : 8 rows in set (0.00 sec) *** ProbeSet @@ -1456,23 +1456,23 @@ Tux01 has less indexes than P2(!): : MariaDB [db_webqtl]> show indexes from ProbeSetXRef ; | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | -+--------------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ -| ProbeSetXRef | 0 | ProbeSetId | 1 | ProbeSetFreezeId | A | 885 | NULL | NULL | | BTREE | | | -| ProbeSetXRef | 0 | ProbeSetId | 2 | ProbeSetId | A | 47713039 | NULL | NULL | | BTREE | | | -| ProbeSetXRef | 0 | DataId_IDX | 1 | DataId | A | 47713039 | NULL | NULL | | BTREE | | | -| ProbeSetXRef | 1 | Locus_IDX | 1 | Locus | A | 15904346 | NULL | NULL | YES | BTREE | | | +|--------------+------------+------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------| +| ProbeSetXRef | 0 | ProbeSetId | 1 | ProbeSetFreezeId | A | 885 | NULL | NULL | | BTREE | | | +| ProbeSetXRef | 0 | ProbeSetId | 2 | ProbeSetId | A | 47713039 | NULL | NULL | | BTREE | | | +| ProbeSetXRef | 0 | DataId_IDX | 1 | DataId | A | 47713039 | NULL | NULL | | BTREE | | | +| ProbeSetXRef | 1 | Locus_IDX | 1 | Locus | A | 15904346 | NULL | NULL | YES | BTREE | | | : 4 rows in set (0.000 sec) : MariaDB [db_webqtl]> show indexes from ProbeSetXRef ; | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | -+--------------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ -| ProbeSetXRef | 0 | ProbeSetId | 1 | ProbeSetFreezeId | A | 856 | NULL | NULL | | BTREE | | | -| ProbeSetXRef | 0 | ProbeSetId | 2 | ProbeSetId | A | 46412145 | NULL | NULL | | BTREE | | | -| ProbeSetXRef | 0 | DataId_IDX | 1 | DataId | A | 46412145 | NULL | NULL | | BTREE | | | -| ProbeSetXRef | 1 | ProbeSetId1 | 1 | ProbeSetId | A | 5156905 | NULL | NULL | | BTREE | | | -| ProbeSetXRef | 1 | Locus | 1 | Locus | A | 23206072 | NULL | NULL | YES | BTREE | | | +|--------------+------------+-------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------| +| ProbeSetXRef | 0 | ProbeSetId | 1 | ProbeSetFreezeId | A | 856 | NULL | NULL | | BTREE | | | +| ProbeSetXRef | 0 | ProbeSetId | 2 | ProbeSetId | A | 46412145 | NULL | NULL | | BTREE | | | +| ProbeSetXRef | 0 | DataId_IDX | 1 | DataId | A | 46412145 | NULL | NULL | | BTREE | | | +| ProbeSetXRef | 1 | ProbeSetId1 | 1 | ProbeSetId | A | 5156905 | NULL | NULL | | BTREE | | | +| ProbeSetXRef | 1 | Locus | 1 | Locus | A | 23206072 | NULL | NULL | YES | BTREE | | | : 5 rows in set (0.00 sec) ** Check storage -- cgit v1.2.3 From 45fc1da136b78bac906aad013686a9530f68bd5e Mon Sep 17 00:00:00 2001 From: BonfaceKilz Date: Tue, 11 May 2021 10:33:49 +0300 Subject: doc: docker-container: Default to python3-genenetwork2 for examples --- doc/docker-container.org | 6 +++--- 1 file changed, 3 insertions(+), 3 deletions(-) (limited to 'doc') diff --git a/doc/docker-container.org b/doc/docker-container.org index ec91824a..c894c4b5 100644 --- a/doc/docker-container.org +++ b/doc/docker-container.org @@ -59,11 +59,11 @@ RUN tar -xzf /tmp/gn2.tar.gz -C / && rm -f /tmp/gn2.tar.gz && \ Build the image(Note the fullstop at the end): -: sudo docker build -t python2-genenetwork2:latest -f Dockerfile . +: sudo docker build -t python3-genenetwork2:latest -f Dockerfile . To load the image interactively you've just created: -: docker run -ti "python2-genenetwork2:latest" bash +: docker run -ti "python3-genenetwork2:latest" bash Assuming you have a docker instance running, you could always run commands in it e.g: @@ -78,7 +78,7 @@ CI environment using Github Actions. To push to dockerhub, first get the image name by running =docker images=. Push to dockerhub using a command similar to: -: docker push bonfacekilz/python2-genenetwork2:latest +: docker push bonfacekilz/python3-genenetwork2:latest Right now, we have 2 images on DockerHub: -- cgit v1.2.3 From c22fdbd6a128ab9c9ddfc14299115a40cec37f07 Mon Sep 17 00:00:00 2001 From: BonfaceKilz Date: Wed, 26 May 2021 18:33:27 +0300 Subject: doc: docker-container: Rename python3-genenetwork2 to genenetwork2 See: https://is.gd/gHJvhe --- doc/docker-container.org | 12 ++++++------ 1 file changed, 6 insertions(+), 6 deletions(-) (limited to 'doc') diff --git a/doc/docker-container.org b/doc/docker-container.org index c894c4b5..ef0d71fc 100644 --- a/doc/docker-container.org +++ b/doc/docker-container.org @@ -32,13 +32,13 @@ First create the gn2 tar archive by running: env GUIX_PACKAGE_PATH="/home/bonface/projects/guix-bioinformatics:/home/bonface/projects/guix-past/modules" \ ./pre-inst-env guix pack --no-grafts\ -S /gn2-profile=/ \ - screen genenetwork2 + screen python2-genenetwork2 # For the Python 3 version: env GUIX_PACKAGE_PATH="/home/bonface/projects/guix-bioinformatics:/home/bonface/projects/guix-past/modules" \ ./pre-inst-env guix pack --no-grafts\ -S /gn2-profile=/ \ - screen python3-genenetwork2 + screen genenetwork2 #+end_src The output will look something similar to: @@ -59,16 +59,16 @@ RUN tar -xzf /tmp/gn2.tar.gz -C / && rm -f /tmp/gn2.tar.gz && \ Build the image(Note the fullstop at the end): -: sudo docker build -t python3-genenetwork2:latest -f Dockerfile . +: sudo docker build -t genenetwork2:latest -f Dockerfile . To load the image interactively you've just created: -: docker run -ti "python3-genenetwork2:latest" bash +: docker run -ti "genenetwork2:latest" bash Assuming you have a docker instance running, you could always run commands in it e.g: -: docker run "python2-genenetwork2:latest" python --version +: docker run "genenetwork2:latest" python --version * Pushing to DockerHub @@ -78,7 +78,7 @@ CI environment using Github Actions. To push to dockerhub, first get the image name by running =docker images=. Push to dockerhub using a command similar to: -: docker push bonfacekilz/python3-genenetwork2:latest +: docker push bonfacekilz/genenetwork2:latest Right now, we have 2 images on DockerHub: -- cgit v1.2.3