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_cachetable_pool_threads  default value of 80 (the only one of out the 3 that I left to default).
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://www.vecchiflipper.it
http://www.pinballowners.com/webmaster

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://tokutek.atlassian.net/browse/DB-1033
> 
> 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://bugs.launchpad.net/bugs/1656022
> 
> 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
>  *************************** 1. row ***************************
>         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`,`EXPIRE_DATE`),
>    KEY `EXPIRE_DATE_IX` (`EXPIRE_DATE`)
>  ) ENGINE=TokuDB DEFAULT CHARSET=latin1 ROW_FORMAT=TOKUDB_UNCOMPRESSED
>  /*!50100 PARTITION BY RANGE (UNIX_TIMESTAMP(EXPIRE_DATE))
>  (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 = 'aa60185e07d29c8866f00a5fa9e951aca07f3e3ae42b7ebb57474bf1a26721b9' AND EXPIRE_DATE > date_add(NOW(), interval 1 hour)\G
>  *************************** 1. row ***************************
>             id: 1
>    select_type: SIMPLE
>          table: mytable
>     partitions: p1004,p1005,p1006,p1007,p1008,p1009,p1010,p1011,p1012,p1013,p1014,p1015,p1016,p1017,p1018,p1019,p1020,p1021,p1022,p1023,p1024,p1025,p1026,p1027,p1028,p1029,p1030,p1031,p1032,p1033,p1034,p1035,p1036,p1037,p1038,p1039,p1040,p1041,p1042,p1043,p1044,p1045,p1046,p1047,p1048,p1049,p1050,p1051,p1052,p1053,p1054,p1055,p1056,p1057,p1058,p1059,p1060,p1061,p1062,p1063,p1064
>           type: range
>  possible_keys: PRIMARY,EXPIRE_DATE_IX
>            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=1484233335;
>  DELETE FROM mytable WHERE ID = '70f8536e1c07367e591be7d05092d2bf10fd3b2df463a7bdad412e6d705477f1' AND EXPIRE_DATE > DATE_ADD(NOW(), INTERVAL 1 HOUR);
> 
>  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://bugs.launchpad.net/percona-server/+bug/1656022/+subscriptions
>