1

We've recently begun testing an upgrade from mySQL5.6 to percona server 5.7 and the use of tokuDB tables. The database is serving our PHP 5.5 application which use PDO library for parameterized querying.

Upon loading up percona with identical data into a tokudb table and comparing performance to the existing production we immediately noticed a huge drop in performance (10x slower). For the queries below assume the table has 12 million rows

I've been able to narrow this issue down in the 5.7 database to the fact that when executing a query such as:

SELECT * FROM TABLE WHERE id='12345'; -- exec time 10.5sec
vs.
SELECT * FROM TABLE WHERE id=12345; -- exec time 1.3sec

where id is of column type integer. It was my impression and my research seems to confirm that mySQL should do implicit conversion of '12345' to 12345 when the column compared is a numeric type, however this doesn't seem to be happening in mySQL5.7/Percona. It was happening in mySQL5.6x

The problem here is that with this behavior, you'd need to explicitly set the type using PDOStatement::bindParam (ref http://php.net/manual/en/pdostatement.bindparam.php) for each variable! Doing this would cause a near global rewrite of all prepared statements which currently pass arrays of parameters to PDOStatement:execute() which doesn't support explicit type setting!

So - my question is this - has something changed in mySQL so implicit conversion isn't done in 5.7 or is it Percona or is it tokuDB table? Is there a configuration parameter I can set to turn this back on?

Ross
  • 1,639
  • 1
  • 18
  • 22
  • I will put up a bounty for the answer. – Your Common Sense Mar 27 '16 at 18:57
  • Out of curiosity, why the move to Percona instead of the more common step to MariaDB? – Jacco Mar 27 '16 at 19:25
  • @Jacco - because Percona acquired tokutek. Also, percona is and has been a leading voice in high performance mySQL for years. – Ross Mar 29 '16 at 22:33
  • I'll post more info when I get some free time - its time consuming to load multiple other test environments with different server version/table types etc – Ross Mar 29 '16 at 22:40

1 Answers1

0

It is not clear if you are trying to upgrade and compare 5.6 TokuDB performance to 5.7 TokuDB performance or 5.6 InnoDB to 5.7 TokuDB, can you please clarify and identify the specific 5.6 and 5.7 variants and versions?

If TokuDB all around, one possibility is incorrect index selection due to bad/old/NULL index statistics. There are also many SQL_MODE defaults changes in 5.7 some of which could also be influencing the behavior.

It might also be useful to see the results of 'SHOW CREATE TABLE' and 'SHOW INDEXES FROM' on both the 5.6 and 5.7 instances.

George Lorch
  • 201
  • 1
  • 4
  • While these differences are unfortunately potential confounders, my question is really about whether there is an issue (as there seems to be) with implicit type conversion in mySQL or Percona, potentially when using the TokuDB engine vs the InnoDB. The issue I highlighted exists in Percona 5.7 with Tokudb but not in Percona 5.6 (or mySQL 5.6) with InnoDB. – Ross Mar 29 '16 at 22:38
  • As far as I know, there is no known issue. Type conversion is not a Storage Engine function, the server above handles that before sending the fields down into the engines through the SE API, so there must be some other contributing factor. Full disclosure, I am the TokuDB Technical Lead at Percona. – George Lorch Mar 29 '16 at 23:41