sorted by: recent | see : popular

Content Tagged with and + MySQL

Today Sun announced release of X4540 Open Storage server (a.k.a Thor). We were fortunate to get early access to Thor to certify Amanda Enterprise and Zmanda Recovery Manager (ZRM) for MySQL. Both solutions are optimized for backing up to disk, are already certified with Solaris 10, and leverage capabilities of ZFS. So it made perfect sense to certify them on Thor, effectively creating a high-performance and yet relatively inexpensive backup appliance.

The unit we were using is powered by 8 CPUs operating at 2300 MHz and provides 48 SATA drives with total capacity close to 50TB in 4U enclosure. That is good packing; a typical EMC unit with so much capacity will take the whole rack.

The two boot drives configured as a mirror were running UFS. To get capacity for backups, we easily created Zpool with 12 drives and 6 spares in RAIDZ2 configuration. That gave us 7 TB of capacity for backups with very high level of protection against drive failure:

bash-3.00# zfs list
NAME USED AVAIL REFER MOUNTPOINT
zmanda 203K 7.13T 49.0K /zmanda
zmanda/vtapes 49.0K 7.13T 49.0K /var/lib/amanda/vtapes

While installing ZRM and Amanda Enterprise on Thor, we found that a couple of Solaris packages that we require were missing, and our coverage of dependencies in documentation was not perfect. Luckily, that was the only hiccup. After fixing the dependencies, the rest of installation and configuration went smoothly.

This screenshot shows part of a summary report about backup of five Solaris clients to Thor over 1 Gb network. Each client had about 100 MB of data. In that test we forced full backup on each run, with each client pushing data at 60 MB/s. That is not bad at all considering that you can get only 20-25 MB/s for a typical LTO-2 tape drive.

The X4540 Open Storage server is an excellent choice for creating your own backup to disk appliance. It provides high performance and very large capacity. One key feature is how easy it is to manage all that capacity with ZFS.

Special thanks to Sun?s Menlo Park and Colorado interoperability teams who helped us to get started and advised on considerations in configuring ZFS for backup to disk.
———
Dmitri Joukovski

For many MySQL implementations scalability is a crucial requirement. Scaling on various dimensions: Size, Performance, Cost and DBA-Stress-Level.

While several factors impact the scalability of the database, underlying storage probably has the highest impact. You need to make sure that the storage will fill up the buffers fast enough to keep queries happy, and acknowledge the writes fast enough to keep transactions happy. You need to make sure that you can keep growing the available storage as your database grows - MySQL databases are particularly prone to collect more and more data. You also need to make sure that maintenance tasks such as backup, cloning, and application testing will scale gracefully with the size of your database.

Storage systems from NetApp are a great choice when designing for MySQL scalability. Administrators can expand storage on the go (using FlexVol) eliminating the need for over-provisioning. NetApp’s ability to create snapshots in a highly storage efficient manner is particularly important for scaling the maintenance tasks. You can quickly clone your MySQL database and use it to test and deploy new applications. Most importantly, this snapshot capability enables fast and scalable backups.

As I had indicated before, we have been working with the engineering team at NetApp to create highly scalable backup and archiving solutions for MySQL. Today we formally announced this joint work. Our MySQL backup product, Zmanda Recovery Manager (ZRM) for MySQL with NetApp SnapShot Manager, meets two crucial design goals for scaling MySQL backups: least impact on the live production applications and quick point-in-time recovery. Database snapshots created by ZRM can also act as source for NetApp SnapMirror software, creating a highly scalable disaster recovery solution.

With your MySQL databases stored on a NetApp filer and protected by Zmanda, you can scale up while reducing your stress level.

MySQL databases are increasingly used by high volume, high transaction applications
that support businesses running full throttle 24-hours a day, seven days a week.
Backup and recovery operations need to be conducted in such as way that is non-disruptive
to users and applications. The latest release of Zmanda Recovery Manager (ZRM) 2.2 provides
Continuous Data Protection (CDP) as well as instant point-in-time recovery for MySQL.
In this webinar, we will demonstrate the latest ZRM enhancements so that you can use ZRM
to build a robust, flexible, and easy to use backup and recovery solution.

