This is the second in a series on what's seriously limiting MySQL in certain circumstances (links: part 1). In the first part, I wrote about single-threaded replication. Upstream from the replicas is the primary, which enables replication by writing a so-called "binary log" of events that modify data in the server. The binary log is a real limitation in MySQL.

The binary log is necessary not only for replication, but for point-in-time recovery, too. Given a backup and the corresponding binary log position, you can replay the binary log and roll forward the state of your server to a desired point in time.

But enabling the binary log reduces MySQL's performance dramatically. It is not the logging itself that's the problem -- writing the log is usually not much additional work. It's ensuring consistency and …

During the MySQL Users Conference, Edward Screven did a keynote presentation that made many of us feel warm and fuzzy about Oracle's future plans for MySQL. If you advance 16m 25s into the presentation, it even gives something to rejoice the MySQL Enterprise customers: "Backup is now included". He didn't say much more after that. Asking around at the conference the days following this announcement, I couldn't get a straight answer about when and how would it be available for existing customers.

Now, 6 months later (give or take a couple of weeks), the MySQL Enterprise Features page has no signs of the now included MySQL Enterprise Backup (the utility previously known as InnoDB Hot Backup) and there has been no other news supporting Edward's announcement …

If you frequently delete rows (or update rows with variable-length data types), you can end up with a lot of wasted space in your data file(s), similar to filesystem fragmentation.

If you’re not using the innodb_file_per_table option, the only thing you can do about it is export and import the database, a time-and-disk-intensive procedure.

But if you are using innodb_file_per_table, you can identify and reclaim this space!

Prior to 5.1.21, the free space counter is available from the table_comment column of information_schema.tables. Here is some SQL to identify tables with at least 100M (actually 97.65M) of free space:

SELECT table_schema, table_name, table_comment FROM information_schema.tables WHERE engine LIKE 'InnoDB' AND table_comment RLIKE 'InnoDB free: ([0-9]{6,}).*';

Starting with 5.1.21, this was moved to the data_free column (a much more appropriate place):

…

This post is the fifth of a series that started here.

From the previous posts of this series, we now have an instance restart script that can restart the database node in case of failure and automatically reconfigure Pacemaker and the other servers that needs to access the MySQL server. What we will cover in this post is the monitoring script that run on the MySQL node.

At its smallest expression, the instance monitoring script is a simple empty loop that will run forever like:

PLAIN TEXT CODE:

1. #!/bin/bash
2.  
3. while [ 1 ]
4. do
5.     sleep 60
6. done

Although fully functional, this monitoring script is rather weak could be …

Dear Perl and Drizzle developers,

I’m pleased to announce the release of DBD::drizzle .301! This release implements several fixes:

* Eric Day’s fix to a bug where a result can be NULL after error.
* A lot of reworking of column_info() to utilize Drizzle’s DATA_DICTIONARY and INFORMATION_SCHEMA as opposed to describe and show commands as well as fixing the catalog test to work with these changes
* Other fixes from DBD::mysql

You can find the source code at Launchpad.net:

lp:dbd-drizzle

As well as CPAN:

http://search.cpan.org/~capttofu/DBD-drizzle-0.301/lib/DBD/drizzle.pm

Please feel free to report bugs to http://rt.cpan.org, and as always, thank you for your interest in DBD::drizzle!

Related posts:

1. DBD::mysql 4.018 released
2. …

MySQL Cluster Push-Down Joins

A great chance to find out about the “SPJ” project that’s under way to improve the performance of complex table JOINs. A free webinar has been scheduled for Thursday, November 04 at 09:00 Pacific time (16:00 UK; 17:00 Central European Time) – just register for the webinar at mysql.com. Even if you can’t attend, by registering you’ll get an email telling you where to watch the replay shortly after the webinar.

MySQL Cluster performance has always been extremely high and scalable when the work load is primarily primary key reads and write but complex JOINS (many tables in the JOIN and/or a large number of results from the first part of the query) have traditionally been much slower than when using …

I keep a file of notes, one per line, for reference. The real reason is for source material if I ever write a 3rd edition of High Performance MySQL, but hardly a day goes by that I don’t look something up. At the very top, I have a section that I want to share with you. It’s a list of URLs that are invaluable sources of information and insight.

• http://blogs.sun.com/realneel/
• http://developer.cybozu.co.jp/kazuho/
• http://www.chriscalender.com/
• http://www.facebook.com/notes.php?id=102841356695
• http://mysqlha.blogspot.com/
…

We've published our first case study. The customer, ideeli, had a database that was struggling on standard MySQL and InnoDB. The big win was the upgrade to XtraDB. The business continued to grow quickly, and months later under much more traffic, the database is still outperforming their previous version.

I thought I'd write a few notes that didn't seem appropriate to include in the case study, because this was a fun project that might be interesting to readers.

As usual, it was all about diagnosing the problem correctly. I used a variety of tools to help with this, foremost among them "stalk" and "collect" from Aspersa. There were several problems, not just one, and they required different techniques to diagnose. This can be hard when the …

I keep a file of notes, one per line, for reference. The real reason is for source material if I ever write a 3rd edition of High Performance MySQL, but hardly a day goes by that I don’t look something up. At the very top, I have a section that I want to share with you. It’s a list of URLs that are invaluable sources of information and insight. http://blogs.

Translation of "Methods for searching errors in SQL application" just finished, epilogue is at http://sql-error.microbecal.com/en/concl.html

It contains list of methods which had been discussed.

Epilogue

Finally I'd like to repeat methods which we discussed. Unfortunately
there are several problems left. I will be glad to know your opinion
about what else to descuss. I will be waiting your notes at sveta_dot_smirnova_at_oracle_dot_com or sveta_at_js-client_dot_com

List of methods.

...

Rest of the text is …