TusaCentral - MySQL Blogs

My MySQL tips

MySQL COLD CASE

In the lasts weeks, I choose to stay silent, to try to see and read other people blogs with a new eye. I was trying to go in depth of any single article, reading investigating and double-checking.

There were a lot of news and things going on, not all new and not all really interesting for me, but at the end, enough to le me say that I had spent many nights up reading and studying, instead leading ahead my own projects.

But what was surprise me as well, was that at the end what is very often interesting as a NEW feature or cool coming solution, it is not so interesting in the day by day work.

More what happens is sometime, or I should say too often, the new things that are cool development, are built on the rubble left behind.

Too often we have to still deal with annoying OLD bugs, or feature requests that are "by the facto" standard in almost all decent Database platforms.

Just in the last page (as for the time of writing) of Planet MySQL, I can see mention of the Microsecond issue from Chris Calendar, the way how MySQL handle temporary tables in "A More Perfect UNION " and last but not least my annoying lack of trigger DISABLE/ENABLE function.

So at the end I found myself looking to what I start to call "Cold Case" in my mind. I also star to thought "how I can help"? Well not forgetting them, and instead write and write again about them every time I will find, hoping to be able to propose also some works around, whenever possible.

This is why I have open a series of "Cold Case".

I also invite you all to do not forget and do not leave them behind. Write about your Cold Case and let us push for having them solve once for all.

{joscommentenable}

What about MySQL-Proxy

This post is not technical!

Who knows me, knows that I am not a nasty person.

I try to be polite and politically correct, and that has being a problem for me several times, because it pays only in long term.

But to day I need to talk about MySQL-Proxy so I can say something not pleasant for someone.

sakila_proxy_256x298

I have found myself in trouble several time because MySQL-Proxy for stability reasons and performance issues, but a part from that it normally works as expected.
Is really up to you and how you uses normally if you will get in trouble or not.

But ... hey MySQL-Proxy is an ALPHA product, oh my!!

It is an ALPHA not even BETA or RC, more reading the documentation TODAY:
"This documentation covers MySQL Proxy 0.8.0.
Warning

MySQL Proxy is currently an Alpha release and should not be used within production environments."

Honestly I have being mention MySQL -Proxy several times to customer but I always say to them, IT IS ALPHA, if you want I will install it,
I will configure and write for you special LUA scripts, BUT you must sign a paper that state you take the responsibility of installing an ALPHA product in production.

Normally the 90% of customers stops to go ahead, and try to find a different solution.

Too bad because MySQL-Proxy if correctly maintained could be an incredible tool, and ouch!! I discover this bug http://bugs.mysql.com/bug.php?id=61474.

Well you know is an ALPHA product so, I say to myself too bad, we should not expect a fast resolution.
We wait for a while then nothing really "serious" happened there, no fix, nothing, so we decide to drop it from the architecture.
The customer was not happy and neither I.

But I take note of it, and given I KNOW that MySQL Enterprise monitor use MySQL-Proxy I decide to dedicate some time to check, if this is still the case or not.

Today, I had a discussion with my 9 years Son, and to relax a little I put myself in front of some fun ... working with MySQL, yes I still enjoy it.

So I take out the Enterprise installation I did install it and then test it.

As expected MySQL-Proxy is still there, and documentations states:
"Using the MySQL Proxy functionality built into MySQL Enterprise Agent. This is the method offered and supported since MySQL Enterprise Monitor 2.0."

All good then, we have the possibility to add our own monitor or to use the default one provided by Oracle.

Hey wait a minute, is this a GA product?
No it is an Enterprise Product!

So why the MySQL Enterprise Monitor is using as default solution for an important functionality as the Slow Query Review an ALPHA product?

This sounds really odd to me.

Given I think no one will be happy to pay money for an ALPHA product, that has also issue with the latest MySQL versions, and given I think that lawyers in Oracle knows quite well their works.
I come to the conclusion that the MySQL-Proxy included in the Enterprise version is different from the one available.

Or at least I hope that for all the ones that are paying solid money for MySQL Enterprise Manager.

In any case, it is very sad that good and valuable work done in the past years by Jan Kneschke, and others like GiusePPe Maxia, is left behind damaging the community.

MySQL-Proxy is still the only solution for many stupid issues left behind in the MySQL programming, like user audit.