When: Thursday May 29, 2008, 10:00am Pacific / 1:00pm Eastern

Ask any DBA what are his top priorities for backup of MySQL. Chances are that hot backup and recovery to a specific point-in-time (or transaction) will be on top of everybody?s list.

The recovery to any point-in-time has been always an ultimate goal for data protection. Traditional backup solutions allow recovering only to a point in time when the last backup took place, e.g. last night. A lot of new data could be created and lost since that last backup. That is why in the last several years many vendors have been working on Continuous Data Protection (CDP).

Storage Networking Industry Association (SNIA) defines Continuous Data Protection (CDP) as “a class of mechanisms that continuously capture or track data modifications enabling recovery to previous points in time”.

ZRM 2.2 enables CDP by using LVM, VxFS, VSS, ZFS and NetApp snapshots in conjunction with MySQL transaction logs. When doing a recovery to a particular point in time, ZRM reads data from the snapshot and then replays MySQL transaction log from that point forward. This enables almost instantaneous point-in-time recovery.

For each point in time T, ZRM creates a snapshot that includes a MySQL binary log. To recover MySQL database to a specific Recovery Point Objective (RPO) between T2 and T3, ZRM reads data from snapshot T2 and replays transactions from binlog T3 up to RPO providing the user with point-in-time recovery. To enable instantaneous recovery snapshots are stored where the database files are, for example, on the same NetApp filer.

Since real life recoveries are performed under stressful conditions, we designed our product to simplify your life and ensure successful recovery:

• With a single click you can initiate point-in-time recovery that reads data from the snapshot and replays transactions so you get your database back in time.
• Our unified recovery mechanism allows the very same recovery workflow not just for snapshots, but for all other backup methods such as logical and raw backup. The unified recovery works even with third party data extraction utilities for MySQL such as InnoDB HotBackup.
• ZRM allows recovery to any point in time for multiple MySQL servers in the organization. MySQL servers could have different storage engines and run on different operating systems and all backup and recovery operations are centrally managed by ZRM.

Some CDP solutions capture all writes to the database effectively duplicating what a binary log is designed to do. Why re-invent the wheel and waste computing resources? ZRM is integrated with MySQL on a deeper level than any other backup solution. That allows us to provide very efficient CDP solution with almost instantaneous point-in-time recovery.

——
Dmitri Joukovski

Final slides from keynote delivered this morning at the MySQL user conference. Topic was protecting live MySQL databases.

(Slides render well in both OpenOffice and PowerPoint)

No, this is not a blog about Computational Fluid Dynamics - my least favorite subject in college. This is about a more exciting (sorry mechanical engineers!) CFD: Customer Facing Data. This is the data that is typically available on the website of an organization that their customers interact with. CFD can range all the way from profiles of users on a social networking site such as Facebook to the customer information database of an e-commerce company such as Travelocity.

CFD represents today’s data protection challenge. Probably the biggest challenge while planning a backup solution for CFD is that it is very hard to figure out what to plan for. You might be starting with a very small database which might grow much more rapidly than what you think. If the data can be segmented based on users or some other characteristic, then you will find that your databases may scale-out instead of scale-up. Also, rate of change can be very rapid, sometimes with lots of small changes (e.g. tags) or sometimes with big data changes (e.g. addition of user generated media content), very similar to the rather unpredictable viral growth pattern of the Internet.

Many organizations are keen to save and analyze behavior of users as they interact with CFD. This metadata can itself pose a data protection challenge of its own, since it may change very rapidly, while the actual data is not changing (e.g. tracking of customer intelligence metadata about which prospects downloaded which whitepapers from your corporate website, so that you can determine the best marketing & sales approach for these prospects).

MySQL is the DBMS of choice for CFD. Some skeptical press and analysts have said a few times that MySQL is not being deployed at the back-end of the enterprise (financials, billing etc.), but only on the web-tier. The implicit (sometimes explicit) implication being that web-tier applications are less important for the organization. I think this analysis fails to realize the actual importance of the data being stored in web-tier applications. Most of this is CFD, loss of which will cause huge pain and costs in the form of lost revenues, customers and reputation.

