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


From a7f2e8dea0a84a70aec29159a9ae35bd55ba047d Mon Sep 17 00:00:00 2001
From: BonfaceKilz
Date: Thu, 17 Jun 2021 08:52:17 +0300
Subject: doc: Add results of investing rpy2 blocking issue in gn2

---
 doc/rpy2-performance.org | 170 +++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 170 insertions(+)
 create mode 100644 doc/rpy2-performance.org

(limited to 'doc')

diff --git a/doc/rpy2-performance.org b/doc/rpy2-performance.org
new file mode 100644
index 00000000..29b99ba0
--- /dev/null
+++ b/doc/rpy2-performance.org
@@ -0,0 +1,170 @@
+* Python-Rpy2 performance issues with genenetwork2
+
+At one point, genenetwork2 was down. A possible cause was that it
+wrote into the log file in an infinite loop due to rpy2, so a solution
+was to empty it. Currently, as a work around, rpy2 is disabled by
+removing it's imports. This affects WGCNA/ CTL imports and commenting
+out Biweight Midcorrelation option in the trait page. See:
+
+- [[https://github.com/genenetwork/genenetwork2/commit/1baf5f7611909c651483208184c5fbf7d4a7a088][1baf5f7]]
+- [[https://github.com/genenetwork/genenetwork2/commit/afee4d625248565857df98d3510f680ae6204864][afee4d6]]
+- [[https://github.com/genenetwork/genenetwork2/commit/c458bf0ad731e5e5fd9cbd0686936b3a441bae63][c458bf0]]
+- [[https://github.com/genenetwork/genenetwork2/commit/d31f3f763471b19559ca74e73b52b3cb5e7153ce][d31f3f7]]
+
+** Reproducing the problem
+
+I went back to commit #b8408cea. With regards to logs, I never
+experienced any log issue. Perhaps it's because of how I start my
+server:
+
+: env SERVER_PORT=5004 TMPDIR=/home/bonface/tmp WEBSERVER_MODE=DEBUG LOG_LEVEL=DEBUG GENENETWORK_FILES=/home/bonface/data/genotype_files/ GN2_PROFILE=/home/bonface/opt/python3-genenetwork2 ./scripts/run_debug.sh
+
+However, when loading the homepage, I occasionally ran into this trace:
+
+#+begin_export ascii
+DEBUG:wqflask.views:.check_access_permissions: @app.before_request check_access_permissions
+DEBUG:wqflask.views:.shutdown_session: remove db_session
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Error: ignoring SIGPIPE signal
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: In addition:
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Warning messages:
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: 1:
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: In (function (package, help, pos = 2, lib.loc = NULL, character.only = FALSE,  :
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:  library '/home/bonface/R/x86_64-unknown-linux-gnu-library/4.0' contains no packages
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: 2:
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: In (function (package, help, pos = 2, lib.loc = NULL, character.only = FALSE,  :
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:  library '/home/bonface/R/x86_64-unknown-linux-gnu-library/4.0' contains no packages
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Fatal error: unable to initialize the JIT
+
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:
+ *** caught segfault ***
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: address (nil), cause 'memory not mapped'
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:
+Possible actions:
+1: abort (with core dump, if enabled)
+2: normal R exit
+3: exit R without saving workspace
+4: exit R saving workspace
+
+Selection:
+
+#+end_export
+
+This blocks the flask service. Seems to be related to: [[https://github.com/rpy2/rpy2/issues/769][rpy2-issue#769]]
+and [[https://github.com/rpy2/rpy2/issues/809][rpy2-issue#809]]. I tried to reproduce this problem using some endpoint:
+
+#+begin_src python
+@app.route("/test")
+  def test():
+      from rpy2 import robjects as ro
+      from rpy2 import rinterface
+      from threading import Thread
+
+      def rpy2_init_simple():
+          rinterface.initr_simple()
+
+      thread = Thread(target=rpy2_init_simple)
+      thread.start()
+      return "This is a test after importing rpy2"
+#+end_src
+
+which generates this trace:
+
+#+begin_export ascii
+/home/bonface/opt/python3-genenetwork2/lib/python3.8/site-packages/rpy2/rinterface.py:955: UserWarning: R is not initialized by the main thread.
+              Its taking over SIGINT cannot be reversed here, and as a
+              consequence the embedded R cannot be interrupted with Ctrl-C.
+              Consider (re)setting the signal handler of your choice from
+              the main thread.
+warnings.warn(
+DEBUG:wqflask.views:.shutdown_session: remove db_session
+
+#+end_export
+
+Modifying the endpoint to:
+
+#+begin_src python
+@app.route("/test")
+  def test():
+      import wqflask.correlation.show_corr_results
+      import wqflask.ctl.ctl_analysis
+      import time
+      from wqflask.correlation.correlation_functions import cal_zero_order_corr_for_tiss
+
+      print("Sleeping for 3 seconds")
+      time.sleep(3)
+      return "This is a test after importing rpy2"
+#+end_src
+
+and refreshing the page a couple of times, I get:
+
+#+begin_export ascii
+DEBUG:wqflask.views:.check_access_permissions: @app.before_request check_access_
+permissions
+Sleeping for 3 seconds
+DEBUG:wqflask.views:.shutdown_session: remove db_session
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Error: ignoring SIGPI
+PE signal
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: In addition:
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Warning messages:
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: 1:
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: In (function (package
+, help, pos = 2, lib.loc = NULL, character.only = FALSE,  :
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:  library '/home/bonfa
+ce/R/x86_64-unknown-linux-gnu-library/4.0' contains no packages
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: 2:
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: In (function (package
+, help, pos = 2, lib.loc = NULL, character.only = FALSE,  :
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:  library '/home/bonfa
+ce/R/x86_64-unknown-linux-gnu-library/4.0' contains no packages
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:
+*** caught segfault ***
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: address (nil), cause
+'memory not mapped'
+
+WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:
+Possible actions:
+1: abort (with core dump, if enabled)
+2: normal R exit
+3: exit R without saving workspace
+4: exit R saving workspace
+
+Selection: [2021-06-16 13:11:00 +0300] [18657] [INFO] Handling signal: winch
+[2021-06-16 13:11:00 +0300] [18657] [INFO] Handling signal: winch
+[2021-06-16 13:13:02 +0300] [18657] [INFO] Handling signal: winch
+#+end_export
+
+However, this seems to be non-deterministic, in the sense that I can't really pin what causes the above. I've tried to write a Locust Test that simulates users hitting that endpoint:
+
+#+begin_src python
+"""Load test a single trait page"""
+from locust import HttpUser, task, between
+
+
+  class LoadTest(HttpUser):
+      wait_time = between(1, 2.5)
+
+      @task
+      def fetch_trait(self):
+          """Fetch a single trait"""
+          self.client.get("/test")
+#+end_src
-- 
cgit v1.2.3


From f7027d4c87786da0d505e0e8e1da23d6e55f6d80 Mon Sep 17 00:00:00 2001
From: BonfaceKilz
Date: Thu, 17 Jun 2021 10:09:38 +0300
Subject: doc: rpy2-performance: Replace begin_export with begin_src

---
 doc/rpy2-performance.org | 19 +++++++++++--------
 1 file changed, 11 insertions(+), 8 deletions(-)

(limited to 'doc')

diff --git a/doc/rpy2-performance.org b/doc/rpy2-performance.org
index 29b99ba0..ddcac81a 100644
--- a/doc/rpy2-performance.org
+++ b/doc/rpy2-performance.org
@@ -21,7 +21,7 @@ server:
 
 However, when loading the homepage, I occasionally ran into this trace:
 
-#+begin_export ascii
+#+begin_src
 DEBUG:wqflask.views:.check_access_permissions: @app.before_request check_access_permissions
 DEBUG:wqflask.views:.shutdown_session: remove db_session
 WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Error: ignoring SIGPIPE signal
@@ -58,7 +58,7 @@ Possible actions:
 
 Selection:
 
-#+end_export
+#+end_src
 
 This blocks the flask service. Seems to be related to: [[https://github.com/rpy2/rpy2/issues/769][rpy2-issue#769]]
 and [[https://github.com/rpy2/rpy2/issues/809][rpy2-issue#809]]. I tried to reproduce this problem using some endpoint:
@@ -80,7 +80,7 @@ and [[https://github.com/rpy2/rpy2/issues/809][rpy2-issue#809]]. I tried to repr
 
 which generates this trace:
 
-#+begin_export ascii
+#+begin_src
 /home/bonface/opt/python3-genenetwork2/lib/python3.8/site-packages/rpy2/rinterface.py:955: UserWarning: R is not initialized by the main thread.
               Its taking over SIGINT cannot be reversed here, and as a
               consequence the embedded R cannot be interrupted with Ctrl-C.
@@ -89,7 +89,7 @@ which generates this trace:
 warnings.warn(
 DEBUG:wqflask.views:.shutdown_session: remove db_session
 
-#+end_export
+#+end_src
 
 Modifying the endpoint to:
 
@@ -108,7 +108,7 @@ Modifying the endpoint to:
 
 and refreshing the page a couple of times, I get:
 
-#+begin_export ascii
+#+begin_src
 DEBUG:wqflask.views:.check_access_permissions: @app.before_request check_access_
 permissions
 Sleeping for 3 seconds
@@ -136,7 +136,7 @@ WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:  library '/home/bonfa
 ce/R/x86_64-unknown-linux-gnu-library/4.0' contains no packages
 
 WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:
-*** caught segfault ***
+\*** caught segfault ***
 
 WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: address (nil), cause
 'memory not mapped'
@@ -151,9 +151,11 @@ Possible actions:
 Selection: [2021-06-16 13:11:00 +0300] [18657] [INFO] Handling signal: winch
 [2021-06-16 13:11:00 +0300] [18657] [INFO] Handling signal: winch
 [2021-06-16 13:13:02 +0300] [18657] [INFO] Handling signal: winch
-#+end_export
+#+end_src
 
-However, this seems to be non-deterministic, in the sense that I can't really pin what causes the above. I've tried to write a Locust Test that simulates users hitting that endpoint:
+However, this seems to be non-deterministic, in the sense that I can't
+really pin what causes the above. I've tried to write a Locust Test
+that simulates users hitting that endpoint:
 
 #+begin_src python
 """Load test a single trait page"""
@@ -168,3 +170,4 @@ from locust import HttpUser, task, between
           """Fetch a single trait"""
           self.client.get("/test")
 #+end_src
+
-- 
cgit v1.2.3


From a992e2038424c2acdf11078356204847d128c8c6 Mon Sep 17 00:00:00 2001
From: BonfaceKilz
Date: Thu, 17 Jun 2021 10:11:53 +0300
Subject: docs: rpy2-performance: Indicate version of python-rpy2 being used

---
 doc/rpy2-performance.org | 8 ++++----
 1 file changed, 4 insertions(+), 4 deletions(-)

(limited to 'doc')

diff --git a/doc/rpy2-performance.org b/doc/rpy2-performance.org
index ddcac81a..177c427e 100644
--- a/doc/rpy2-performance.org
+++ b/doc/rpy2-performance.org
@@ -1,10 +1,10 @@
 * Python-Rpy2 performance issues with genenetwork2
 
 At one point, genenetwork2 was down. A possible cause was that it
-wrote into the log file in an infinite loop due to rpy2, so a solution
-was to empty it. Currently, as a work around, rpy2 is disabled by
-removing it's imports. This affects WGCNA/ CTL imports and commenting
-out Biweight Midcorrelation option in the trait page. See:
+wrote into the log file in an infinite loop due to rpy2(v3.4.4), so a
+solution was to empty it. Currently, as a work around, rpy2 is
+disabled by removing it's imports. This affects WGCNA/ CTL imports and
+commenting out Biweight Midcorrelation option in the trait page. See:
 
 - [[https://github.com/genenetwork/genenetwork2/commit/1baf5f7611909c651483208184c5fbf7d4a7a088][1baf5f7]]
 - [[https://github.com/genenetwork/genenetwork2/commit/afee4d625248565857df98d3510f680ae6204864][afee4d6]]
-- 
cgit v1.2.3


From a5981b8e71380b171b210d55da58e5037b455a6c Mon Sep 17 00:00:00 2001
From: BonfaceKilz
Date: Thu, 17 Jun 2021 10:20:39 +0300
Subject: doc: rpy2-performance: Add a section exploring possible solutions

---
 doc/rpy2-performance.org | 9 +++++++++
 1 file changed, 9 insertions(+)

(limited to 'doc')

diff --git a/doc/rpy2-performance.org b/doc/rpy2-performance.org
index 177c427e..8f917ca0 100644
--- a/doc/rpy2-performance.org
+++ b/doc/rpy2-performance.org
@@ -171,3 +171,12 @@ from locust import HttpUser, task, between
           self.client.get("/test")
 #+end_src
 
+
+** A possible solution
+
+From this [[https://github.com/rpy2/rpy2/issues/809#issuecomment-845923975][comment]], a possible reason for the above traces, is that
+from Flask's end, a [[https://tldp.org/LDP/lpg/node20.html][SIGPIPE]] is somehow generated by our Python
+code. However, at this particular point, the R thread just happens to
+be running, and R can't handle this correctly. This seems to have been
+fixed in this [[https://github.com/rpy2/rpy2/pull/810][PR]] with a this [[https://github.com/rpy2/rpy2/issues/809#issuecomment-851618215][explanation]]. On our end, to have these
+changes, we have to update our python-rpy2 version.
-- 
cgit v1.2.3


From d6937e74b85a4fc44153530520774836eed60fe6 Mon Sep 17 00:00:00 2001
From: zsloan
Date: Mon, 28 Jun 2021 18:31:29 +0000
Subject: Added doc for creating guix profile, written by Bonface

---
 doc/guix_profile_setup.org | 39 +++++++++++++++++++++++++++++++++++++++
 1 file changed, 39 insertions(+)
 create mode 100644 doc/guix_profile_setup.org

(limited to 'doc')

diff --git a/doc/guix_profile_setup.org b/doc/guix_profile_setup.org
new file mode 100644
index 00000000..c397377c
--- /dev/null
+++ b/doc/guix_profile_setup.org
@@ -0,0 +1,39 @@
+* Setting up GUIX profile for GN
+
+First create a guix profile with the latest packages:
+
+: ~/opt/guix/bin/guix pull
+
+This will create a profile with the latest packages under`~/.config/guix/current`
+
+Now you have the latest guix. Check: `$HOME/.config/guix/current/bin/guix --version`
+
+At this point, it's worth mentioning that installing
+python3-genenetwork using `$HOME/.config/guix/current/bin/guix` should
+work; but let's use the dev version(since that may come handy in
+time), and it's a nice thing to know.
+
+Next, we ensure that the appropriate GUILE<sub>PATHS</sub> are set:
+
+: export GUILE_LOAD_PATH=$HOME/.config/guix/current/share/guile/site/3.0/
+: export GUILE_LOAD_COMPILED_PATH=$HOME/.config/guix/current/lib/guile/3.0/site-ccache/ 
+
+Get into the container:
+
+: $HOME/.config/guix/current/bin/guix environment -C guix --ad-hoc bash gcc-toolchain
+: ./bootstrap
+: ./configure --localstatedir=/var --sysconfdir=/etc
+
+Check that everything works:
+
+: make check
+
+Clean up and build:
+
+: make clean-go
+: make -j 4
+: exit
+
+Install Python3 (substitute paths when necessary):
+
+: env GUIX_PACKAGE_PATH='/home/zas1024/guix-bioinformatics:/home/zas1024/guix-past/modules' $HOME/.config/guix/current/bin/guix install python3-genenetwork2 -p ~/opt/python3-genenetwork2 --substitute-urls="http://guix.genenetwork.org https://berlin.guixsd.org https://ci.guix.gnu.org https://mirror.hydra.gnu.org"
-- 
cgit v1.2.3


From fe345c9f2e99be748511f6889420d6560a553a37 Mon Sep 17 00:00:00 2001
From: Pjotr Prins
Date: Tue, 24 Aug 2021 09:55:05 +0200
Subject: README: install with guix profile

---
 doc/README.org | 73 ++++++++++++++++++++++++++++++++++++++++++++++++++--------
 1 file changed, 63 insertions(+), 10 deletions(-)

(limited to 'doc')

diff --git a/doc/README.org b/doc/README.org
index 43c92e3c..fb1781aa 100644
--- a/doc/README.org
+++ b/doc/README.org
@@ -2,7 +2,8 @@
 
 * Table of Contents                                                     :TOC:
  - [[#introduction][Introduction]]
- - [[#install][Install]]
+ - [[#installing-guix-packages][Installing Guix packages]]
+ - [[#creating-a-gnu-guix-profile][Creating a GNU Guix profile]]
  - [[#running-gn2][Running GN2]]
  - [[#run-gn-proxy][Run gn-proxy]]
  - [[#run-redis][Run Redis]]
@@ -37,27 +38,79 @@ tree. Current supported versions can be found as the SHA values of
 For a full view of runtime dependencies as defined by GNU Guix, see
 an example of the [[#gn2-dependency-graph][GN2 Dependency Graph]].
 
-* Install
+* Installing Guix packages
 
 Make sure to install GNU Guix using the binary download instructions
 on the main website. Follow the instructions on
 [[GUIX-Reproducible-from-source.org]] to download pre-built binaries. Note
-the download amounts to several GBs of data.
+the download amounts to several GBs of data. Debian-derived distros
+may support
+
+: apt-get install guix
+
+* Creating a GNU Guix profile
+
+We run a GNU Guix channel with packages at [[https://git.genenetwork.org/guix-bioinformatics/guix-bioinformatics][guix-bioinformatics]]. The
+README has instructions for hosting a channel, but typically we use
+the GUIX_PACKAGE_PATH instead. First upgrade to a recent guix with
+
+: mkdir ~/opt
+: guix pull -p ~/opt/guix-pull
+
+It should upgrade (ignore the locales warnings).  You can optionally
+specify the specific git checkout of guix with
+
+: guix pull -p ~/opt/guix-pull --commit=f04883d
+
+which is useful when you ned to roll back to an earlier version
+(sometimes our channel goes out of sync). Next, we install
+GeneNetwork2 with
+
+: source ~/opt/guix-pull/etc/profile
+: git clone https://git.genenetwork.org/guix-bioinformatics/guix-bioinformatics.git ~/guix-bioinformatics
+: cd ~/guix-bioinformatics
+: git pull
+: env GUIX_PACKAGE_PATH=$HOME/guix-bioinformatics guix package -i genenetwork2 -p ~/opt/genenetwork2
+
+you probably also need guix-past (the upstream channel for older packages):
+
+: git clone https://gitlab.inria.fr/guix-hpc/guix-past.git ~/guix-past
+: cd ~/guix-past
+: git pull
+: env GUIX_PACKAGE_PATH=$HOME/guix-bioinformatics:$HOME/guix-past/modules ~/opt/guix-pull/bin/guix package -i genenetwork2 -p ~/opt/genenetwork2
+
+ignore the warnings. Guix should install the software without trying
+to build everything. If you system insists on building all packages,
+try the `--dry-run` switch and fix the [[https://guix.gnu.org/manual/en/html_node/Substitute-Server-Authorization.html][substitutes]]. You may add the
+`--substitute-urls="http://guix.genenetwork.org https://ci.guix.gnu.org https://mirror.hydra.gnu.org"` switch.
+
+The guix.genenetwork.org has most of our packages pre-built(!). To use
+it on your own machine the public key is
+
+#+begin_src scheme
+(public-key
+ (ecc
+  (curve Ed25519)
+  (q #E50F005E6DA2F85749B9AA62C8E86BB551CE2B541DC578C4DBE613B39EC9E750#)))
+#+end_src
+
+Once we have a GNU Guix profile, a running database (see below) and the file storage,
+we should be ready to fire up GeneNetwork:
 
 * Running GN2
 
-Default settings for GN2 are listed in a file called
-[[../etc/default_settings.py][default_settings.py]]. You can copy this file and pass it as a new
-parameter to the genenetwork2 command, e.g.
+Check out the source with git:
 
-: genenetwork2 mysettings.py
+: git clone git@github.com:genenetwork/genenetwork2.git
+: cd genenetwork2
 
-or you can set environment variables to override individual parameters, e.g.
+Run GN2 with above Guix profile
 
-: env SERVER_PORT=5004 SQL_URI=mysql://user:pwd@dbhostname/db_webqtl genenetwork2
+: export GN2_PROFILE=$HOME/opt/genenetwork2
+: env TMPDIR=$HOME/tmp WEBSERVER_MODE=DEBUG LOG_LEVEL=DEBUG SERVER_PORT=5002 GENENETWORK_FILES=/export/data/genenetwork/genotype_files SQL_URI=mysql://webqtlout:webqtlout@localhost/db_webqtl ./bin/genenetwork2 -gunicorn-dev
 
 the debug and logging switches can be particularly useful when
-developing GN2.
+developing GN2. Location and files are the current ones for Penguin2.
 
 * Run gn-proxy
 
-- 
cgit v1.2.3


From 8a8dcba497e8f4210329f42cd4b4e766d95a36fc Mon Sep 17 00:00:00 2001
From: Pjotr Prins
Date: Tue, 24 Aug 2021 10:54:01 +0200
Subject: README: on installing GN2

---
 doc/README.org | 27 +++++++++++++++++++++++++--
 1 file changed, 25 insertions(+), 2 deletions(-)

(limited to 'doc')

diff --git a/doc/README.org b/doc/README.org
index fb1781aa..1236016e 100644
--- a/doc/README.org
+++ b/doc/README.org
@@ -2,6 +2,7 @@
 
 * Table of Contents                                                     :TOC:
  - [[#introduction][Introduction]]
+ - [[#check-list][Check list]]
  - [[#installing-guix-packages][Installing Guix packages]]
  - [[#creating-a-gnu-guix-profile][Creating a GNU Guix profile]]
  - [[#running-gn2][Running GN2]]
@@ -38,6 +39,17 @@ tree. Current supported versions can be found as the SHA values of
 For a full view of runtime dependencies as defined by GNU Guix, see
 an example of the [[#gn2-dependency-graph][GN2 Dependency Graph]].
 
+* Check list
+
+To run GeneNetwork the following services need to function:
+
+1. [ ] GNU Guix with a guix profile for genenetwork2
+1. [ ] A path to the (static) genotype files
+1. [ ] Gn-proxy for authentication
+1. [ ] The genenetwork3 service
+1. [ ] Redis
+1. [ ] Mariadb
+
 * Installing Guix packages
 
 Make sure to install GNU Guix using the binary download instructions
@@ -107,15 +119,26 @@ Check out the source with git:
 Run GN2 with above Guix profile
 
 : export GN2_PROFILE=$HOME/opt/genenetwork2
-: env TMPDIR=$HOME/tmp WEBSERVER_MODE=DEBUG LOG_LEVEL=DEBUG SERVER_PORT=5002 GENENETWORK_FILES=/export/data/genenetwork/genotype_files SQL_URI=mysql://webqtlout:webqtlout@localhost/db_webqtl ./bin/genenetwork2 -gunicorn-dev
+: env TMPDIR=$HOME/tmp WEBSERVER_MODE=DEBUG LOG_LEVEL=DEBUG SERVER_PORT=5012 GENENETWORK_FILES=/export/data/genenetwork/genotype_files SQL_URI=mysql://webqtlout:webqtlout@localhost/db_webqtl ./bin/genenetwork2 etc/default_settings.py -gunicorn-dev
 
 the debug and logging switches can be particularly useful when
 developing GN2. Location and files are the current ones for Penguin2.
 
+It may be useful to tunnel the web server to your local browser with
+an ssh tunnel:
+
+If you want to test a service running on the server on a certain
+port (say 8202) use
+
+  ssh -L 8202:127.0.0.1:8202 -f -N myname@penguin2.genenetwork.org
+
+And browse on your local machine to http://localhost:8202/
+
 * Run gn-proxy
 
 GeneNetwork requires a separate gn-proxy server which handles
-authorisation and access control. For instructions see the [[https://github.com/genenetwork/gn-proxy][README]].
+authorisation and access control. For instructions see the
+[[https://github.com/genenetwork/gn-proxy][README]]. Note it may already be running on our servers!
 
 * Run Redis
 
-- 
cgit v1.2.3


From 7a15d24a6598f30801dd897ddc72d3773641e7bd Mon Sep 17 00:00:00 2001
From: BonfaceKilz
Date: Tue, 5 Oct 2021 11:32:17 +0300
Subject: doc: docker-container.org: Remove python2 gn2 docker set-up command

---
 doc/docker-container.org | 7 -------
 1 file changed, 7 deletions(-)

(limited to 'doc')

diff --git a/doc/docker-container.org b/doc/docker-container.org
index ef0d71fc..79b8272f 100644
--- a/doc/docker-container.org
+++ b/doc/docker-container.org
@@ -28,13 +28,6 @@ which will be added to a base mariaDB image.
 First create the gn2 tar archive by running:
 
 #+begin_src sh
-# For the Python 2 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 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=/ \
-- 
cgit v1.2.3


From ba98ef026544d4437e65a7bd248ff9591296b48e Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Wed, 13 Oct 2021 09:37:19 +0300
Subject: Add some documentation for generating heatmaps

Issue:
https://github.com/genenetwork/gn-gemtext-threads/blob/main/topics/gn1-migration-to-gn2/non-clustered-heatmaps-and-flipping.gmi

* Add some documentation on generating the heatmaps, that would be
  useful for the end user.
---
 doc/heatmap-generation.org              |  34 ++++++++++++++++++++++++++++++++
 doc/images/gn2_header_collections.png   | Bin 0 -> 7890 bytes
 doc/images/heatmap_form.png             | Bin 0 -> 9363 bytes
 doc/images/heatmap_with_hover_tools.png | Bin 0 -> 42578 bytes
 4 files changed, 34 insertions(+)
 create mode 100644 doc/heatmap-generation.org
 create mode 100644 doc/images/gn2_header_collections.png
 create mode 100644 doc/images/heatmap_form.png
 create mode 100644 doc/images/heatmap_with_hover_tools.png

(limited to 'doc')

diff --git a/doc/heatmap-generation.org b/doc/heatmap-generation.org
new file mode 100644
index 00000000..a697c70b
--- /dev/null
+++ b/doc/heatmap-generation.org
@@ -0,0 +1,34 @@
+#+STARTUP: inlineimages
+#+TITLE: Heatmap Generation
+#+AUTHOR: Muriithi Frederick Muriuki
+
+* Generating Heatmaps
+
+Like a lot of other features, the heatmap generation requires an existing collection. If none exists, see [[][Creating a new collection]] for how to create a new collection.
+
+Once you have a collection, you can navigate to the collections page by clicking on the "Collections" link in the header
+
+
+[[./images/gn2_header_collections.png]]
+
+From that page, pick the collection that you want to work with by clicking on its name on the collections table.
+
+That takes you to that collection's page, where you can select the data that you want to use to generate the heatmap.
+
+** Selecting Orientation
+
+Once you have selected the data, select the orientation of the heatmap you want generated. You do this by selecting either *"vertical"* or *"horizontal"* in the heatmaps form:
+
+[[./images/heatmap_form.png]]
+
+Once you have selected the orientation, click on the "Generate Heatmap" button as in the image above.
+
+The heatmap generation might take a while, but once it is done, an image shows up above the data table.
+
+** Downloading the PNG copy of the Heatmap
+
+Once the heatmap image is shown, hovering over it, displays some tools to interact with the image.
+
+To download, hover over the heatmap image, and click on the "Download plot as png" icon as shown.
+
+[[./images/heatmap_with_hover_tools.png]]
diff --git a/doc/images/gn2_header_collections.png b/doc/images/gn2_header_collections.png
new file mode 100644
index 00000000..ac23f9c1
Binary files /dev/null and b/doc/images/gn2_header_collections.png differ
diff --git a/doc/images/heatmap_form.png b/doc/images/heatmap_form.png
new file mode 100644
index 00000000..163fbb60
Binary files /dev/null and b/doc/images/heatmap_form.png differ
diff --git a/doc/images/heatmap_with_hover_tools.png b/doc/images/heatmap_with_hover_tools.png
new file mode 100644
index 00000000..4ab79f99
Binary files /dev/null and b/doc/images/heatmap_with_hover_tools.png differ
-- 
cgit v1.2.3