I have recently been trying out semisynchronous replication. This is a new feature added in MySQL 5.5 based on the original google patch.

Installing was very simple and has been covered elsewhere in detail. Suffice to say it was relatively simple to do.

While test I was a bit surprised by some behavior I saw that turned out correct. What I wanted to examine what was semi-synchronous actually does and use cases for it.

The manual defines this feature correctly in very careful language:
If semisynchronous replication is enabled on the master side and there is at least one semisynchronous slave, a thread …

MySQL system variables and status variables have two scopes. These are GLOBAL and SESSION which are self explanatory.
This is important to realize when altering system variables dynamically. The following example does not produce the expected results.

mysql> USE test;
Database changed
mysql> CREATE TABLE example1(
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> col1 VARCHAR(10) NOT NULL,
    -> PRIMARY KEY(id)
    -> );
Query OK, 0 rows affected (0.29 sec)

mysql> SHOW CREATE TABLE example1\G
*************************** 1. row ***************************
       Table: example1
Create Table: CREATE TABLE `example1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col1` varchar(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

We see that the table has a default CHARACTER SET of latin1. If you wanted to ensure all tables are …

How often do people get indexing wrong? All the time. This is a sample table in Drizzle, I'll show how you can view indexes with the new data dictionary code.

drizzle> show create table f;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                      |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| f     | CREATE TABLE `f` (
   `a` int NOT NULL,
   `b` int NOT NULL,
   `c` int NOT NULL,
   PRIMARY KEY (`a`,`b`,`c`),
   KEY `c` (`c`),
   KEY `b` (`b`,`c`)
)  | …

As part of my MySQLCamp for the Oracle DBA series of talks to help the Oracle DBA understand, use and appreciate MySQL I have also developed a series of short interesting posts I have termed “Don’t Assume”. Many of these are re-occurring points during my consulting experiences as I observe Oracle DBA’s using MySQL. I am putting the finishing touches to my MySQL for the Oracle DBA series of talks and I’m excited to highlight some of the subtle differences and unique characteristics of MySQL RDBMS in comparison to Oracle and some extent other products including SQL Server.

Stay tuned for more soon.

I will be presenting at the MySQL Users Conference 2010 in Santa Clara, April 2010 two presentations from this series, …

I spend the last couple days playing with InnoDB page compression on the latest Percona build.

I’m pretty happy so far with Percona and the latest InnoDB changes.

Compression wasn’t living up to my expectations though.

I think the biggest problem is that the compression can only use one core in replication and ALTER TABLE statements.

We have an 80GB database that was running on 96GB boxes filled with RAM.

I wanted to try to run this on much smaller instances (32GB-48GB boxes) by compressing the database.

Unfortunately, after 24 hours of running an ALTER TABLE which would only use one core per table, the SQL replication thread went to 100% and started falling behind fast.

I think what might be happening is that the InnoDB page buffer is full because it can’t write to the disk fast enough which causes the insert thread to force compression of the pages in the foreground.

…

MySQL Connector/Net 6.3.1, a new version of the all-managed .NET driver
for MySQL has been released. This is an alpha release and is intended to
introduce you to the new features and enhancements we are planning. This
release should not be used in a production environment.

It is now available in source and binary form from
[http://dev.mysql.com/downloads/connector/net/6.3.html] and mirror sites
(note that not all mirror sites may be up to date at this point of time
- if you can’t find this version on some mirror, please try again later
or choose another download site.)

The new features or changes in this release are:

• Visual Studio 2010 RC support
• Nested transaction scope support

What we know may be broken
—————————-

…

Novell’s Q1. The future of OpenSolaris. And more.

Follow 451 CAOS Links live @caostheory on Twitter and Identi.ca

“Tracking the open source news wires, so you don’t have to.”

# Novell reported Linux platform revenue of $37.5m in Q1, up 6.4%.

# Internet.com reported that Novell’s Linux business broke even as Microsoft deal revenues fade.

# As the H reported Oracle exec Dan Roberts confirmed that OpenSolaris has a future at Oracle.

# Citrix acquired Paglo, launched GoToManage service.

# StatusNet …

This Thursday (March 4th, 15:00 UTC - slightly later than usual), Robin Schumacher will present MySQL Column Databases. If you're doing Data Warehouse with your databases this is a must-attend, but it's also interesting to learn what typical other scenarios there are for using column-based storage engines, and how column databases work in the first place.

For MySQL University sessions, point your browser to this page. You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. (Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording, …

Have you ever come across a situation called NO MORE SPOOL SPACE?  My friend does and hence I studied a bit about Teradata. The query: SELECT DISTINCT fieldname FROM tablename; The error: “NO MORE SPOOL SPACE”. Correct, the problem wasn’t related to MySQL, that was for something called Teradata. Initially, I could see that query should be […]

GNUnify is one of the major annual FOSS events here in India. Organised by the students of Symbiosis Institute for Computer Science & Research and the Pune Linux Users Group (PLUG), GNUnify is an event of consequence in the Indian FOSS ecosystem. This year, the conefrence took place on Feb 19th and 20th in the wonderful city of Pune. I submitted two papers on MySQL and fortunately both of them were selected. Thanks to the organising team, who were helpful enough to schedule both sessions on the second day on request, since I would not have been able to make it on the first day. But more of that later. Let me recount the experience of …