Project

General

Profile

Bug #6657

Missing index on tig_ma_jids and lot's of select executions

Added by Wojciech Kapcia TigaseTeam about 1 year ago. Updated about 1 year ago.

Status:
Closed
Priority:
Critical
Target version:
-
Start date:
Due date:
% Done:

100%

Estimated time:
Source Code Disclaimer:

Description

It looks like there is a missing index on tig_ma_jids conflicted with multiples select calls:

During this time the below query runs 4,548 times. It also looks like it scans 692,582 rows in each run.

An accumulation of such SELECT statements can have an effect on CPU usage.

It is also show in the results of - mysql> SHOW FULL PROCESSLIST;

Are you aware of this query? And is there an index on table "tig_ma_jids" on column "jid"?

select jid, jid_id from tig_ma_jids where jid = 'xmppb…@…' or jid = 'xmpp…@…';

User@Host: tigase[tigase] @ [10.3.10.175] Id: 17723

Query_time: 0.930243 Lock_time: 0.000056 Rows_sent: 1 Rows_examined: 692581


Related issues

Related to Tigase XMPP Server - Task #6665: Release a new version 7.1.3Closed2018-02-02

Associated revisions

Revision 190b9977 (diff)
Added by Andrzej Wójcik IoT 1 CloudTigaseTeam about 1 year ago

#6657: missing index on tig_ma_jids

Revision d76409ad (diff)
Added by Wojciech Kapcia TigaseTeam about 1 year ago

#6657 update dependency version

History

#1 Updated by Wojciech Kapcia TigaseTeam about 1 year ago

  • Description updated (diff)

#3 Updated by Andrzej Wójcik IoT 1 CloudTigaseTeam about 1 year ago

  • Status changed from New to In QA
  • Assignee changed from Andrzej Wójcik to Wojciech Kapcia
  • % Done changed from 0 to 100

I've added a code responsible for adding this missing index. I've verified that fix add the index and that index is used by MySQL during execution of this query.

Below is result of execution of following explain in JSON.

explain select jid, jid_id from tig_ma_jids where jid = 'admin@localhost';
[
  {
    "id": "1",
    "select_type": "SIMPLE",
    "table": "tig_ma_jids",
    "partitions": null,
    "type": "ref",
    "possible_keys": "tig_ma_jids_jid_index",
    "key": "tig_ma_jids_jid_index",
    "key_len": "1023",
    "ref": "const",
    "rows": "1",
    "filtered": 100,
    "Extra": "Using where"
  }
]

I've verified SQL schemas for version 2.0.0, but there is no need for an index on jid field as for MySQL jid_id is looked up using newly introduced jid_sha1 field which already has an index.

#4 Updated by Wojciech Kapcia TigaseTeam about 1 year ago

%andrzej.wojcik shouldn't this index be added to other databases as well (MS SQL Server, Postgresql)?

#5 Updated by Andrzej Wójcik IoT 1 CloudTigaseTeam about 1 year ago

Other DB schemas already contain this index. Only MySQL schema was missing this index.

#6 Updated by Wojciech Kapcia TigaseTeam about 1 year ago

  • Related to Task #6665: Release a new version 7.1.3 added

#7 Updated by Wojciech Kapcia TigaseTeam about 1 year ago

  • Status changed from In QA to Closed

Also available in: Atom PDF