{"id":316,"date":"2011-06-07T12:42:14","date_gmt":"2011-06-07T12:42:14","guid":{"rendered":"http:\/\/10sa.com\/sql_stories\/?p=316"},"modified":"2012-08-24T09:39:45","modified_gmt":"2012-08-24T09:39:45","slug":"mysql-partitioning-issues","status":"publish","type":"post","link":"http:\/\/10sa.com\/sql_stories\/?p=316","title":{"rendered":"MySQL Partitioning issues."},"content":{"rendered":"

Supposing that the partitioning in MySQL is not popular, in my opinion the some part of documentation would be the reason for that.
\nI mean the following part Partitioning Keys, Primary Keys, and Unique Keys<\/a>. It sounds like a magic story in belles-lettres.
\nIf you try to make partition of a table and you see the following message:
\nERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table’s partitioning function
\ndo not read the documantation – it is sufficient to read one comment (Posted by Adrian Corston). The only one for the time being.
\nSo 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.<\/p>\n

Comparing to Oracle the partitioning is similar but not so complex as in Oracle. In Oracle I found two very important issues:
\n– dividing into global and local partitions (even on index level);
\n– possibility to partition a table according to reference to expression of other (parent) table (reference-partitioned<\/a>);
\nOracle documenation<\/a>.<\/p>\n

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.<\/p>\n

After your newly partitioned table is ready do not forget about TABLE ANALYZE<\/a>!<\/p>\n

    Additionaly one could be aware that:<\/p>\n
  1. the query cache is not supported for partitioned tables<\/a><\/li>\n
  2. ALTER TABLE DISCARD TABLESPACE for InnoDb partitioned tables<\/li>\n<\/ol>\n

    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”.<\/p>\n

    You can see the limits of your OS (This command displays the maximum number of file handles.)<\/p>\n

    \r\n# OS limitations\r\ncat \/proc\/sys\/fs\/file-max\r\n# Actual kernel limitations\r\n\/sbin\/sysctl -a | grep file-max\r\n\r\ncat \/proc\/sys\/fs\/file-nr\r\n3391    969     52427\r\n|\t |       |\r\n|\t |       |\r\n|        |       maximum open file descriptors\r\n|        total free allocated file descriptors\r\ntotal allocated file descriptors\r\n(the number of file descriptors allocated since boot)\r\n# src: http:\/\/www.netadmintools.com\/art295.html\r\n<\/pre>\n
    Now we complete the following file “\/etc\/security\/limits.conf”<\/p>\n
    \r\nmysql           soft    nofile  32768\r\nmysql           hard    nofile  65536\r\n<\/pre>\n
    and my.cnf (in mysqld section)
    \nopen_files_limit=65536<\/p>\n","protected":false},"excerpt":{"rendered":"
    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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[7],"tags":[],"_links":{"self":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/316"}],"collection":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=316"}],"version-history":[{"count":24,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/316\/revisions"}],"predecessor-version":[{"id":414,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=\/wp\/v2\/posts\/316\/revisions\/414"}],"wp:attachment":[{"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=316"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=316"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/10sa.com\/sql_stories\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=316"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}