Wojciech Kapcia (Tigase) opened 3 years ago
-
Name Previous Value Current Value Iterations
empty tigase-server-8.4.0
-
Name Previous Value Current Value Iterations
tigase-server-8.4.0
tigase-server-8.5.0
Version
tigase-server-8.4.0
tigase-server-8.5.0
-
Previous Value Current Value JDBCRepository NID subquery doesn't use index making the call slow
Flatten UserData (tig_nodes / tig_pairs) schema to avoide performance issues (JDBCRepository NID subquery doesn't use index making the call slow)
-
-
-
Name Previous Value Current Value Version
tigase-server-8.5.0
8.5.0
-
-
-
Previous Value Current Value Flatten UserData (tig_nodes / tig_pairs) schema to avoide performance issues (JDBCRepository NID subquery doesn't use index making the call slow)
Improve JDBCRepository UserRepository performance when accessing multi-level data (JDBCRepository NID subquery doesn't use index making the call slow)
-
-
-
Changed the implementation to explicitly use
INNER JOINSwhen generating query to get node ID.Original query in MySQL where not using indexes most of the time that could result in rather suboptimal performance on bigger repositories:
mysql> explain select nid as nid6, node as node6 -> from tig_nodes nodes6, -> (select nid as nid5, node as node5 -> from tig_nodes nodes5, -> (select nid as nid4, node as node4 -> from tig_nodes nodes4, -> (select nid as nid3, node as node3 -> from tig_nodes nodes3, -> (select nid as nid2, node as node2 -> from tig_nodes nodes2, -> (select nid as nid1 -> from tig_nodes -> where (uid = 21) AND (parent_nid is null) AND (node = 'root')) nodes1 -> where (parent_nid = nid1) -> AND (node = 'some')) nodes2 -> where (parent_nid = nid2) -> AND (node = 'deep')) nodes3 -> where (parent_nid = nid3) -> AND (node = 'node')) nodes4 -> where (parent_nid = nid4) -> AND (node = 'privacy')) nodes5 -> where (parent_nid = nid5) -> AND (node = 'blocked'); +----+-------------+-----------+------------+--------+-----------------------------------+---------+---------+-------------------------------------------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+--------+-----------------------------------+---------+---------+-------------------------------------------+------+----------+--------------------------+ | 1 | SIMPLE | nodes4 | NULL | ref | PRIMARY,tnode,node,parent_nid | node | 767 | const | 1 | 100.00 | Using where | | 1 | SIMPLE | nodes3 | NULL | eq_ref | PRIMARY,tnode,node,parent_nid | PRIMARY | 8 | tigasedb_inst_qa_b11330.nodes4.parent_nid | 1 | 5.00 | Using where | | 1 | SIMPLE | nodes2 | NULL | eq_ref | PRIMARY,tnode,node,parent_nid | PRIMARY | 8 | tigasedb_inst_qa_b11330.nodes3.parent_nid | 1 | 5.00 | Using where | | 1 | SIMPLE | tig_nodes | NULL | eq_ref | PRIMARY,tnode,node,uid,parent_nid | PRIMARY | 8 | tigasedb_inst_qa_b11330.nodes2.parent_nid | 1 | 5.00 | Using where | | 1 | SIMPLE | nodes5 | NULL | ref | PRIMARY,tnode,node,parent_nid | tnode | 9 | tigasedb_inst_qa_b11330.nodes4.nid | 4 | 27.80 | Using where; Using index | | 1 | SIMPLE | nodes6 | NULL | ref | tnode,node,parent_nid | tnode | 9 | tigasedb_inst_qa_b11330.nodes5.nid | 4 | 28.06 | Using where; Using index | +----+-------------+-----------+------------+--------+-----------------------------------+---------+---------+-------------------------------------------+------+----------+--------------------------+New with INNER JOIN always uses index:
mysql> explain SELECT t6.nid -> FROM tig_nodes t1 -> INNER JOIN tig_nodes t2 ON t2.parent_nid = t1.nid AND t2.uid = 21 AND t2.node = 'some' -> INNER JOIN tig_nodes t3 ON t3.parent_nid = t2.nid AND t3.uid = 21 AND t3.node = 'deep' -> INNER JOIN tig_nodes t4 ON t4.parent_nid = t3.nid AND t4.uid = 21 AND t4.node = 'node' -> INNER JOIN tig_nodes t5 ON t5.parent_nid = t4.nid AND t5.uid = 21 AND t5.node = 'privacy' -> INNER JOIN tig_nodes t6 ON t6.parent_nid = t5.nid AND t6.uid = 21 AND t6.node = 'blocked' -> WHERE (t1.uid = 21) -> AND (t1.parent_nid IS NULL) -> AND (t1.node = 'root'); +----+-------------+-------+------------+--------+-----------------------------------+-------+---------+--------------------------------------------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+-----------------------------------+-------+---------+--------------------------------------------+------+----------+--------------------------+ | 1 | SIMPLE | t1 | NULL | ref | PRIMARY,tnode,node,uid,parent_nid | tnode | 784 | const,const,const | 1 | 100.00 | Using where; Using index | | 1 | SIMPLE | t2 | NULL | eq_ref | PRIMARY,tnode,node,uid,parent_nid | tnode | 784 | tigasedb_inst_qa_b11330.t1.nid,const,const | 1 | 100.00 | Using index | | 1 | SIMPLE | t3 | NULL | eq_ref | PRIMARY,tnode,node,uid,parent_nid | tnode | 784 | tigasedb_inst_qa_b11330.t2.nid,const,const | 1 | 100.00 | Using index | | 1 | SIMPLE | t4 | NULL | eq_ref | PRIMARY,tnode,node,uid,parent_nid | tnode | 784 | tigasedb_inst_qa_b11330.t3.nid,const,const | 1 | 100.00 | Using index | | 1 | SIMPLE | t5 | NULL | eq_ref | PRIMARY,tnode,node,uid,parent_nid | tnode | 784 | tigasedb_inst_qa_b11330.t4.nid,const,const | 1 | 100.00 | Using index | | 1 | SIMPLE | t6 | NULL | eq_ref | tnode,node,uid,parent_nid | tnode | 784 | tigasedb_inst_qa_b11330.t5.nid,const,const | 1 | 100.00 | Using index | +----+-------------+-------+------------+--------+-----------------------------------+-------+---------+--------------------------------------------+------+----------+--------------------------+ 6 rows in set, 1 warning (0.003 sec) -
Previous Value Current Value Open
Closed
| Type |
Performance
|
| Priority |
Normal
|
| Assignee | |
| Version |
8.5.0
|
Iterations
-
tigase-server-8.5.0 Open
Issue Votes (0)
Watchers (3)
While tree-like structure of UserData gives quite a lot of flexibility it's not utilised but entails performance issues without much benefit.
Culprit:
tigase.db.jdbc.JDBCRepository#buildNodeQueryhttps://dev.mysql.com/doc/refman/8.0/en/subquery-optimization.html