Uploaded image for project: 'Percona Server for MySQL'
  1. Percona Server for MySQL
  2. PS-6924

unexplained long statistics stage

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Done
    • Priority: Medium
    • Resolution: Invalid
    • Affects Version/s: 5.6.36-82.1
    • Fix Version/s: None
    • Component/s: TokuDB
    • Labels:
      None

      Description

      we have a query which looks like this

      select LOGID,OFFSET,PID,TDATE,TRTYPE,EXTRACTABLE,TID,NETTIME,LAYOUTVALS from TRANSACTIONS_2018 where ((PID=1 OR PID=2 OR PID=3 OR PID=4 OR PID=5 OR PID=6 OR PID=7 OR PID=8 .... for about 1000 PIDs) AND trtype in ('A','B','C','D','E')) and TDATE>='2017-01-01' and TDATE<='2020-01-23' order by pid,tdate,logid,offset;

      the query runs many times, each time for different PIDs
      usually the query will take about 100 ms but sometimes it can take 3 seconds,

      profiling shows that this time is spent in the statistics stage,
      it is my suspicion that this is because tokudb is running analyze (this is a very big table)

      mysql> show variables like '%analyze%';
      -------------------------------------------------------+

      Variable_name Value

      -------------------------------------------------------+

      tokudb_analyze_delete_fraction 1.000000
      tokudb_analyze_in_background OFF
      tokudb_analyze_mode TOKUDB_ANALYZE_STANDARD
      tokudb_analyze_throttle 0
      tokudb_analyze_time 5
      tokudb_auto_analyze 0

      -------------------------------------------------------+

       

      as you can see tokudb_auto_analyze is 0, so im not sure why this is happening
      either this is a bug or there is some parameter we are not tuning correctly

       

      this is the structure of the table

      CREATE TABLE `TRANSACTIONS_2018` (
      `LOGID` int(10) unsigned NOT NULL,
      `OFFSET` int(10) unsigned NOT NULL,
      `PID` int(10) unsigned NOT NULL,
      `TDATE` date NOT NULL,
      `NETTIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `DRUG` char(19) DEFAULT NULL,
      `TID` char(35) DEFAULT NULL,
      `TRTYPE` char(2) DEFAULT NULL,
      `EXTRACTABLE` tinyint(4) NOT NULL,
      `LAYOUTVALS` varchar(271) DEFAULT NULL,
      PRIMARY KEY (`PID`,`TDATE`,`LOGID`,`OFFSET`),
      KEY `TRANSACTIONS_2018_LOGID` (`LOGID`) USING BTREE,
      KEY `TRANSACTIONS_2018_TID` (`TID`) USING BTREE
      ) ENGINE=TokuDB DEFAULT CHARSET=latin1 ROW_FORMAT=TOKUDB_QUICKLZ

      this is the output of EXPLAIN FORMAT=JSON

      "query_block": {
      "select_id": 1,
      "ordering_operation": {
      "using_filesort": false,
      "table": {
      "table_name": "TRANSACTIONS_2018",
      "access_type": "range",
      "possible_keys": [
      "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
      "PID",
      "TDATE"
      ],
      "key_length": "7",
      "rows": 9566,
      "filtered": 100

        Smart Checklist

          Attachments

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              ishayw ishay wayner
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: