Showing entries 1 to 10 of 14
4 Older Entries »
Displaying posts with tag: partition (reset)
archival using exchange partitions MySQL 5.6

If table is partitioned it makes it easy to maintain. Table has grown so huge and the backups are just keep running long then probably you need to think of archival or purge.

Purge the data if you don't want data from old partitions just by doing truncate or drop of those partitions which completes momentarily without locking the table for a long time

Archival can be done couple of ways.
We can take mysqldump (preferably from a slave) with a where condition to filter out the data you don't want to copy. And then import the dump file to archive database. check this link out for mysqldump with where clause .. and once data is copied, the old partition can be truncated.
http://www.electrictoolbox.com/mysqldump-selectively-dump-data/

But mysqldump is not the fastest option. it …

[Read more]
how to archive Terabytes of data in few seconds

If table is partitioned it makes it easy to maintain. Table has grown so huge and the backups are just keep running long then probably you need to think of archival or purge.

Purge the data if you don't want data from old partitions just by doing truncate or drop of those partitions which completes momentarily without locking the table for a long time

Archival can be done couple of ways.
We can take mysqldump (preferably from a slave) with a where condition to filter out the data you don't want to copy. And then import the dump file to archive database. check this link out for mysqldump with where clause .. and once data is copied, the old partition can be truncated.
http://www.electrictoolbox.com/mysqldump-selectively-dump-data/

But mysqldump is not the fastest option. it …

[Read more]
New Shard-Query features checked into SVN

I checked some updates to Shard-Query into SVN. Partitioning support has been extended for MySQL 5.6+ to ALL partitioning types.

This includes all previously unsupported types including RANGE LIST/COLUMNS partitioned tables that are partitioned over more than one column, and HASH/KEY/LINEAR variants as well. Shard-Query now exclusively uses the PARTITION hint for partition elimination instead of WHERE clauses in MySQL 5.6. For 5.5 and previous versions, support remains limited to LIST,RANGE, and LIST/RANGE COLUMNS over a single column.

The old mysql interface DAL has been replaced completely by the PDO DAL.

There is no major difference for end users except that you have to check that the return of the query() method is an object with the is_object() function instead of checking that it is a resource with the is_resource() function. I updated bin/run_query, which is the example application.

I …

[Read more]
MySQL Architect at Oracle

I have worked as an architect in the MySQL/NDB world for more than 20 years and I am still working at Oracle and I like it here. Given all the FUD spread about MySQL I thought it might be a good idea to spread the word about all the great things we're doing to MySQL at Oracle.

#1 We are working on improving modularity in MySQL code base
In the early days of MySQL the MySQL development had serious issues with its development model. It was a model designed for a small code base. I used to work at Ericsson which is developing telecom switches that have systems with tens of millions lines of code. Such large systems require modularity. The Ericsson switches was developed with modularity built into the programming language already since the 70's. Even with this modularity a second level of modularity was required. The learnings from this reengineering project that span over more than a decade has given me valuable insights …

[Read more]
On LVM: How to setup Volume Groups and Logical Volumes.

LVM (Logical Volume Management) is a very important tool to have in the toolkit of a MySQL DBA. It allows you to create and extend logical volumes on the fly. This allows me to, say, add another disk and extend a partition effortlessly. The other very important feature is the ability to take snapshots, that you can then use for backups. All in all its a must have tool. Hence, this guide will allow you to understand various terminologies associated with LVM, together with setting up LVM volumes and in a later part will also show you how to extend...

Data Warehousing Best Practices: Comparing Oracle to MySQL pt 2

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 I do hope that MySQL partitioning will develop to the level that Oracle partitioning does, because Oracle’s partitioning looks very nice (then again, that’s why it costs so much I guess).

Partition – …

[Read more]
Using MySQL Partitioning Instead of MERGE Tables

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 id and 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 CREATE statement, but I have found that it is easier for me to think …

[Read more]
Kontrollkit – new backup script is partition space aware

I’ve been wanting to write a backup script for a while now that does the following: reads the partition information for the directory that you are backing up into and computes the used/available/percentage-available space. Then it reads the total data size from the MySQL tables and ensures that you have enough space on disk (for [...]

InnoDB's tablespace ids and Partitions

There are times when what you have is a partially running database and a bunch of backup innodb tablespace files (the .ibd files). If you're using innodb_file_per_table, then you have a separate .ibd file for each InnoDB table.

Now, you have your running database with a bunch of tables, and you want to replace some of them with the backup .ibd files. According to the MySQL docs, you'd do this:

  1. ALTER TABLE foo DISCARD TABLESPACE; (this deletes the current .ibd file)
  2. copy the old .ibd file into your database directory
  3. ALTER TABLE foo IMPORT TABLESPACE;

Assuming your .ibd file was from the same database and you did not drop the table and recreate it sometime between when you made the backup .ibd and now, this should work. Except... if …

[Read more]
Intelligent user-controlled partitioning and writing distribution-aware NDB API Applications

Default partitioning

By default, Cluster will partition based on primary key

When adding rows to a table that’s using MySQL Cluster as the storage engine, each row is assigned to a partition where that partition is mastered by a particular data node in the Cluster. The best performance comes when all of the data required to satisfy a transaction is held within a single partition so that it can be satisfied within  a single data node rather than being bounced back and forth between multiple nodes where  extra latency will be introduced.

By default, Cluster partions the data by hashing the primary key. This is not always optimal.

For example, if we have 2 tables, the first using a single-column primary key (sub_id) and the second using a composite key (sub_id, service_name)…

mysql> …
[Read more]
Showing entries 1 to 10 of 14
4 Older Entries »