Posted by: Eric Jensen
Date: July 03, 2008 10:35AM
I have been experimenting with 5.1 partitioning, but performance issues are leading me to abandoning it.
The documentation says "Key caches are not supported for partitioned tables.", but then goes on to describe how preloading indexes into caches isn't supported. Does this mean that indexes from partitioned tables cannot be cached in the key cache at all, even through normal means and not preloading? Based on my experiments, it appears that this is the case because I had delay_key_write=ALL on, but it was not filling up with "key blocks not flushed" as it normally would.
I have a simple one-to-many mapping table consisting of two integer ID's that are auto_increment in other tables, so keep increasing. It becomes huge. I set it up to range partition every X values in the auto_increment and let it run. What I found, was that instead of mysql being I/O bound as it normally is, as I added more partitions (about 20) it eventually became entirely CPU bound to the point where it was much slower than the I/O bound inserts into the huge unpartitioned tables. My large "insert into my_partitioned_mapping_table values ..." statements were taking 100% mysqld CPU (about half user and half system) in the "Update" thread state. My hypothesis from this is that while selects have efficient partition pruning, inserts are doing something CPU-intensive to check every value against every partition?
Are these simply bugs? If not, then between the two of them, I can't use mysql partitioning as it degrades both query performance by lacking an index cache of any kind (would only make sense for unindexed tables that get scanned), and insert performance.
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.