From its very inception, Zmanda has been focused on technologies powering CFD (MySQL, LAMP stack, and now increasingly Solaris). We have dug deeper than anyone else in understanding the needs of protecting this crucial part of any organization and very rapidly delivered products to address these needs. We are the data protection company for CFD. While we do protect back-end applications and platforms, our technology and business focus remains CFD.

I will be talking about deploying radically simple backup solutions for CFD on Thursday (April 17th, 10AM) at the MySQL user conference. I am in the unenviable position of making a Backup presentation sound interesting after the Facebook keynote (”A Match Made in Heaven? The Social Graph and the Database”). Well, I guess the point that conference organizers are trying to make is that if you have a radically simple MySQL backup and recovery solution, you will have more time to spend on Facebook!

Speaking of analysts, I recently read an analyst report which indicates that, on average, salaries offered to MySQL DBAs can be up to 40% lower than those offered to Oracle or DB2 DBAs. This ironically is considered one of the barriers for entry of MySQL in some environments. Per this report: higher paid database personnel have vested interest to keep MySQL out. Another claim is that somehow the lower salary to MySQL DBAs reflects the relative importance of the MySQL powered application for the business. Well if you are a MySQL DBA, you will do well to make your management realize that you are the keeper of their Customer Facing Data. Any business deploys its best resources for customer facing activities. Hopefully this will start reflecting on your paycheck soon.

If you would like to talk about your data protection challenges, or your salary as a MySQL DBA, stop by our booth #307 at the MySQL user conference.

Worried about Backup and Recovery of your MySQL Databases? MySQL Backup school from Zmanda provides hands on and in depth training on Backup and Recovery of MySQL. Just sign up and show up with your laptop. More information available here.

Zmanda has introduced a new version of Zmanda Recovery Manager (ZRM) which adds quite a few capabilities for MySQL users. Version 2.1 includes enhanced snapshot support that enables backup without application downtime or interruption to online data access. Version 2.1 also has global management of backups so that you can manage all your MySQL backup jobs from a single graphical console. You can now run the entire ZRM solution on Solaris as well as on Linux.

Join us for an overview and a live demo of ZRM 2.1 on Thursday 3/20 at 10am PST. Click here to register.

Brian “Krow” Aker’s Idle Thoughts - Solaris, Linux, it is GNU folks…

Brian hits the nail on the head… The way you get a usable system is install all the GNU tools.

This is how I go from fresh Ubuntu install to building MySQL:

apt-get build-dep mysql-server

apt-get install bison

(now go and build).

(and i could do this graphically if I wasn’t so stuck in my ways)

For Solaris? umm… there was a point where I could get Solaris to apply security updates and Brian could get all the stuff needed to build a MySQL Server. Together we had the knowledge needed… but neither was as trivial as with Ubuntu and combining knowledge was too much - I just gave up and went on to more productive things.

Even on an existing Solaris system… getting your PATH right is a trip into some weird fantasy land seemingly designed to annoy you. No doubt this all made some sense back in the day… but now it just causes pain when all you want to do is compile your program, find the bug and fix it.

When I started at SGI several years ago, what’s the first thing I did? Went and installed all the GNU packages. IRIX is a lot nicer then.

Same with MacOS X - the first thing you do is go and install darwinports or fink and get a remotely usable system.

With Windows, it varies - but the shell is so outrageously shit you need cygwin just for bash, you need either emacs or VisualStudio to get an editor you don’t want to kill, Firefox for a web browser that works etc etc etc. The fact that the Windows packing system just blows chunks makes it the most painful experience of all.

So even if you’ve heard rave things about the debugger in VisualStudio - actually getting a Windows install to the state where you can run the debugger takes hours. Click click click, upgrade, yes, install, swap disks, upgrade, upgrade, wait, reboot, install manually, install manually, install manually. ick.

Project Indiana is possibly the saviour of Solaris. Default userland is gnu, default shell is bash. Starts to make it feel like home. Just as when Solaris started shipping GNOME made it feel more homely.

Solaris comes with a version of vi that is old enough to drink in bars. Project Indiana realises that a drunk editor isn’t a good idea and ships something sensible.
The BSDs get a lot of things right. Sane userland that is familiar to people. Jumping onto a FreeBSD box is remarkably easy.

