PlanetMysql.ru — информация о СУБД MySQL

If you are running your MySQL databases on the Amazon EC2 compute cloud, Zmanda Recovery Manager (ZRM) for MySQL can perform fast full backups of these databases by using Elastic Block Store (EBS) Snapshots. ZRM takes only a momentary read lock on the MySQL database during the creation of the snapshot, in order to ensure consistency of the backed up database archive. MySQL Backups using Amazon EBS snapshots are differential backups, meaning that only the blocks that have changed since your last full backup (via EBS snapshot) will be saved. For example, if you have a database with 100 GBs of data, but only 5 GBs of data has changed since your last snapshot, only the 5 additional GBs of snapshot data will be stored back to Amazon S3 during the current full backup run.

ZRM automatically deletes EBS snapshots (containing full backups of MySQL) according to the configured retention policy. Just like other snapshot based full backups, ZRM intelligently correlates EBS Snapshots with incremental backups using MySQL logs, enabling you to recover your MySQL instances running on EC2 to any point in time.

Backups made using EBS snapshots can be recovered on the original EC2 instance or on a new EC2 instance. This also provides a quick and convenient mechanism to instantiate new MySQL database servers based on the database state from a desired point-in-time.

ZRM can run on the same EC2 instance as the MySQL database. On the other hand, if you have multiple EC2 instances with MySQL databases, you can run ZRM on one centralized EC2 instance dedicated for backup purposes. In this case, backup configuration and management for all MySQL databases is performed via Zmanda Management Console from this centralized backup server.

We have created an Amazon Machine Image (AMI) with ZRM pre-configured. This makes implementation of a MySQL backup solution on the cloud even simpler. We have used the “EC2 Small Instance” - which is powerful enough to backup most MySQL workloads in the cloud. This also makes it a very cost-effective option. This AMI is available to all ZRM customers, as part of the ZRM Enterprise subscription. You will need to create your own Amazon EC2 account, and pay standard per hour price to Amazon to run an instance based on this AMI. Note that you can configure your backup server instance to run only during the backup window. So, if you are backing up your databases once a week, and your backups takes less than an hour, then you can have this instance up only during that hour. EC2 pricing is per instance-hour consumed from the time an instance is launched until it is terminated. Each partial instance-hour consumed will be billed as a full hour. In addition to the EC2 compute capacity, you will pay standard storage charges for Amazon S3 (to store EBS Snapshots).

Join us on January 28th for a webinar on MySQL Backups (hosted by Sun/MySQL). Along with an introduction to Zmanda Recovery Manager, we will also discuss backing up MySQL applications on the cloud, and demonstrate the new ZRM AMI.

PlanetMySQL Voting: Vote UP / Vote DOWN

I was asked this question recently “I am wanting to create a simple MySQL database consisting of 5 tables”?

While it’s easy to tell people to RTFM, the question does warrant an answer for the MySQL beginner to provide a more specific guidance as to where to start, and what to do. As a expert in MySQL it’s easy to forget how you would describe what to do. Here are my tips to getting started.

Step 1. Download the MySQL 5.1 software for your platform (e.g. Linux, Windows, Mac etc) from MySQL 5.1 Downloads. There are many different versions of MySQL, MySQL 5.1 is the current production version.

Step 2. You will need to install the MySQL software. The MySQL reference manual is the place to go, Chapter 2 describes installing MySQL. You can also download a copy of the manual in various different formats at MySQL Documentation. This is also valuable for the time when the documentation may be be unavailable online.

Step 3. Download a GUI tool to help you in the design of your first MySQL Tables. There are a number of products available to do this, the MySQL Query Browser and WebYog are just two examples. If your bold, you can use the mysql client command line tool and use the CREATE TABLE command to create your table structures.

MySQL by itself is ineffective for producing a client facing end result unless you have an application purpose and therefore a general application to access the data in MySQL. Using a LAMP/WAMP stack is a good place to start. XAMPP is a good cross platform program that gives you MySQL and a PHP technology stack. You also get PhpMyAdmin included with XAMPP which is a good web based design tool. I don’t mention earlier because it needs a running php/apache/mysql environment. If you elect to start with this stack, then you don’t need to install any GUI tools.

Finally, there a wealth of knowledge, not at least the MySQL Forums and the #mysql channel on irc.freenode.net which can be good places to get free beginner information.

PlanetMySQL Voting: Vote UP / Vote DOWN

I was asked this question recently “I am wanting to create a simple MySQL database consisting of 5 tables”?

While it’s easy to tell people to RTFM, the question does warrant an answer for the MySQL beginner to provide a more specific guidance as to where to start, and what to do. As a expert in MySQL it’s easy to forget how you would describe what to do. Here are my tips to getting started.

Step 1. Download the MySQL 5.1 software for your platform (e.g. Linux, Windows, Mac etc) from MySQL 5.1 Downloads. There are many different versions of MySQL, MySQL 5.1 is the current production version.

Step 2. You will need to install the MySQL software. The MySQL reference manual is the place to go, Chapter 2 describes installing MySQL. You can also download a copy of the manual in various different formats at MySQL Documentation. This is also valuable for the time when the documentation may be be unavailable online.

