|Showing entries 1 to 30 of 86||Next 30 Older Entries|
mysql> CREATE TABLE pfoo (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255))[Read more...]
-> PARTITION BY HASH(id) PARTITIONS 4;
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO pfoo(name) VALUES('test01'),('test02'),('test03'),('test04'),
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM pfoo;
| id | name |
| 4 | test04
Recently i had to manage big database installation that was running out of disk space. The partition on which the mysql datadir was located only had a few gigabytes free. Resizing the partition was not possible without a long downtime so that was no option. The installation had only innodb-tables so using symlinks was also not possible.
Luckily they were using MySQL 5.6 and the server had another partition with more than enough disk space available so i decided to use that partition for the biggest tables.
First get the 15 biggest tables:
root@mysqlserver [(none)]> set global innodb_stats_on_metadata=0; select table_schema, table_name "Table Name",round(sum(data_length+index_length)/1024/1024/1024,4) "Size (GB)" from information_schema.tables group by table_schema,table_name ORDER BY
Following on from my fun post on Where are they now: MySQL Storage Engines, I thought I’d cover the few storage engines that are really just interfaces to a collection of things. In this post, I’m talking about MERGE.
The MERGE engine was basically a multiplexer down to a number of MyISAM tables. They all had to be the same, there was no parallel query execution and it saw fairly limited use. One of the main benefits was that then you could actually put more rows in a MyISAM table than your “files up to 2/4GB” file system allowed. With the advent of partitioning, this really should have instantly gone away[Read more...]
At the recent MySQL Tech Tour Events in New York and Boston I gave a presentation on MySQL integration with NoSQL,Big Data and the Cloud.
This covered discussion on topics including:
mysql> create table nye (`event_id` int not null auto_increment,`edate` year(4) not null, description varchar(200),
-> primary key(`event_id`,`edate`))
-> partition by range( edate ) (
-> partition p2010 VALUES LESS THAN (2011),
-> partition p2011 VALUES LESS THAN (2012),
-> partition p2012 VALUES LESS THAN (2013) );
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO nye(edate,description) VALUES('2010','twenty ten');
Query OK, 1 row affected (0.00 sec)[Read more...]
mysql> INSERT INTO nye(edate,description)
common_schema 1.2 is released! This version comes shortly after 1.1, yet contains quite a few interesting goodies:
A closer look at these follows:
A new view called[Read more...]
If you use RANGE (or RANGE COLUMNS) partitioning, and in particular when partitioning by date/time, then your are subject to the following questions: how and when do you create the "next" partition? How and when do you drop your older partitions?
Many people use in-house scripting to do that, and Giuseppe Maxia wrote Partition Helper. But I would like to take you one step forward, and provide with a query (based on views) which automagically understands which new partition you want to create, and provides you with the statement to do so. It looks somewhat like this (a demo follows later on):
[Read more...]mysql> SELECT * FROM sql_range_partitions \G *************************** 1. row
Like others we were not satisfied with the fix for a bug in MySQL which caused the query cache and partitioning to not work reliably together. The bug, in simple terms, was that if the query cache was enabled and you used partitioned tables and if a partitioned table was using a transactional engine like InnoDB or XtraDB, the query cache could, under certain circumstances, return incorrect results.
Returning incorrect results is a definite, high-priority bug. However, the upstream fix was to disable all caching of queries from partitioned tables. We wanted a better solution[Read more...]
A customer opened an issue recently to ask why the query cache wasn't working after he upgraded to MySQL 5.5.25. The reason really ended up surprising me.
As of MySQL 5.5.23, the Query Cache is disabled for partitioned tables!
This is a "fix" for bug #53775.
At first I thought perhaps the fix for the bug had resulted in the query cache being inadvertently disabled for partitioned tables, but the comments that go along with the commit make it pretty clear that disabling the query cache was the intended "fix". You can review the commit message and the code changed at revision 2661.803.1 in the MySQL Server 5.5 repository.
There’s a lot of great new features in MySQL 5.6 DMRs – almost too many to keep track of. And while a lot of (justified) attention is given to the headline-grabbing features of 5.6 (memcached APIs! global transaction ids! improved PERFORMANCE_SCHEMA!), I’m often curious about the new features that don’t make as big a splash. I thought I would look at one such new feature – WorkLog #5217. I’m not telling you what this WorkLog is yet; test your knowledge of 5.6 features by seeing if you can figure it out from the following scenario. Imagine the following table data:
mysql> SELECT * FROM p; +------+------+ | a | b | +------+------+ | 1 | 1 | | 11 | 1 | | 21 | 1 | +------+------+ 3 rows in set (0.00 sec)
OK, try to write an UPDATE statement that[Read more...]
I have been working for a customer benchmarking insert performance on Amazon EC2, and I have some interesting results that I wanted to share. I used a nice and effective tool iiBench which has been developed by Tokutek. Though the “1 billion row insert challenge” for which this tool was originally built is long over, but still the tool serves well for benchmark purposes.
OK, let’s start off with the configuration details.
First of all let me describe the EC2 instance type that I used.
I chose m2.4xlarge instance as that’s the instance type with highest memory available, and memory is what really really matters.
High-Memory Quadruple Extra Large[Read more...]
While looking at partitioning I recently made a mistake which I guess can happen to others. Often this is due to not fully reading the documentation or scanning it too quickly and misunderstanding what’s being said.
So this post is to complain about the MySQL partitioning syntax and to warn others who may easily make the same mistake without realising.
First we probably need to ask why we are partitioning a table in the first place. The main reasons for this are I think:
In my case I wanted to do both. I had a several tables which store a large number of rows (batches of data) based on an incremental batch number. One of these tables was around 40 GB and had about 500,000,000 rows in it. When processing[Read more...]
CREATE TABLE message (
message_id BINGINT UNSIGNED PRIMARY KEY,
user_id INT UNSIGNED,
WordPress sites can get big. Really big. When you’re looking at a site of Cheezburger, Engadget or Techcrunch proportions, you get hundreds of comments per post, on dozens of posts per day, which adds up to millions of comments per year.
In order to keep your site running in top condition, you don’t want to be running queries against tables with lots of rarely accessed rows, which is what happens with most comments – after the post drops off the front page, readership drops, so the comments are viewed much less frequently. So, what we want to do is remove these old comments from the primary comment table, but keep them handy, for when people read the archives.
The idea of MySQL partitioning is that it splits tables up into multiple logical tablespaces, based on your criteria. Running a query on a single partition of a large[Read more...]
In Part 1, and Part 2 of this series, I presented some thoughts on partitioning. I heard some great feedback on why people use partitioning. Here, I present a flow chart that summarizes what I’ve learned. In summary: with TokuDB in the picture there’s almost no reason to use partitioning. Or I should say, there are almost always better (higher performing, more robust, lower maintenance) alternatives to partitioning.
In part one, I presented a very brief and particular view of partitioning. I covered what partitioning is, with hardly a mention of why one would use partitioning. In this post, I’ll talk about a few use cases often cited as justification for using partitions.
One use case for justifying partitions is that each partition can be placed on a separate disk to avoid spindle contention. I have to say that on this one, I agree with Kevin Burton, who makes the point that if you want to distribute I/O load across several disks, you can use a RAID configuration on the disks. In this case, he says that partitioning is not[Read more...]
At Kscope this year, I attended a half day in-depth session entitled Data Warehousing Performance Best Practices, given by Maria Colgan of Oracle. My impression, which was confirmed by folks in the Oracle world, is that she knows her way around the Oracle optimizer.
See part 1 for the introduction and talking about power and hardware. This part will go over the 2nd “P”, partitioning. Learning about Oracle’s partitioning has gotten me more interested in how MySQL’s partitioning works, and[Read more...]
One common question I get is how to use partitioning instead of MERGE tables. The process I use involves using stored procedures to create and drop partitions. This article will go over the stored procedures I use; special thanks to Roland Bouman for taking a look and giving great feedback to optimize this process.
First, a simple table, not partitioned (yet):
use test; DROP TABLE IF EXISTS my_part; CREATE TABLE IF NOT EXISTS my_part ( id int NOT NULL, creationDate datetime NOT NULL, PRIMARY KEY (id,creationDate) ) ENGINE=InnoDB;
In real, life there is more to the table than just
creationDate. The most important part is that the partitioned field(s) need to be part of the primary key.
Now, add the partition definition. This can be done in the[Read more...]
select c2, sum(s0.c1), max(c1) from t1 as s0 join t1 using (c1,c2) where c2 = 98818 group by c2;
TO_DAYS(). And depending on how you have partitioned your table, your queries are as fast as you expect them to be. However, there are cases where your query takes twice as long as it should, and of course this will not make you [Read more...]
mysqldumpcommands for backing up individual partitions of the tables in the current schema. The script is maintained as a snippet at MySQL Forge.
|Showing entries 1 to 30 of 86||Next 30 Older Entries|