Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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'
share|improve this question

migrated from stackoverflow.com Nov 13 '11 at 11:36

This question came from our site for professional and enthusiast programmers.

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

1 Answer 1

up vote 1 down vote accepted

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

MySQL, NoSQL: help me to choose the right one! (on a )

Hope this helps :)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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