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
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.
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]
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 :
A first good metric when inserting 1 row
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 …
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]
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 …
|
|
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 …
|
|
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 …
|
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. |
