Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 13

Displaying posts with tag: partitions (reset)

MySQL bug 69179 – INFORMATION_SCHEMA.PARTITIONS causes query plan changes
+1 Vote Up -0Vote Down

Shard-Query examines INFORMATION_SCHEMA.PARTITIONS to determine if a table is partitioned.  When a table is partitioned, Shard-Query creates multiple background queries, each limited to a single partition.  Unfortunately, it seems that examining INFORMATION_SCHEMA.PARTITIONS causes query plans to change after the view is accessed.

I have reported bug 69179 to MySQL AB  Oracle Corporation(old habits die hard).

Be careful: If you have automated tools (like schema management GUI tools) then make sure they don’t examine INFORMATION_SCHEMA.PARTITIONS or you may get bad plans until you analyze your tables or restart the database, even if using persistent stats.

I can only get the bug to happen when a WHERE clause is issued that limits access to a single partition.

  [Read more...]
Dropping MySQL partitions for MariaDB Dynamic column
+0 Vote Up -0Vote Down
In the same time of passionate debat of the best distribution MySQL vs MariaDB on a 120K Euro server(here), i wanted to share the story of drop of InnoDB insert performance for one of our client having a tables between 10 to 100 Billions rows.

In most case this size of table is just a no go in relationnel database.

So why it used to work so far  ?

c1 int, c2 tinyint, c3 mediumint, c4 binary(1), c5 float .
c2 avg cardinality 1000
c3 avg cardinality 10000
Very simple log table with optimized column type :
  • Partition by range on c1 where c1 increase with loading date
  • InnoDB compression 8K block size  
A first good metric when inserting 1 row
  • Into a 1G partition









  [Read more...]
Dynarr256 for DBACC -or- The death of MAX_ROWS
Employee +1 Vote Up -0Vote Down
Back in 2006 we became aware of problems storing large numbers of rows in a single table in cluster. Johan Andersson and Yves Trudeau have each blogged about the problem and the common workaround here and here.  We've since then done some cleanup to provide a more proper "Table is full" error message when running into this problem.

As explained in the referenced blog posts, the problem is the result of a limitation on the size of the hash index of each partition. The hash index for each partition would allow at most ~49 million records. By default an ndbd or ndbmtd node have only 1 local query handler (LQH) block and thus 1 partition per node.  The ndbmtd nodes having

  [Read more...]
Blowing up in memory
+3 Vote Up -0Vote Down

MySQL isn’t too concerned about table handler memory usage – it will allocate row size buffer thrice per each table invocation. There’s a few year old bug discussing UNION memory usage – for each mention in an union one can allocate nearly 200k of unaccounted memory – so a megabyte sized query can consume 7GB of RAM already.

Partitioning though adds even more pain here – it will allocate those three buffers per each partition, so opening a table with 1000 partitions looks like this on memory profile:

Click to enlarge, and you will see 191MB sent to execute a simple single-row fetching query from a

  [Read more...]
Exchanging partitions with tables
+8 Vote Up -2Vote Down
While I was presenting my partitioning tutorial at the latest MySQL Conference, I announced a new feature that was, as far as I knew, still in the planning stage. Mattias Jonsson, one of the partitions developers, was in attendance, and corrected me, explaining that the feature was actually available in a prototype.
So, we can have a look at this improvement, which I am sure will make DBAs quite happy. The new feature is an instantaneous exchange between a partition and a table with the same structure. Using this feature, you can transfer the contents of one partition to one table, and vice versa. Since the transition is done only in the
  [Read more...]
Holiday gift - A deep look at MySQL 5.5 partitioning enhancements
+13 Vote Up -0Vote Down



Half a day into my vacation, I managed to finish an article on a topic that has been intriguing me for a while.
Since several colleagues were baffled by the semantics of the new enhancements of MySQL 5.5 partitions, after talking at length with the creator and the author of the manual pages, I produced this article: A deep look at MySQL 5.5 partitioning enhancements.
Happy holidays!

UPDATE This matter was more tricky than it appeared at first sight. As Bug#49861 shows, several MySQL engineers were






  [Read more...]
Spider and vertical partition engines with new goodies
+4 Vote Up -0Vote Down



The Spider storage engine should be already known to the community. Its version 2.5 has recently been released, with new features, the most important of which is that you can execute remote SQL statements in the backend servers. The method is quite simple. Together with Spider, you also get an UDF that executes SQL code in a remote server. You send a query with parameters saying how to connect to the server, and check the result (1 for success, 0 for failure). If the SQL involves a SELECT, the result can be sent to a temporary table. Simple and effective.

In addition to the Spider engine, Kentoku




  [Read more...]
MySQL Partitions at PHPCon Italia
Employee +0 Vote Up -0Vote Down

I will speak at PHPCon Italia 2009, in Rome, on March 19th.

The subject is a very trendy. I will cover efficiency with partitions, a topic that every DBA and MySQL developers should enjoy.

MySQL Partitions at PHPCon Italia
Employee +0 Vote Up -0Vote Down

I will speak at PHPCon Italia 2009, in Rome, on March 19th.

The subject is a very trendy. I will cover efficiency with partitions, a topic that every DBA and MySQL developers should enjoy.

MySQL Partitions at PHPCon Italia
Employee +0 Vote Up -0Vote Down

I will speak at PHPCon Italia 2009, in Rome, on March 19th.

The subject is a very trendy. I will cover efficiency with partitions, a topic that every DBA and MySQL developers should enjoy.

FOSDEM 2009 - Boosting performance with MySQL 5.1 partitions
Employee +0 Vote Up -0Vote Down

The MySQL Developers Room at FOSDEM was a success. There was a full room (about 80 seats, and many sitting on the floor) throughout the day. We started at 9am with a talk on PBXT, and ended at 17:00. My talk had a large audience and was very well received.

The enthusiasm was quite high also because of a stunt I did during the presentation. It was a creative way of explaining the difference between physical and logical partitioning. I will repeat this stunt at the MySQL Users Conference, during my Tutorial on Partitioning.
Those who were

  [Read more...]
FOSDEM 2009 - Boosting performance with MySQL 5.1 partitions
Employee +0 Vote Up -0Vote Down

The MySQL Developers Room at FOSDEM was a success. There was a full room (about 80 seats, and many sitting on the floor) throughout the day. We started at 9am with a talk on PBXT, and ended at 17:00. My talk had a large audience and was very well received.

The enthusiasm was quite high also because of a stunt I did during the presentation. It was a creative way of explaining the difference between physical and logical partitioning. I will repeat this stunt at the MySQL Users Conference, during my Tutorial on Partitioning.
Those who

  [Read more...]
FOSDEM 2009 - Boosting performance with MySQL 5.1 partitions
Employee +0 Vote Up -0Vote Down

The MySQL Developers Room at FOSDEM was a success. There was a full room (about 80 seats, and many sitting on the floor) throughout the day. We started at 9am with a talk on PBXT, and ended at 17:00. My talk had a large audience and was very well received.

The enthusiasm was quite high also because of a stunt I did during the presentation. It was a creative way of explaining the difference between physical and logical partitioning. I will repeat this stunt at the MySQL Users Conference, during my Tutorial on Partitioning.
Those who

  [Read more...]
Showing entries 1 to 13

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

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.