Comment 20 for bug 1656022
Revision history for this message
| Rick Pizzi (pizzi) wrote Re: [Bug 1656022] Re: Partition pruning doesn't seem to work with TokuDB engine | #20 |
© 2004
Canonical Ltd.
•
Terms of use
•
Data privacy
•
Contact Launchpad Support
•
Blog
•
Careers
•
System status
•
f33e652
(Get the code!)
Saw the issue, thanks!
Looking forward to try the fix,
I have noticed that once the dataset grows more than what the OS cache can handle, we are back to square one as far as stalls go.
Tomorrow I will go back to 16k (sorry!) and to 50% tokudb_cache_mem which offered the best results in our situation so far.
Mind you, with 8 GB toku cache and 64k once the dataset hit the disk, I have been able to saturate the tokudb_
Not sure why this happened or what that means actually, but having the pool saturated for one minute when I had the stall just seemed related.
Thanks!
Rick
--
Riccardo Pizzi
<email address hidden>
http://
http://
Dogs have masters. Cats have staff.
On 16/gen/2017, at 20:31, George Ormond Lorch III <email address hidden> wrote:
> Hi Rick,
> I've been looking around this one call and decided that a slightly different approach should be taken to correct this, assuming that it is what is I think it is as evidenced by the pmp output.
>
> Here is the issue tracker for TokuDB
> https:/
>
> Once I get this fixed and tested I will try to get you an experimental
> binary to test out.
>
> --
> You received this bug notification because you are subscribed to the bug
> report.
> https:/
>
> Title:
> Partition pruning doesn't seem to work with TokuDB engine
>
> Status in Percona Server:
> New
>
> Bug description:
> We noticed that partition pruning, although correctly shown by the
> EXPLAIN PARTITIONS command, is not honoured for the TokuDB engine.
>
> Please consider the following table:
>
> show create table mytable\G
> *******
> Table: mytable
> Create Table: CREATE TABLE `mytable` (
> `ID` char(64) NOT NULL,
> `DATA_BLOB` mediumblob NOT NULL,
> `EXPIRE_DATE` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
> PRIMARY KEY (`ID`,`
> KEY `EXPIRE_DATE_IX` (`EXPIRE_DATE`)
> ) ENGINE=TokuDB DEFAULT CHARSET=latin1 ROW_FORMAT=
> /*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP
> (PARTITION p1003 VALUES LESS THAN (1484240400) ENGINE = TokuDB,
> PARTITION p1004 VALUES LESS THAN (1484244000) ENGINE = TokuDB,
> PARTITION p1005 VALUES LESS THAN (1484247600) ENGINE = TokuDB,
> PARTITION p1006 VALUES LESS THAN (1484251200) ENGINE = TokuDB,
> PARTITION p1007 VALUES LESS THAN (1484254800) ENGINE = TokuDB,
> PARTITION p1008 VALUES LESS THAN (1484258400) ENGINE = TokuDB,
> PARTITION p1009 VALUES LESS THAN (1484262000) ENGINE = TokuDB,
> PARTITION p1010 VALUES LESS THAN (1484265600) ENGINE = TokuDB,
> PARTITION p1011 VALUES LESS THAN (1484269200) ENGINE = TokuDB,
> PARTITION p1012 VALUES LESS THAN (1484272800) ENGINE = TokuDB,
> PARTITION p1013 VALUES LESS THAN (1484276400) ENGINE = TokuDB,
> PARTITION p1014 VALUES LESS THAN (1484280000) ENGINE = TokuDB,
> PARTITION p1015 VALUES LESS THAN (1484283600) ENGINE = TokuDB,
> PARTITION p1016 VALUES LESS THAN (1484287200) ENGINE = TokuDB,
> PARTITION p1017 VALUES LESS THAN (1484290800) ENGINE = TokuDB,
> PARTITION p1018 VALUES LESS THAN (1484294400) ENGINE = TokuDB,
> PARTITION p1019 VALUES LESS THAN (1484298000) ENGINE = TokuDB,
> PARTITION p1020 VALUES LESS THAN (1484301600) ENGINE = TokuDB,
> PARTITION p1021 VALUES LESS THAN (1484305200) ENGINE = TokuDB,
> PARTITION p1022 VALUES LESS THAN (1484308800) ENGINE = TokuDB,
> PARTITION p1023 VALUES LESS THAN (1484312400) ENGINE = TokuDB,
> PARTITION p1024 VALUES LESS THAN (1484316000) ENGINE = TokuDB,
> PARTITION p1025 VALUES LESS THAN (1484319600) ENGINE = TokuDB,
> PARTITION p1026 VALUES LESS THAN (1484323200) ENGINE = TokuDB,
> PARTITION p1027 VALUES LESS THAN (1484326800) ENGINE = TokuDB,
> PARTITION p1028 VALUES LESS THAN (1484330400) ENGINE = TokuDB,
> PARTITION p1029 VALUES LESS THAN (1484334000) ENGINE = TokuDB,
> PARTITION p1030 VALUES LESS THAN (1484337600) ENGINE = TokuDB,
> PARTITION p1031 VALUES LESS THAN (1484341200) ENGINE = TokuDB,
> PARTITION p1032 VALUES LESS THAN (1484344800) ENGINE = TokuDB,
> PARTITION p1033 VALUES LESS THAN (1484348400) ENGINE = TokuDB,
> PARTITION p1034 VALUES LESS THAN (1484352000) ENGINE = TokuDB,
> PARTITION p1035 VALUES LESS THAN (1484355600) ENGINE = TokuDB,
> PARTITION p1036 VALUES LESS THAN (1484359200) ENGINE = TokuDB,
> PARTITION p1037 VALUES LESS THAN (1484362800) ENGINE = TokuDB,
> PARTITION p1038 VALUES LESS THAN (1484366400) ENGINE = TokuDB,
> PARTITION p1039 VALUES LESS THAN (1484370000) ENGINE = TokuDB,
> PARTITION p1040 VALUES LESS THAN (1484373600) ENGINE = TokuDB,
> PARTITION p1041 VALUES LESS THAN (1484377200) ENGINE = TokuDB,
> PARTITION p1042 VALUES LESS THAN (1484380800) ENGINE = TokuDB,
> PARTITION p1043 VALUES LESS THAN (1484384400) ENGINE = TokuDB,
> PARTITION p1044 VALUES LESS THAN (1484388000) ENGINE = TokuDB,
> PARTITION p1045 VALUES LESS THAN (1484391600) ENGINE = TokuDB,
> PARTITION p1046 VALUES LESS THAN (1484395200) ENGINE = TokuDB,
> PARTITION p1047 VALUES LESS THAN (1484398800) ENGINE = TokuDB,
> PARTITION p1048 VALUES LESS THAN (1484402400) ENGINE = TokuDB,
> PARTITION p1049 VALUES LESS THAN (1484406000) ENGINE = TokuDB,
> PARTITION p1050 VALUES LESS THAN (1484409600) ENGINE = TokuDB,
> PARTITION p1051 VALUES LESS THAN (1484413200) ENGINE = TokuDB,
> PARTITION p1052 VALUES LESS THAN (1484416800) ENGINE = TokuDB,
> PARTITION p1053 VALUES LESS THAN (1484420400) ENGINE = TokuDB,
> PARTITION p1054 VALUES LESS THAN (1484424000) ENGINE = TokuDB,
> PARTITION p1055 VALUES LESS THAN (1484427600) ENGINE = TokuDB,
> PARTITION p1056 VALUES LESS THAN (1484431200) ENGINE = TokuDB,
> PARTITION p1057 VALUES LESS THAN (1484434800) ENGINE = TokuDB,
> PARTITION p1058 VALUES LESS THAN (1484438400) ENGINE = TokuDB,
> PARTITION p1059 VALUES LESS THAN (1484442000) ENGINE = TokuDB,
> PARTITION p1060 VALUES LESS THAN (1484445600) ENGINE = TokuDB,
> PARTITION p1061 VALUES LESS THAN (1484449200) ENGINE = TokuDB,
> PARTITION p1062 VALUES LESS THAN (1484452800) ENGINE = TokuDB,
> PARTITION p1063 VALUES LESS THAN (1484456400) ENGINE = TokuDB,
> PARTITION p1064 VALUES LESS THAN (1484460000) ENGINE = TokuDB) */
>
> If we run the following explain, all seems okay:
>
> mysql>explain partitions DELETE FROM mytable WHERE ID = 'aa60185e07d29c
> *******
> id: 1
> select_type: SIMPLE
> table: mytable
> partitions: p1004,p1005,
> type: range
> possible_keys: PRIMARY,
> key: PRIMARY
> key_len: 68
> ref: const,const
> rows: 1
> Extra: Using where
> 1 row in set (0.00 sec)
>
> We can see that partition p1003 is pruned because contains stale data
> (rows with EXPIRE_DATE less than one hour old).
>
> However, when running DROP PARTITION p1003, we see stalls in SELECT
> and DELETE statements that should NOT hit that partition because of
> pruning:
>
> # Time: 170112 16:02:15
> # Schema: myschema Last_errno: 0 Killed: 0
> # Query_time: 12.219223 Lock_time: 9.126238 Rows_sent: 0 Rows_examined: 0 Rows_affected: 0
> # Bytes_sent: 11 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
> # QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
> # Filesort: No Filesort_on_disk: No Merge_passes: 0
> SET timestamp=
> DELETE FROM mytable WHERE ID = '70f8536e1c0736
>
> You can see that this query took 12 seconds and was locked for 9
> seconds waiting for what seems a lock on the partition which we
> expected to be pruned.
>
> We wouldn't expect the above DELETE to lock if the partition we are
> dropping is pruned and therefore out of scope for the DML.
>
> Server version: 5.6.34-79.1-log Percona Server (GPL), Release
> 79.1, Revision 1c589f9
>
> thanks
> Rick
>
> To manage notifications about this bug go to:
> https:/
>