Step 3. Download a GUI tool to help you in the design of your first MySQL Tables. There are a number of products available to do this, the MySQL Query Browser and WebYog are just two examples. If your bold, you can use the mysql client command line tool and use the CREATE TABLE command to create your table structures.

MySQL by itself is ineffective for producing a client facing end result unless you have an application purpose and therefore a general application to access the data in MySQL. Using a LAMP/WAMP stack is a good place to start. XAMPP is a good cross platform program that gives you MySQL and a PHP technology stack. You also get PhpMyAdmin included with XAMPP which is a good web based design tool. I don’t mention earlier because it needs a running php/apache/mysql environment. If you elect to start with this stack, then you don’t need to install any GUI tools.

Finally, there a wealth of knowledge, not at least the MySQL Forums and the #mysql channel on irc.freenode.net which can be good places to get free beginner information.

PlanetMySQL Voting: Vote UP / Vote DOWN

Recently I had a chance to take a look at Redis project, which is semi-persistent in memory database with idea somethat similar to memcache but richer feature set.

Redis has simple single process event driven design, which means it does not have to deal with any locks which is performance killer for a lot of applications. This however limits it scalability to single core. Still with 100K+ operations a second this single core performance will be good enough for many applications. Also nothing stops you from running many Redis instance on single server to get advantage of multiple cores.

I call Redis semi-persistent because it does not store the data on disk immediately but rather dumps its all database every so often – you have a choice of configuring time and number of updates between database dumps. Because dump is basically serial write Redis does not an expensive IO subsystem. Also because this dump is background it does not affect read/write performance to the database which is in memory. In the tests I’ve done I’ve seen Redis doing writes some 4MB/sec for probably 50% of test duration where Innodb had to write 50MB/sec for about third of throughput and doing a lot of random IO as it was doing it. This is among other things because Innodb has to flush full 16K pages while doing flush.

The background flush in Redis is designed the following way – Redis process forks and justs dumps the database it has in the background. Unix copy on write takes care of getting another copy of pages as they are modified. This keeps overhead rather low. The database is dumped in temporary file which is renamed only after fsync which means if you crash during the dump you simply discard partial file.

I also liked full pipelining support in the protocol – you can send multiple commands at once – any commands and redis server will process them in order and returns results to you. This not only allows for multi-get and multi-set but any of batches of commands being submitted. The API support for this is relatively week but the features are there.

When it comes to data types – Redis supports simple key-value storage just as memcache but it also adds support for Lists, which are similar to the linked list data type you would have as well as sets which allow to store sets of strings with support of various set operations.

I kind of miss support for something like associative array/hash/map in the data types but I guess there is nothing in architecture which would stop it from being added later.

Redis also has support for “databases” which are basically key spaces inside the server. This should allow for using server by different applications, different versions, testing or some other features. Though you’ve got to be careful with these – there is only simple per instance password based authentication in place, so if application can talk to the instance it can access all databases.

I am also a bit surprised why databases are numbered instead of named. Naming the databases would make it more simple to avoid unwanted conflicts etc.

Redis also supports master/slave replication out of the box and it is extremely simple. You just specify from which node to replicate and this is it. It is even more simple than with MySQL as you do not need to deal with snapshot or binary log position. Replication is asynchronous and low overhead – redis will perform the database dump and store the commands on the data since the start of the process. Slave can get the data (which is basically set of commands to populate database itself) and when get the data from the master as it comes. I did not benchmark the replication capacity but I’d expect it to be close to 100K of writes/sec the single instance can handle.

The benchmarks I’ve done were for applications which is very update intensive with updates being pretty much random single row updates which are hard to batch. With MySQL/Innodb I got server being able to handle some 30.000 updates/sec on 16 core server with replication being able to handle 10.000 updates/sec. This was using about 5 cores so you could probably get 4 MySQL instances on this server and get up to 100K updates/sec with up to 40K updates/sec being able to replicate.

With Redis I got about 3 times more updates/sec – close to 100.000 updates/sec with about 1.5 core being used. I have not tried running multiple instances and I’m not sure the network and TCP stack would scale linearly in this case but anyway we’re speaking about hundreds of thousands of updates/sec.

I think Redis can be great piece of architecture for number of applications. You can use it as the database or as cache (it supports data expiration too)

I have not benchmarked it against memcache in terms of performance and memory usage. This may be another project to look at.

---

Entry posted by peter | No comment

Add to: | | | |

PlanetMySQL Voting: Vote UP / Vote DOWN

More good news around the WebStack team: Kai 'Oswald' Seider, of Apache Friends and XAMPP fame, has joined the team - see Kai's post (German, mock-english). XAMPP is one of the (the?) most popular WAMP Distros (see Google Trend), and Kai will now be able to work full-time on it. I talked with Kai a few months ago and enjoyed our phone chat a lot but, unfortunately, I was away when he visited Santa Clara recently (photo gallery). One of the topics during the trip was how to leverage the WebStack and the XAMPP efforts; stay tuned for news on that.

Welcome to the team, Kai!

PlanetMySQL Voting: Vote UP / Vote DOWN

I see that Kaj mentioned the (GlassFish) WebStack when telling the story of the MySQL.Com Outage. One of the main benefits of the WebStack is that all the pieces work very well together, so that was one less thing to worry about; the electrical woes were stressful enough...