Showing entries 1 to 10 of 15
5 Older Entries »
Displaying posts with tag: partitions (reset)
Yahoo's MySQL Partition Manager is Open Source

The guys at Yahoo released their partition management script on github:

At Yahoo, we manage a massive number of MySQL databases spread across multiple data centers. We have thousands of databases and each database has many partitioned tables. In order to efficiently create and maintain partitions we developed a partition manager which automatically manages these for you with minimal pre

MySQL Partitions timestamp - datetime

So I recently realized that I have not yet talked much about MySQL partitions.
Many good blog posts on MySQL partitions already exists and I have listed a few below.

[Read more]
MySQL bug 69179 – INFORMATION_SCHEMA.PARTITIONS causes query plan changes

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.  It may be that the per partition statistics …

[Read more]
Dropping MySQL partitions for MariaDB Dynamic column

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 we touch about 400 …
[Read more]
Dynarr256 for DBACC -or- The death of MAX_ROWS

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 MaxNoOfExecutionThreads = 4 or 8 cluster will have 2 or 4 LQH per node respectively. So, the …

[Read more]
Blowing up in memory

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 table (I filed a bug on this).

There’re multiple real …

[Read more]
Exchanging partitions with tables
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 attribution of the data, there is no copy involved. The data …

[Read more]
Holiday gift - A deep look at MySQL 5.5 partitioning enhancements

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

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 SHIBA has also created the …

[Read more]
MySQL Partitions at PHPCon Italia

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.

Showing entries 1 to 10 of 15
5 Older Entries »