The typical thing said by people is “backwards compatibility” and all that… basically so that everyone can run their apps from 1985 and not change a thing. Worthy goal. Of course, 1985 does not need to be the default environment in 2008.

There is a standard for the unixy way of things: it’s Linux with GNU tools in userland.

Just as Windows set the big standard for having a kind of usable GUI (the Mac did it better, but Windows got the numbers) - and to get people to use Linux on the Desktop we needed to get it to a stage where those people are comfortable.

If you want your UNIXy system to be used by anybody today, you need to have it be comfortable for Linux people.

On the other hand though, Ubuntu is still the best desktop I’ve ever used and am rather happy with it (no matter how much i bitch and moan about certain things being obviously broken).

(and no, I’m not switching my desktop to any Solaris variant - but wholeheartedly look forward to the days when maintaing software than runs on Solaris is a heck of a lot easier because Solaris becomes less annoying).

One more point: OSX and Solaris are the only remotely proprietary UNIXes left. Everybody else is either dead or doesn’t know it yet. Solaris is nearly all free (AFAIK there’s still just some binary only drivers around… which sucks… but these things can take time, so that’s okay) and OSX has parts which are (sometimes seemingly dependent on phase of the moon) free-ish. So really, OSX is the one last hold out of the largely proprietary UNIX world. It’s a fascinating thing to think about…. freedom wins.

(and this no doubt goes on far too long and incoherently…. but that’s because of long days and late nights because of upcoming really cool stuff which I’ll blog about later)

This is the third of a five-part series on database management system choices. For the first post in the series, please click here.

High-end OLTP relational database management system vendors try to offer one-stop shopping for almost all data management needs. But as I noted in my prior post, their product category is facing two major competitive threats. One comes from specialty data warehouse database management system products. I’ve covered those extensively in this blog, with key takeaways including:

• Specialty data warehouse products offer huge cost advantages versus less targeted DBMS. This applies to purchase/maintenance and administrative costs alike. And it’s true even when the general-purposed DBMS boast data warehousing features such as star indexes, bitmap indexes, or sophisticated optimizers.
• The larger the database, the bigger the difference. It’s almost inconceivable to use Oracle for a 100+ terabyte data warehouse. But if you only have 5 terabytes, Oracle is a perfectly viable ? albeit annoying and costly ? alternative.
• Most specialty data warehouse products have a shared-nothing architecture. Smaller parts are cheaper per unit of capacity. Hence shared nothing/grid architectures are inherently cheaper, at least in theory. In data warehousing, that theoretical possibility has long been made practical.
• Specialty data warehouse products with row-based architectures are commonly sold in appliance formats. In particular, this is true of Teradata, Netezza, DATAllegro, and Greenplum. One reason is that they’re optimized to stream data off of disk fairly sequentially, as opposed to relying on random seeks.
• Specialty data warehouse products with columnar architectures are commonly available in software-only formats. Even so, Vertica and ParAccel also boast appliance deals, with HP and Sun respectively.
• There is tremendous technical diversity and differentiation in the specialty data warehouse system market.

Let me expand on that last point. Different features may or may not be important to you, depending on whether your precise application needs include:

