I have a 16 core , 12 GB ram, a SSD 500 MB/s I have 1 millions rows in my "Pages" table

The pages contains 1 unique key that are HostID ( int ) and Page( varchar )

I use partitioning by key and have created briefly about 100 partitions

But i'm not sure that how many partitions that i should create for best performance.

briefly about 70 GB of data I use primary key to search :

    select * from table where HostID = 1 AND Page = 'asdasd'
  • 2
    And you created all of those partitions on the same physical disk? – N.B. Aug 24 '11 at 15:02
  • How big is one record (or how big is the whole table in GB)? Are you searching by HostID or by Page? What is the primary key of the table? – Darhazer Aug 24 '11 at 15:04
  • @N.B: yes . 70 GB of data , 200 GB of space – Juidan Ho Aug 24 '11 at 15:18
  • 1
    That defeats purpose of partitions.. partitions are there to help scale writes (and reads to some point). If you use the exact same hard drive - well, it makes no sense. The point of the partition is so that the data can reside at different locations, so that your MySQL doesn't become disk IO bound. To answer your question - the more partitions you create on that same SSD, the more complex your app will get with NO speed benefit. Use more hard drives. – N.B. Aug 24 '11 at 15:30
  • @N.B - Its not true that there will be no benefit at all if its all on the same disk. The concept of "partition pruning" can speed up queries dramatically. Smaller per-partition indexes will have another benefit as well. He'll be better off with multiple disks but partitioning is about more than just that. – bot403 Aug 24 '11 at 15:34

You might find the following answer which demonstrates how best to use clustered primary keys with the innodb engine of interest.


Hope this helps :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.