Jump to content


Photo

How does Mysql 5.6 perform vs Mysql 5.5 regarding select operations against a partitioned table ?


  • Please log in to reply
11 replies to this topic

#1 phdphd

phdphd

    Advanced Member

  • Members
  • PipPipPip
  • 125 posts

Posted 12 September 2013 - 03:00 PM

Hi All,

 

I have been doing some select tests on a partitioned table and on a non partitioned table, both having same data and around 4 millions records, and size of around 400 Mb each. Mysql version is 5.5.8.

 

The partition targeted in the partitioned table contains around 300000 records, and the select statement is of type

SELECT DISTINCT field1, field2 FROM table where partition_field3 =text_value


Even if a select operation against the partitioned table is indeed much faster than against the non-partitioned table, I noticed that in 5-10% of the cases it takes a bit too long from the user's perspective.

I was just wondering whether there is a significant difference in partition select performance between Mysql 5.5.8 and Mysql 5.6 since select operations against partitioned tables are not implemented the same way in both versions. In Mysql 5.6, the statement mentions the partition name rather than the "partitioned_column=value" criteria.  Does it mean that a select statement against a Mysql 5.6 partitioned table would be somewhat as fast as the "classic" form of the same select statement against a non-partitioned table holding the same data as the partition?

 

I would appreciate your opinion on that. I found no related benchmarks on the net.

 

Thanks
 



#2 Irate

Irate

    Advanced Member

  • Members
  • PipPipPip
  • 358 posts
  • LocationHamburg, Germany
  • Age:17

Posted 12 September 2013 - 03:04 PM

You can always run microtime() to get the starting time of your script and ending time of the script.
Quod placet mihi non placeat tibi. - What I think to be good must not always equal your perception of it.

I am not perfect. I try a lot with the code I provide and I don't guarantee for it to work as I have mostly no option to test it on my mobile phone. I do apologize for any inconvenience I caused, but if I do happen to have helped, liking my posts or marking them as to have solved or answered your question would be nice.

#3 phdphd

phdphd

    Advanced Member

  • Members
  • PipPipPip
  • 125 posts

Posted 12 September 2013 - 03:10 PM

I do not have mysql 5.6 yet, and would appreciate some advice from people who might have already made some comparisons between the 2 versions.



#4 Irate

Irate

    Advanced Member

  • Members
  • PipPipPip
  • 358 posts
  • LocationHamburg, Germany
  • Age:17

Posted 12 September 2013 - 03:52 PM

Right, sorry for not being able to help you there, then.
My mobile phone's very limited when it comes to active developing.
Quod placet mihi non placeat tibi. - What I think to be good must not always equal your perception of it.

I am not perfect. I try a lot with the code I provide and I don't guarantee for it to work as I have mostly no option to test it on my mobile phone. I do apologize for any inconvenience I caused, but if I do happen to have helped, liking my posts or marking them as to have solved or answered your question would be nice.

#5 kicken

kicken

    Wiser? Not exactly.

  • Gurus
  • 2,731 posts
  • LocationBonita, FL

Posted 12 September 2013 - 04:12 PM

From what I gather reading the documentation, the new partition select syntax would only improve your performance if your partitioning is setup in such a way that you can ignore safely ignore some partitions. For instance if you partition by year, but are only interested in this years data, you could limit mysql to only searching this years partition.

In addition to asking/searching around for other peoples opinions, you would probably be best served by downloading and installing mysql 5.6, importing your data, and playing around with it to see how it performs. Given the newness of mysql 5.6, and the specific nature of your inquiry, finding existing relevant opinions/benchmarks may be a bit challenging.
Recycle your old CD's, don't trash them!
Did I help you out?  Feeling generous? I accept tips via Paypal or Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7

#6 phdphd

phdphd

    Advanced Member

  • Members
  • PipPipPip
  • 125 posts

Posted 12 September 2013 - 05:02 PM

Thanks kicken for your reply.



#7 phdphd

phdphd

    Advanced Member

  • Members
  • PipPipPip
  • 125 posts

Posted 13 September 2013 - 04:50 AM

Actually docs say partitions are not compatible with innob tables that contain foreign keys. Since the table I want to partition will have a foreign key, I am afraid I will have to find a workaround.



#8 vinny42

vinny42

    Advanced Member

  • Members
  • PipPipPip
  • 414 posts

Posted 14 September 2013 - 05:10 AM


For instance if you partition by year, but are only interested in this years data, you could limit mysql to only searching this years partition

 

PostgerSQL also does this through "Constraint Exclusion". Each partition has a CHECK constraint that controls the range of data that can be inserted into each partition, and the query planner can use that constraint to work out which partitions it can ignore.

 

However, I think the problem here may be the DISTINCT, which may result in a SEQSCAN, which is slow no matter how you do it.

You can get the same result using SELECT field1, field2 FROM table WHERE ... GROUP BY field1, field2; which may convince MySQL to use an index. (provided that MySQL can use separate indexes per partition...)



#9 phdphd

phdphd

    Advanced Member

  • Members
  • PipPipPip
  • 125 posts

Posted 15 September 2013 - 10:49 AM

Hi All,

 

FYI I installed mysql 5.6 on an old 32-bit laptop and I am impressed by the power of partitioning in mysql 5.6, compared to partitioning in mysql 5.5. Despite the less powerful architecture (32 bits instead of 64 bits), select time has been divided by up to 3 (same table, same records).

 

Now I've got a question to specialists : I noticed that performance is even greater on page reload (as fast as just 2/100s). It seems the results are cached somewhere. Let's assume that User1 connects to the page containing the php code that runs the query against the database. If a few seconds later User2 connects to the same page, thus running the same query, will User2 benefit from the results having been cached ?

 

Thanks.

 

BTW:I tried the  "GROUP BY field1, field2" suggestion, but unfortunately without noticeable effects.



#10 mac_gyver

mac_gyver

    Advanced Member

  • Administrators
  • 2,607 posts

Posted 15 September 2013 - 10:55 AM

It seems the results are cached somewhere. Let's assume that User1 connects to the page containing the php code that runs the query against the database. If a few seconds later User2 connects to the same page, thus running the same query, will User2 benefit from the results having been cached ?

 

 

yes, mysql has a (result) query cache. if the exact same select query statement is executed, and there have been no changes (insert/update/delte) to the database tables in the query, the result will be returned from the cache. at the database, it doesn't know which of your web site users caused the query. the database only sees the database connection details and the query statements.


multi-purpose programming fool. well written source-code should be self-documenting. well written code should be self-troubleshooting. 


#11 phdphd

phdphd

    Advanced Member

  • Members
  • PipPipPip
  • 125 posts

Posted 15 September 2013 - 10:57 AM

another good piece of news :happy-04:!



#12 vinny42

vinny42

    Advanced Member

  • Members
  • PipPipPip
  • 414 posts

Posted 15 September 2013 - 01:36 PM


. If a few seconds later User2 connects to the same page, thus running the same query, will User2 benefit from the results having been cached ?

 

if it is exactly the same query, and none of the used table have changed in any way, and there is sufficient space allocated for caching, then yes.

 

 

 

BTW:I tried the  "GROUP BY field1, field2" suggestion, but unfortunately without noticeable effects.

 

 

 
 
 

Did you use EXPLAIN to see what the query does? You'd need an index on both columns together to make this work.


Edited by vinny42, 15 September 2013 - 01:40 PM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com