• Absolute scalability. Teradata, DATAllegro, and SAS have customers with multi-hundred terabyte data warehouses (user data). Netezza is headed for that range soon. Many other vendors effectively top out in the tens of terabytes right now.
• Pre-projection. Columnar data warehouse products only retrieve the columns needed for a particular query. Depending on how wide your rows are, that can be a huge advantage. Through materialized views ? and in Netezza’s case a hardware assist ? row-based vendors have varying degrees of effectiveness at accomplishing similar things.
• Pinpoint queries. For some data warehouse applications (e.g., in call centers), it is important to look up specific records at high speed. Conventional-but-much-faster row-based systems like Teradata, Greenplum, or DATAllegro can usually meet these needs. Not all columnar vendors would do as well.
• Data scoring. Similarly, data scoring ? e.g., in applications such on-the-fly call center offer optimization — tends to involve entire rows of data. Row-based systems may outperform columnar ones for that kind of use.
• Partitioning. A major virtue of most specialty data warehouse products is that they give pretty good performance without a lot of pre-tuning. Even so, range partitioning can ensure that data with particular field values is concentrated together, meaning that less data total needs to come off of disk to satisfy certain queries. A major use of this capability is in applications where data with recent dates is retrieved or analyzed more intensely than older information ? and there are a lot of those.
• Load speed. Row-based data warehouse managers usually have fast load speeds, for bulk loads and trickle feeds alike. Columnar vendors differ in how fast they are at which kinds of loads.
• Concurrency. If you need your data warehouse to support a large and diverse set of concurrent reports and queries, Teradata will definitely do the job for you. Netezza and DATAllegro have also cleaned up bottlenecks through a few revisions each. Newer products might or might not do as well.
• Schema support. Some data warehouse products are optimized for pure star schemas with single fact tables, or schemas only a little more complex than that. Others are more broadly applicable. For many data warehouses, the simple schemas suffice.
• Compressibility. Every serious data warehouse product offers at least 2-5X compression. (Columnar systems are somewhat ahead of row-based ones.) Some spike up into double digits, especially for certain kinds of columns. Depending on the nature of your data, compression may or may not be a huge differentiating factor.
• Transparency/compatibility. If you need your data warehouse to run exactly the SQL that SQL Server does, you might want to talk to ParAccel. If you want to be compatible with MySQL, Infobright Brighthouse is the ticket.
• MDX or ?Model by? instead of standard SQL.

One more complication: There also are a number of products that accelerate data analysis outside the DBMS, usually in a memory-centric way. Ones I’ve written about in the past include QlikView, SAP BI Accelerator, Applix, and the whole category of complex event/stream processing.

The complete series

• Part 1: Database management system choices ? overview
• Part 2: Database management system choices ? 4 categories of relational
• Part 3: Database management system choices ? relational data warehouse
• Part 4: Database management system choices ? mid-range-relational
• Part 5: Database management system choices ? beyond relational

Please subscribe to our feed!

This is the second of a five-part series on database management system choices. For the first post in the series, please click here.

For the most part, relational database management systems divide into four major classes:

• High-end OLTP (OnLine Transaction Processing) relational DBMS. Oracle is the flagship for this category, followed by DB2.
• Specialty data warehouse DBMS. Teradata is the leader here, followed by Netezza, DATAllegro, ParAccel, Vertica, Infobright, Greenplum, Kognitio, Sybase IQ, and a host of others.
• Mid-range relational database management systems. Most of the contenders here fall into one or more of three categories: Open-source-based relational DBMS (MySQL, PostgreSQL, EnterpriseDB); reseller-focused relational DBMS (Progress OpenEdge, Pervasive PSQL); or crippled ?editions? of high-end systems. Microsoft SQL Server was once a clear mid-range system, but now is better classified as high-end OLTP.
• Embedded relational database management systems. The leader of this category is Sybase’s SQL Anywhere. Also significant are memory-centric products Oracle TimesTen and solidDB.

High-end OLTP relational database management systems are complex and mature products, differentiated mainly on reliability, availability, performance, scalability, security, license cost, maintenance cost, programming/administration cost, and datatype support. All except the last point can be evaluated pretty effectively on an outside-in basis. That is, is suffices to look at proven results, without worrying a whole lot about architecture or product futures. Just remember that a lot of the high-end features come through extra-cost add-ins, which need to be included in any evaluation.

That said — most evaluations of high-end OLTP RDBMS are pretty moot anyway. Large enterprises usually have one favored vendor, who provides a significant quantity discount on license and maintenance costs. Using the OLTP RDBMS you already have also usually leads to significant efficiencies in administration expense. Thus, the high-end OLTP is the one part of the DBMS market that truly is as mature as conventional wisdom would have us believe ? at least, that is, until something like H-Store comes to fruition.

Even so, high-end relational OLTP DBMS vendors face two major competitive challenges, which are taking significant share of new applications within those vendors’ installed bases. For more about those, please see the next two posts in this series.

The complete series

• Part 1: Database management system choices ? overview
• Part 2: Database management system choices ? 4 categories of relational
• Part 3: Database management system choices ? relational data warehouse
• Part 4: Database management system choices ? mid-range-relational
• Part 5: Database management system choices ? beyond relational

Please subscribe to our feed!

As I’ve previously noted:

