MySQL Partitioning issues.
Supposing that the partitioning in MySQL is not popular, in my opinion the some part of documentation would be the reason for that.
I mean the following part Partitioning Keys, Primary Keys, and Unique Keys. It sounds like a magic story in belles-lettres.
If you try to make partition of a table and you see the following message:
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function
do not read the documantation – it is sufficient to read one comment (Posted by Adrian Corston). The only one for the time being.
So in that case you have PK (id as an autoincremented integer) and some additional unique indexed column (e.g. code which is unique per one product) you simply extend the PK with code. So, the following construction PRIMARY (id, code) allows you to use `code` in partitioning expression. `id` is still left positioned – so index for `id` works.
Comparing to Oracle the partitioning is similar but not so complex as in Oracle. In Oracle I found two very important issues:
– dividing into global and local partitions (even on index level);
– possibility to partition a table according to reference to expression of other (parent) table (reference-partitioned);
Oracle documenation.
One of the most important things in MySQL MyISAM partitioning is possible after-crash recovery facilities. It is due to the fact that mostly only the last partition is used for writing. After a crash you can check archive partitions and repair only the latest. Thanks to that the outgage time is minimalized.
After your newly partitioned table is ready do not forget about TABLE ANALYZE!
- Additionaly one could be aware that:
- the query cache is not supported for partitioned tables
- ALTER TABLE DISCARD TABLESPACE for InnoDb partitioned tables
I would recommend to enlarge the limits for open files by MySQL, in contrary you may excperience the errors like “Out of resources when opening file ‘/tmp/#s” or “Got error 24 from storage engine” – eventually you could see the following entry in your log “mysqld: the problem, but since we have already crashed, something is definitely wrong”.
You can see the limits of your OS (This command displays the maximum number of file handles.)
# OS limitations
cat /proc/sys/fs/file-max
# Actual kernel limitations
/sbin/sysctl -a | grep file-max
cat /proc/sys/fs/file-nr
3391 969 52427
| | |
| | |
| | maximum open file descriptors
| total free allocated file descriptors
total allocated file descriptors
(the number of file descriptors allocated since boot)
# src: http://www.netadmintools.com/art295.html
Now we complete the following file “/etc/security/limits.conf”
mysql soft nofile 32768 mysql hard nofile 65536
and my.cnf (in mysqld section)
open_files_limit=65536
No comments yet.