I recently had to implement a detailed audit of the actions done by Administrator on a MySQL instance.
The only very good and manageable solution was at the end the use of MySQL-Proxy, I will describe the work done in another post.

What I want to stress and reiterate here is: question-mark

IF MySQL-Proxy is so good to be in the MySQL Enterprise Monitor, that we assume it should be the top of stability, why then is still declared ALPHA??
IF MySQL-Proxy in Enterprise is a different product, then WHY it is not state in a clear way somewhere?
Finally WHY MySQL-Proxy is not correctly fixed and align with the latest MySQL products?

Simple questions that comes to my mind, I am not expecting an answer but may be some paying customers,
will have the same doubts I have, and it could be that given they pay they will get a real answer back from Oracle.

In that case please let us know.

Great MySQL to ALL!!!

{joscommentenable}

Oracle goes for Money

So what? nothing new ... from MySQL AB time.

at http://blogs.oracle.com/MySQL/entry/new_commercial_extensions_for_mysql

I saw it and I smile, hey I am happy to see that seriously!!

What I saw there were feature really Enterprise oriented AND really good for them as well, I though "GOOD STUFF", her what Rob report:

MySQL Enterprise Scalability

Thread Pool

MySQL Enterprise High Availability

Oracle VM Template for MySQL Enterprise Edition
Windows Clustering for MySQL Enterprise Edition

MySQL Enterprise Security

External Authentication for PAM
External Authentication for Windows

This is in line with what was discussed and discussed and discussed and discussed in MYSQL AB from 2006.

What do you think it would have being happen if unless being bought by SUN MySQL would have gone public?

Do you remember we did not have "Falcon" because it was not really scaling right, and we did invest a lot of money there,
so what will have MySQL done to recover some money and to be able to find an alternative solution?

MySQL will have done exactly the same because it makes a lot of sense, it is a logical path to follow in a open market.
What stops MySQL was not the fact that people in MySQL were good and now the Oracle guys are bad.
What stop it was the SUN acquisition, and the fact that SUN was so far away from MySQL that they don't really understand what would be good to go for, Period.

Oracle is just following not only his internal model, but something that was already up in the air from quite long.
MySQL needs to be Enterprise oriented, and in order to do that needs to have spacial features that are not available for the community version.

What I am really whatching is not if Oracle ask money for Enterprise feature, but that Oracle will not leave the community version behind.
I don't want to see MYSQL GA becoming a "B" series product, what I am concern is to have the same core feature, and to have in the enterprise ONLY the added ones specifically focus for the Enterprise customers.

I am exited to try the new features, and I will have the chance to do it downloading the MySQL enterprise trial.