• Infobright is about to make more noise about its MySQL-based data warehouse software, Brighthouse.
• Brighthouse has some very interesting technical features.
• A Sun/Infobright partnership would make a lot of sense.

Talking with Infobright today, I was again struck by how close their relationship with MySQL (the company is). Stay tuned.

Please subscribe to our feed!

Congratulations to Marten and team on Sun’s acquisition of MySQL today! This is a billion dollar stamp of approval on the importance of the LAMP stack. This also gives another strong backing to the business model which involves giving one’s software away for free to up to 99+% of users!

This announcement comes at an interesting time for Zmanda. Just last week we announced support for the Solaris platform for our Amanda Enterprise product line. With our industry leading MySQL backup solution, we now have full coverage of operating systems and database software from the new Sun!

A great day for open source software and business of open source software!

We presented a webinar along with MySQL last week. The topic was MySQL Backup from the perspective of an Oracle DBA. We got some really good questions at the end of the webinar. Here is a transcript of Q&A:

Q from Trey: Will this Webinar be available online for viewing later?

A from MySQL: Yes. It will be on our site in a few days at http://www.mysql.com/news-and-events/on-demand-webinars/

Q from Randy: Is a copy of presentation available on web? Where?

A from Zmanda : slides are available on Zmanda Network: http://network.zmanda.com/

Q from Jing: Converting database from oracle to MySQL, any limitation regards the version compatibility?

A from MySQL: We provide a free GUI migration tool that helps migrate Oracle data objects to MySQL. For a complete and very detailed discussion on the subject, download our white paper on the subject at http://www.mysql.com/why-mysql/white-papers/mysql_wp_oracle2mysql.php

Q from Julien: Is MySQL supported on NetApp?

A from MySQL: Yes

Q from Julien: For mysql on NetApp, which protocols are supported? nfs? iscsi? cifs?

A from MySQL: I believe the key protocols are supported.

Q from Haris: How does ZRM integrate with NetApp

A from Zmanda: ZRM takes advantage of snapshots provided by Data ONTAP OS in NetApp filers.

Q from Jim: Will the “new non-blocking backup utility” for MySQL for hot backup be free? or an extra cost option? Thank you.

A from MySQL: At this time, the core backup utility is planned to be GPL and in the community server

Q from Neil: How much does ZRM cost?

A from Zmanda: Annual subscription starts from $200 per MySQL server. We provide subscriptions with three levels of support — Basic (email only), Standard (business hrs phone and email) and Premium (7 X 24). Pricing is available at www.zmanda.com/pricing.html

Q from Franck: so the licensing is per MySQL Server

A from Zmanda: Yes, subscription is per MySQL server and you can backup as many databases within that server as you have.

Q from Isaac: Is the community edition of Zmanda fully functional or somehow restricted?

A from Zmanda: The Community Edition is fully functional. However, only the Enterprise version provides web-based Management Console that simplifies all operations. It also provides log analyzer that simplifies point-in-time recovery. To see additional details, please check http://www.zmanda.com/zrm-mysql-enterprise.html

Q from Pavel: Does Zmanda GUI manager comes for free???

A from Zmanda: No. Only the Enterprise (which requires annual subscription) version provides a web based console.

Q from Tim: Could you provide onsite support if we need that?

A from Zmanda: Yes. We can provide onsite support and training if needed.

Q from Haijun: Does ZRM include InnoDB online backup?

A from Zmanda: Not in the current release. It is planned for a future release

Q from Alicia: Any idea how long before the release of a ZRM version that supports hot backup of InnoDB databases?

A from Zmanda: 3-6 months (likely next release)

Q from Mike: mysqldump cmdline for non-blocking with innodb?

A from Zmanda: mysqldump –single-transaction is non-blocking for transaction storage engines such as InnoDB

Q from Kevin: Now that Oracle owns the rights for InnoDB, what is it’s future. Will Oracle charge for InnoDB? Is there a replacement for Innodb from MySQL

A from MySQL: For MySQL, it’s business as usual so no changes from a support standpoint. We do have other transactional engines available - NDB (cluster) and our new Falcon transaction engine (about to enter beta). Also we have SolidDB and PBXT, - external engines

Q from