Re: Modify primary key for partitioning
Thank you again for your reply !
>Let's see the UPDATE. And any other queries that may be affected
The UPDATEs are simple, something like "UPDATE mytable SET column1=x WHERE ID_MSD='value'. It had to do a full scan and so took ages to perform. If needed, I'll create an index either on RECEIVED_DATE or ('REVCEIVED_DATE','ID_MSG').
The other queries include (most of the time) a condition on either received_date or id_msg. Not 100% sure about that, but the queries rarely include both RECEIVED_DATE AND ID_MSG columns.
>Ouch. How often do you need those two fields? I'm thinking about "vertically partitioning" them into another table.
Generally speaking, we need to extract those two fields only once, for administrative purposes.
>What is the value of innodb_buffer_pool_size? Hopefully less than the 1GB you have for mysql.
innodb_buffer_pool_size is currently sized of 305 MB
>That is usually a mistake. An index on a true/false column is almost never used; DROP it. Similarly for indexes on single fields where the field has only a few distinct values, and is somewhat evenly distributed. Use "compound" indexes where it makes sense.
I know that's crazy to have so many indexes. My next step (when done with partitioning and compression) is to do some cleaning. On one of those table which reveive many inserts weekly (from batch), I tried to insert 100k rows on this table with and without the indexes (I kept only the PK). The insert were 3 times faster with only the PK. Most of the indexes are useless. (for exemple, only 3 distincts values on more than 300k rows)
By the way, do you think it's a mistake to set innodb_flush_log_at_trx_commit=2 ? I tried it, the performance was incredibly better (I tried with bulk inserts), but I know that's a bit risky (we can lose 1 second of data).
>InnoDB really likes to have an _explicit_ PRIMARY KEY -- either a 'natural' pk, or an artificial AUTO_INCREMENT.
I meant only the PK. All the tables have at least a PK, but I think some of them would appreciate some indexes.
>Interesting to hear that MariaDB was noticeably faster. Perhaps because it includes Percona's Xtradb instead of InnoDB.
I set the parameters equally (MySQL 5.0 and MariaDB) on the same server. Both of them are slave of my production database. The table on the MySQL 5.0 is a "basic" table (not partitoned, not compressed) of 80 GB. The one on the MariaDB 5.5 is partitioned (I do my test on this database) and compressed (the table size is only 11GB). Partitioning should slow down a full scan, but the table size of only 11BG against 80GB, may speed up the query.
>Probably. But I would not go beyond 100 partitions.
I think to partition by quarter (partition of 3 months). I have got only year 2011 and 2012 so far, but half of the rows have a received_date equal to '0000-00-00 00:00:00' so I get an unbalanced first partition. I guess, those rows was before an application upgrade, and so it shouldn't happen anymore.
>If your app compressed XMLCERTIFICATE, you could save _half_ of your 80GB! Smaller -> more cacheable -> faster. OK, maybe it would not save that much, since you are already using InnoDB's "COMPRESSED". Still, you would be doing it in a different way.
The production table is not compressed size 80GB. My test table (the one I gave you the SHOW TABLE STATUS, on MariaDB 5.5) is compressed and size 11GB. Sorry if my explanations are sometimes confused.
>I'm not sure that will work for InnoDB, even with the unchanging partitions. Test before you depend on it!
Indeed I haven't tried it yet.
>I'm thinking about "vertically partitioning" them into another table.
What's the main goal ? Getting a smaller table (without those two huge columns) and so have a cacheable table ?
What are the consequences for the application ? I guess some queries would need to be rewritten ?
Thank you for your time.
Edited 1 time(s). Last edit at 08/31/2012 03:32AM by Aurelien PANIZZA.