I strongly recommend to read Giuseppe blog (http://datacharmer.blogspot.com/2011/09/welcome-mysql-commercial-extensions.html),

What will be tomorrow I cannot say, but so far so good, Go MySQL and (I will never imagine I would say that) go Oracle, good Job!

As usual my 2cent in supporting the cause.

tks to dilbert site as usual is a source of humor for full reference go to:

{joscommentenable}

A Missed Opportunity?

I am reading the announcement about the Percona MySQL event in place of the O'Reilly.

sakila_speaker_off What I see and what I feel is that the whole MySQL community, including Oracle, Percona, SkySQL,MariaDB and any other company doing business with MySQL have lost a good opportunity (so far).

For many years we have seen and hear comments about having this event more focus on users, and less bound to a single company. Now what? We will have a single company promoting the event. I don't care if it is done by Percona instead Oracle or SkySQL, actually I like all of them and I knew personally almost all of them as well.

What I care/like is to have this event NOT limited to a single company.

There was an attempt to have it organized in a different way (see Giuseppe and Sheeri comments), but then what?

I remember a long time ago (1983) when I was in Delhi and we have to run from the waiting room to the aeroplane in order to take a sit, that because the lack of coordination at the airport.
Are we in the same situation today?
The one that will move faster will get the place and the other will need to find a place after?

As a user I don't see that as a good thing, lacking in coordination, not be able to find a way to agree on how to do things, is never good.
As someone coming from MySQL I don't like it at all, where is the spirit of cooperation we had in MySQL is it completely gone?

After the MySQL diaspora, I have seen many time personal interest (and company interest) take over the common benefit, but at the end what happened is that we have now a potentially stronger MySQL community.
This because we need to see all of us (Oracle, Percona, SkySQL, MariaDB and any other related like Pythian where I am currently working) as competitor but also as part of the same “community”, we can compete for crumbs or we can compete for excellence, to be better. Wondering how many others community have such huge opportunity.

So I am really wonder what happened here? Are all of us so weak that we are not be able to organize an event as the MySQL conference?

I think that, as already happened in the past, all the actors would like to see this event happening as an OPEN and Independent event, focus on users and ... on business as well.

Said that, is Percona acting bad? No I don't think so, they are moving ahead, period. It is in their DNA, acting, do things.

Are them not following their own previous statements? Is too early to say that, but what I see here is also an incitation to all others to move on, and act.

What I really hope is to do not have in the next feature a SkyMySQL MySQL event, a MariaDB MySQL event and so on.

What I hope is to see all of us converging and helping each other in having this event as a common event and not as a single company.
Percona did the first move, but this doesn't prevent all others to join and have it to become a MySQL Community event. It will be great to recognize to Percona the role of initiators, but it would also be great to see Percona able to modify his position in respect to the event, from single company event to community open event.

As always I am a dreamer, and my dream here is that $$$ are less relevant than the great work we can do all together.
Despite the fact that doing it as single company will awaken less interest (also in $$$) then doing it as community event.

Remain one big question mark ... who will be in? Who is really ready to start putting effort and money to have a successful MySQL global event?

Will be nice to see statements of real commitment coming in from all actors.

Great MySQL to every one.

{joscommentenable}

Status update on how MySQL handle the partition(s) for maintenance

Overview

We know that having the chance to split a table in different and smaller chunk helps.
It helps because performance searches, insert, index handling and data management as well.
All fine we are all happy, and very often we push on our customer to go for partition in order to solve part of their problems.
What happens quite often is that customer (and sometime MySQL dba as well) see partitions as separate tables.
Unfortunately is not like that, there are some operations that are (still?) creating unexpected results.
One of this is the OPTIMIZATION of a partition.

The case

Some time ago, almost 16 months I was at customer site and we were working with MySQL 5.5 rc.
We were really happy with the new version of MySQL, and being working for MySQL/SUN I was quite proud of it as well.
Then we run the ALTER TABLE X OPTIMIZE PARTITION Y;

Suddenly we realize something was not working as expected because all partitions had temporary tables.
We know and were expecting the LOCK while doing the exercise, but this was not expected at all.

I was attending several internal presentations, and I was in many internal calls,
all stating that the operation should not affect all partitions but only one.
It was not nice, and it was a bug (42822) marked non critical, referring to another one 46158,
which was close because referring to the previous as a duplicate.

Funny thing at the end is ... we still have it in 5.5.12.

There is a work around as usual, but CUSTOMERS need to be aware and manual modified according,
unfortunately this is not the case.

So after all this time, it could sounds silly but let us talk how OPTIMIZE partitions doesn't work and what to do.

What I have done to replicate the error

I have set up a simple test instance with two tables, and use my stresstool (java),

to fill the tables and play with the inserting threads.

Here the tables:

DATABASE changed
root@localhost [test]> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| tbtest1        |
| tbtest2        |
+----------------+
4 rows IN SET (0.00 sec)
 
root@localhost [test]> DESCRIBE tbtest1;
+---------------+--------------+------+-----+-------------------+-----------------------------+
| FIELD         | Type         | NULL | KEY | DEFAULT           | Extra                       |
+---------------+--------------+------+-----+-------------------+-----------------------------+
| a             | int(11)      | NO   | MUL | NULL              |                             |
| uuid          | char(36)     | NO   | PRI | NULL              |                             |
| b             | varchar(100) | NO   |     | NULL              |                             |
| c             | char(200)    | NO   |     | NULL              |                             |
| counter       | bigint(20)   | YES  |     | NULL              |                             |
| time          | timestamp    | NO   |     | CURRENT_TIMESTAMP | ON UPDATE CURRENT_TIMESTAMP |
| partitonid    | int(11)      | NO   |     | 0                 |                             |
| strrecordtype | char(3)      | YES  |     | NULL              |                             |
+---------------+--------------+------+-----+-------------------+-----------------------------+

How the data looks like:

root@localhost [test]> SELECT * FROM tbtest1 LIMIT 10;
+------------+---------------+----------------+---------------+-------------+---------------------+------------+---------------+
| a          | uuid          | b              | c             | counter     | time                | partitonid | strrecordtype |
+------------+---------------+----------------+---------------+-------------+---------------------+------------+---------------+
|  793902056 | 00ea7db2babeb | ac hpzenqafpml | ac hpzr
|  366195670 | 07c620fdfab8d | umlfzae zdzbes | umlfzapgnhlvb |  3359819254 | 2011-06-03 12:27:26 |         18 | fai           |
|   19501151 | 08271816a1256 | odpp gporogqmg | odpp glpgfgnf |  3010735465 | 2011-06-03 12:27:26 |          5 | odo           |
| 1925082207 | 0bf7d042da285 | toammdmqgttlto | toammdeioidzs | 18048741375 | 2011-06-03 12:27:26 |         17 | gvm           |
|  631797159 | 0c9d4857f2f27 | lisn vqclglnmm | lisn vggpirza | 40556949738 | 2011-06-03 12:27:26 |         18 | ldc           |
+------------+---------------+----------------+---------------+-------------+---------------------+------------+---------------+
10 rows IN SET (0.01 sec

How the Table looks like before partition:

 
root@localhost [test]> 
CREATE TABLE `tbtest1` (
  `a` int(11) NOT NULL,
  `uuid` char(36) COLLATE utf8_unicode_ci NOT NULL,
  `b` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `c` char(200) COLLATE utf8_unicode_ci NOT NULL,
  `counter` bigint(20) DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `partitonid` int(11) NOT NULL DEFAULT '0',
  `strrecordtype` char(3) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`uuid`),
  KEY `IDX_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row IN SET (0.02 sec)

Then Insert the partition definition:

ALTER TABLE tbtest1 DROP PRIMARY KEY, ADD PRIMARY KEY (`uuid`,`partitonid`)
PARTITION BY RANGE (partitonid) (
    PARTITION p0 VALUES LESS THAN (2),
    PARTITION p1 VALUES LESS THAN (4),
    PARTITION p2 VALUES LESS THAN (6),
    PARTITION p3 VALUES LESS THAN (8),
    PARTITION p4 VALUES LESS THAN (10),
    PARTITION p5 VALUES LESS THAN (12),
    PARTITION p6 VALUES LESS THAN (14),
    PARTITION p7 VALUES LESS THAN (16),
    PARTITION p8 VALUES LESS THAN (18),
    PARTITION p9 VALUES LESS THAN (20),
    PARTITION p10 VALUES LESS THAN MAXVALUE
);
 
SELECT TABLE_SCHEMA,TABLE_NAME,partition_name,TABLE_ROWS AS 'ROWS', 
TRUNCATE(DATA_LENGTH/pow(1024,2),2) AS 'DATA (M)', 
TRUNCATE(INDEX_LENGTH/pow(1024,2),2) AS 'INDEX (M)',
TRUNCATE((DATA_LENGTH+INDEX_LENGTH)/pow(1024,2),2) AS 'TOTAL(M)' 
     FROM information_schema.partitions 
     WHERE TABLE_SCHEMA <> 'information_schema' 
       AND TABLE_SCHEMA <> 'mysql' 
       AND TABLE_NAME = 'tbtest1'
     ORDER BY `TOTAL(M)` DESC,`DATA (M)` DESC;
+--------------+------------+----------------+------+----------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | partition_name | ROWS | DATA (M) | INDEX (M) | TOTAL(M) |
+--------------+------------+----------------+------+----------+-----------+----------+
| test         | tbtest1    | p0             |  244 |     0.12 |      0.01 |     0.14 |
| test         | tbtest1    | p8             |  227 |     0.12 |      0.01 |     0.14 |
| test         | tbtest1    | p7             |  211 |     0.12 |      0.01 |     0.14 |
| test         | tbtest1    | p5             |  187 |     0.12 |      0.01 |     0.14 |
| test         | tbtest1    | p4             |  227 |     0.12 |      0.01 |     0.14 |
| test         | tbtest1    | p3             |  212 |     0.12 |      0.01 |     0.14 |
| test         | tbtest1    | p1             |  204 |     0.12 |      0.01 |     0.14 |
| test         | tbtest1    | p6             |  175 |     0.10 |      0.01 |     0.12 |
| test         | tbtest1    | p2             |  235 |     0.10 |      0.01 |     0.12 |
| test         | tbtest1    | p9             |  229 |     0.10 |      0.01 |     0.12 |
| test         | tbtest1    | p10            |    0 |     0.01 |      0.01 |     0.03 |
+--------------+------------+----------------+------+----------+-----------+----------+

And fill it with some data with my stresstool, but keeping the partition P10 empty, loading values up to PARTITIONID <=20

then filling P10 with an insert:

INSERT INTO tbtest1 (a, uuid,b,c,counter,time,partitonid,strrecordtype) 
SELECT a, uuid,b,c,counter,time,30,strrecordtype FROM tbtest1 WHERE partitonid <=20;
 
+--------------+------------+----------------+--------+----------+-----------+----------+
| TABLE_SCHEMA | TABLE_NAME | partition_name | ROWS   | DATA (M) | INDEX (M) | TOTAL(M) |
+--------------+------------+----------------+--------+----------+-----------+----------+
| test         | tbtest1    | p10            |  85104 |   106.95 |     10.54 |   117.50 |
| test         | tbtest1    | p5             |  95647 |    57.76 |      8.54 |    66.31 |
| test         | tbtest1    | p4             |  99459 |    57.76 |      8.54 |    66.31 |
| test         | tbtest1    | p3             |  92256 |    57.76 |      8.54 |    66.31 |
| test         | tbtest1    | p2             |  77794 |    57.76 |      8.54 |    66.31 |
| test         | tbtest1    | p9             |  79871 |    57.71 |      8.54 |    66.26 |
| test         | tbtest1    | p7             | 100681 |    57.76 |      7.54 |    65.31 |
| test         | tbtest1    | p1             | 102414 |    56.76 |      8.54 |    65.31 |
| test         | tbtest1    | p6             |  92961 |    56.76 |      7.54 |    64.31 |
| test         | tbtest1    | p8             |  93872 |    56.76 |      7.54 |    64.31 |
| test         | tbtest1    | p0             |  80510 |    56.76 |      7.54 |    64.31 |
+--------------+------------+----------------+--------+----------+-----------+----------+
 
 
 
root@localhost [(none)]> SHOW processlist;
+------+------+---------------------------+------+---------+------+--------+-------------------------------------------------------------------+
| Id   | User | Host                      | db   | Command | Time | State  | Info                                                              |
+------+------+---------------------------+------+---------+------+--------+-------------------------------------------------------------------+
|  144 | root | localhost                 | test | Sleep   |  285 |        | NULL                                                              |
|  195 | root | tusacentral03:56633       | test | Sleep   |   55 |        | NULL                                                              |
|  197 | root | tusacentral07.LOCAL:53368 | test | Sleep   |    1 |        | NULL                                                              |
|  199 | root | tusacentral07.LOCAL:53370 | test | Query   |    1 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,...becordtype)VALUES("2f...95-bc |
|  200 | root | tusacentral07.local:53371 | test | Query   |    1 | update | insert INTO test.tbtest1 (uuid,a,...becordtype)VALUES("0f...d5-a9 |
|  201 | root | tusacentral07.LOCAL:53372 | test | Query   |    1 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,...becordtype)VALUES("ce...94-9d |
|  202 | root | tusacentral07.local:53373 | test | Query   |    1 | update | insert INTO test.tbtest1 (uuid,a,...becordtype)VALUES("32...ec-98 |
|  203 | root | tusacentral07.LOCAL:53374 | test | Query   |    1 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,...becordtype)VALUES("c3...7d-86 |
|  204 | root | tusacentral07.local:53375 | test | Query   |    1 | update | insert INTO test.tbtest1 (uuid,a,...becordtype)VALUES("63...02-ba |
| 1031 | root | localhost                 | NULL | Query   |    0 | NULL   | SHOW processlist                                                  |
+------+------+---------------------------+------+---------+------+--------+-------------------------------------------------------------------+
10 rows IN SET (0.00 sec)

Time to run the optimize, and discover that nothing was changed in all this time.

mysql> ALTER TABLE tbtest1 OPTIMIZE  PARTITION p10;
 
root@localhost [(none)]> SHOW processlist;
+------+------+---------------------------+------+---------+------+---------------------------------+-------------------------------------------------------------+
| Id   | User | Host                      | db   | Command | Time | State                           | Info                                                        |
+------+------+---------------------------+------+---------+------+---------------------------------+-------------------------------------------------------------+
|  144 | root | localhost                 | test | Sleep   |  844 |                                 | NULL                                                        |
|  195 | root | tusacentral03:56633       | test | Query   |   92 | copy TO tmp TABLE               | ALTER TABLE tbtest1 OPTIMIZE  PARTITION p10                 |
|  197 | root | tusacentral07.LOCAL:53368 | test | Sleep   |    0 |                                 | NULL                                                        |
|  199 | root | tusacentral07.LOCAL:53370 | test | Query   |   90 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit-4cdd-a8 |
|  200 | root | tusacentral07.LOCAL:53371 | test | Query   |   90 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit-427f-be |
|  201 | root | tusacentral07.LOCAL:53372 | test | Query   |   90 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit-4694-9d |
|  202 | root | tusacentral07.LOCAL:53373 | test | Query   |   90 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit-4a5a-a0 |
|  203 | root | tusacentral07.LOCAL:53374 | test | Query   |   91 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit-48de-b8 |
|  204 | root | tusacentral07.LOCAL:53375 | test | Query   |   90 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit-4a0f-9c |
| 1031 | root | localhost                 | NULL | Query   |    0 | NULL                            | SHOW processlist                                            |
+------+------+---------------------------+------+---------+------+---------------------------------+-------------------------------------------------------------+
10 rows IN SET (0.00 sec)

MySQL, when using InnoDB, still do the process on all partitions and not only on the selected one.
This is probably not critical for the developer but a customer running it on a huge table could strongly disagree.

Immagine running it against a TABLE with 1.2TB and each partiton of 20-25GB.

It is not funny...

 
total 1031896
drwx------  2 mysql mysql     4096 2011-06-03 13:50 ./
drwxr-xr-x 11 mysql mysql     4096 2011-06-03 13:21 ../
-rw-rw----  1 mysql mysql     8578 2011-03-31 22:44 bbb.frm
-rw-rw----  1 mysql mysql   163840 2011-03-31 22:44 bbb.MYD
-rw-rw----  1 mysql mysql     1024 2011-03-31 22:44 bbb.MYI
-rw-rw----  1 mysql mysql     8578 2011-03-31 22:34 ccc.frm
-rw-rw----  1 mysql mysql   425984 2011-03-31 22:39 ccc.ibd
-rw-rw----  1 mysql mysql       61 2011-03-04 09:47 db.opt
-rw-rw----  1 mysql mysql     8788 2011-06-03 13:50 #sql-3205_c3.frm
-rw-rw----  1 mysql mysql       64 2011-06-03 13:50 #sql-3205_c3.par
-rw-rw----  1 mysql mysql 58720256 2011-06-03 13:51 #sql-3205_c3#P#p0.ibd
-rw-rw----  1 mysql mysql   114688 2011-06-03 13:50 #sql-3205_c3#P#p10.ibd
-rw-rw----  1 mysql mysql 54525952 2011-06-03 13:51 #sql-3205_c3#P#p1.ibd
-rw-rw----  1 mysql mysql   114688 2011-06-03 13:50 #sql-3205_c3#P#p2.ibd
-rw-rw----  1 mysql mysql   114688 2011-06-03 13:50 #sql-3205_c3#P#p3.ibd
-rw-rw----  1 mysql mysql   114688 2011-06-03 13:50 #sql-3205_c3#P#p4.ibd
-rw-rw----  1 mysql mysql   114688 2011-06-03 13:50 #sql-3205_c3#P#p5.ibd
-rw-rw----  1 mysql mysql   114688 2011-06-03 13:50 #sql-3205_c3#P#p6.ibd
-rw-rw----  1 mysql mysql   114688 2011-06-03 13:50 #sql-3205_c3#P#p7.ibd
-rw-rw----  1 mysql mysql   114688 2011-06-03 13:50 #sql-3205_c3#P#p8.ibd
-rw-rw----  1 mysql mysql   114688 2011-06-03 13:50 #sql-3205_c3#P#p9.ibd
-rw-rw----  1 mysql mysql     8788 2011-06-03 13:47 tbtest1.frm
-rw-rw----  1 mysql mysql       64 2011-06-03 13:47 tbtest1.par
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p0.ibd
-rw-rw----  1 mysql mysql 75497472 2011-06-03 13:49 tbtest1#P#p10.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p1.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p2.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p3.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p4.ibd
-rw-rw----  1 mysql mysql 88080384 2011-06-03 13:51 tbtest1#P#p5.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p6.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p7.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:51 tbtest1#P#p8.ibd
-rw-rw----  1 mysql mysql 96468992 2011-06-03 13:51 tbtest1#P#p9.ibd
-rw-rw----  1 mysql mysql     8630 2011-06-03 12:36 tbtest2.frm
-rw-rw----  1 mysql mysql  9437184 2011-06-03 13:51 tbtest2.ibd
root@tusacentral03:/home/mysql/instances/master/data/test#

So what to do?

Use REBUILD instead.

This drives me crazy, why the mapping could not be done inside the code?
If InnoDB then OPTIMIZE = REBUILD ... and go ahead.

To me this sounds not user friendly at all.

mysql> ALTER TABLE tbtest1 REBUILD PARTITION p10;
Query OK, 126994 rows affected (16.50 sec)
Records: 126994  Duplicates: 0  Warnings: 0
 
 
root@localhost [(none)]> SHOW processlist;
+------+------+---------------------------+------+---------+------+---------------------------------+-----------------------------------------------------------------+
| Id   | User | Host                      | db   | Command | Time | State                           | Info                                                            |
+------+------+---------------------------+------+---------+------+---------------------------------+-----------------------------------------------------------------+
|  144 | root | localhost                 | test | Sleep   |  357 |                                 | NULL                                                            |
|  195 | root | tusacentral03:56633       | test | Query   |    3 | setup                           | ALTER TABLE tbtest1 REBUILD PARTITION p10                       |
|  197 | root | tusacentral07.LOCAL:53368 | test | Sleep   |    0 |                                 | NULL                                                            |
|  199 | root | tusacentral07.LOCAL:53370 | test | Query   |    2 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit22da-4c7d-94 |
|  200 | root | tusacentral07.LOCAL:53371 | test | Query   |    2 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit8459-477b-94 |
|  201 | root | tusacentral07.LOCAL:53372 | test | Query   |    2 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit93b7-413f-a8 |
|  202 | root | tusacentral07.LOCAL:53373 | test | Query   |    2 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit6cd0-4e4b-85 |
|  203 | root | tusacentral07.LOCAL:53374 | test | Query   |    2 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partit79c1-4da5-8c |
|  204 | root | tusacentral07.LOCAL:53375 | test | Query   |    2 | Waiting FOR TABLE metadata LOCK | INSERT INTO test.tbtest1 (uuid,a,b,c,counter,partitff17-4be7-b1 |
| 1031 | root | localhost                 | NULL | Query   |    0 | NULL                            | SHOW processlist                                                |
+------+------+---------------------------+------+---------+------+---------------------------------+-----------------------------------------------------------------+
10 rows IN SET (0.00 sec)

Anyhow checking the file system, this time all works as expected and ONLY the partiton p10 was REBUILD.
Please take mental note of the STATE, is saying "Waiting for table metadata lock", this sounds interesting.

drwx------  2 mysql mysql     4096 2011-06-03 13:47 ./
drwxr-xr-x 11 mysql mysql     4096 2011-06-03 13:21 ../
-rw-rw----  1 mysql mysql     8578 2011-03-31 22:44 bbb.frm
-rw-rw----  1 mysql mysql   163840 2011-03-31 22:44 bbb.MYD
-rw-rw----  1 mysql mysql     1024 2011-03-31 22:44 bbb.MYI
-rw-rw----  1 mysql mysql     8578 2011-03-31 22:34 ccc.frm
-rw-rw----  1 mysql mysql   425984 2011-03-31 22:39 ccc.ibd
-rw-rw----  1 mysql mysql       61 2011-03-04 09:47 db.opt
-rw-rw----  1 mysql mysql     8788 2011-06-03 13:47 #sql-tbtest1.frm
-rw-rw----  1 mysql mysql       64 2011-06-03 13:47 #sql-tbtest1.par
-rw-rw----  1 mysql mysql     8788 2011-06-03 13:44 tbtest1.frm
-rw-rw----  1 mysql mysql       64 2011-06-03 13:44 tbtest1.par
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p0.ibd
-rw-rw----  1 mysql mysql 75497472 2011-06-03 13:46 tbtest1#P#p10.ibd
-rw-rw----  1 mysql mysql   606208 2011-06-03 13:47 tbtest1#P#p10#TMP#.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p1.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p2.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p3.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p4.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p5.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p6.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p7.ibd
-rw-rw----  1 mysql mysql 83886080 2011-06-03 13:47 tbtest1#P#p8.ibd
-rw-rw----  1 mysql mysql 92274688 2011-06-03 13:47 tbtest1#P#p9.ibd
-rw-rw----  1 mysql mysql     8630 2011-06-03 12:36 tbtest2.frm
-rw-rw----  1 mysql mysql  9437184 2011-06-03 13:46 tbtest2.ibd
root@tusacentral03:/home/mysql/instances/master/data/test#

While there and to avoid doubt I also checked what about CHECKING a partition,

Who knows may be some surprise as well

mysql> ALTER TABLE tbtest1 CHECK  PARTITION p10;
+--------------+-------+----------+----------+
| TABLE        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| test.tbtest1 | CHECK | STATUS   | OK       |
+--------------+-------+----------+----------+
1 row IN SET (36.64 sec)
 
 
root@localhost [(none)]> SHOW processlist;
+------+------+---------------------------+------+---------+------+--------+----------------------------------------------------------+
| Id   | User | Host                      | db   | Command | Time | State  | Info                                                     |
+------+------+---------------------------+------+---------+------+--------+----------------------------------------------------------+
|  144 | root | localhost                 | test | Sleep   | 1114 |        | NULL                                                     |
|  195 | root | tusacentral03:56633       | test | Query   |   15 | NULL   | ALTER TABLE tbtest1 CHECK  PARTITION p10                 |
|  197 | root | tusacentral07.LOCAL:53368 | test | Sleep   |    0 |        | NULL                                                     |
|  199 | root | tusacentral07.LOCAL:53370 | test | Query   |    0 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,b,c,counte-39e0-4296-97 |
|  200 | root | tusacentral07.LOCAL:53371 | test | Query   |    1 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,b,c,counte-f508-4ec8-87 |
|  201 | root | tusacentral07.LOCAL:53372 | test | Query   |    1 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,b,c,counte-3a5f-4444-81 |
|  202 | root | tusacentral07.LOCAL:53373 | test | Query   |    1 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,b,c,counte-970e-48bb-a2 |
|  203 | root | tusacentral07.LOCAL:53374 | test | Query   |    0 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,b,c,counte-62ea-4c1f-81 |
|  204 | root | tusacentral07.LOCAL:53375 | test | Query   |    0 | UPDATE | INSERT INTO test.tbtest1 (uuid,a,b,c,counte-6277-4dd5-89 |
| 1215 | root | localhost                 | NULL | Query   |    0 | NULL   | SHOW processlist                                         |
+------+------+---------------------------+------+---------+------+--------+----------------------------------------------------------+
10 rows IN SET (0.00 sec)

But luckly no it works fine and no lock on the table.

Conclusions

1) We still have to deal with this non critical bug, and we must remember
DO NOT USE:

ALTER TABLE <table_name> OPTIMIZE PARTITION A,B,C

BUT

ALTER TABLE <table_name> REBUILD PARTITION A,B,C

Not yet clear to me why a simple mapping between REBUILD and OPTIMIZATION is not possible.

2) The level of lock is related to METADATA because the ALTER command, looking around for a better explanation I have seen that there is not too much material about it, but the presentation from Konstantin Osipov for sure helps to better understand.
http://en.oreilly.com/mysql2011/public/schedule/detail/17340

My dream

The OPTIMIZATION of a partition is a special case, where data, is not modified but eventually physically re-organized.
Having the status of the lock declared as METADATA lock, brings me to think we can safely insert/modify all data not related to that partition.
What I am feeling from all this, is the possibility to have in the near future this lock release, I see issues with the Index merge and others so some digging in the code will require..

It would be nice ...to have it.

Reference

17.3.3. Maintenance of Partitions - http://dev.mysql.com/doc/refman/5.1/en/partitioning-maintenance.html
Metadata locking and deadlock detection in MySQL 5.5 - http://en.oreilly.com/mysql2011/public/schedule/detail/17340
optimize partition recreates full table instead just partition - http://bugs.mysql.com/bug.php?id=42822
Make partition maintenance commands more efficient - http://bugs.mysql.com/bug.php?id=46158

Sidebar

Main Menu Mobile

MySQL Blogs

MySQL COLD CASE

What about MySQL-Proxy

This post is not technical!

Oracle goes for Money

So what? nothing new ... from MySQL AB time.

A Missed Opportunity?

Status update on how MySQL handle the partition(s) for maintenance

Overview

The case

What I have done to replicate the error

Conclusions

My dream

Reference

More Articles …

Path

login