To get this straight - I'm not a big fan of filtered or partial MySQL Replication (as of version MySQL 5.0) - there is enough gotchas with replication itself and getting things right with filtering can get quite bumpy road. In some applications however it is very helpful so lets see what one should do or should not do to make it work.

Experience shared here mainly applies to logical level replication. MySQL 5.1 can use row level replication which alleviates some of these issues but I guess it will still take some time before it becomes massively used.

First thing you need to know about partial replication - you can do filtering on the Master or on the Slave. Filtering on the Slave is normally preferred as in this case you get full binary log on the Master which is not only helpful for replication needs but also if you ever need to do roll forward recovery while recovering from backup. There are however some cases when you really …

Regarding my last post MySQL Administrator useful InnoDB graphs here are the explanations of some of the graphs.

idb storage page

• reads graph: number of reads; it monitors if the system is under heavy or light read access.
• pending reads graph: number of pending read requests to the underlying data file (the database must wait to gain access). If this value increases it could indicate serious contention issue. Seeing this graph always close to the bottom line it’s very good.
• writes graph: number of writes; it monitors if the system is under heavy or light write access.
• pending writes graph: number of pending write requests to the underlying data file. If this value climbs requires attention. Seeing this graph …

I often noticed that people complain about the LOAD DATA performance when loading the table with large number of rows of data. Even today I saw a case where the LOAD DATA on a simple 3 column table with about 5 million rows taking ~15 minutes of time. This is because the server did not had any tuning in regards to bulk insertion.

Consider the following simple MyISAM table on Redhat Linux 32-bit.

1
2
3
4
5
6

CREATE TABLE load1 (
  `col1` varchar(100) NOT NULL DEFAULT '',
  `col2` int(11) DEFAULT NULL,
  `col3` char(1) DEFAULT NULL,
  PRIMARY KEY  (`col1`)
) TYPE=MyISAM;

The table has a string key column. Here is the data file( …

Today I had an interesting issue from one of the internal property within Yahoo!, where they were not able to start the MySQL server after the server has been upgraded to 5.1 from 4.1.

Here is the error log when the 5.1.22 server has started on 32-bit RHEL:

1
2
3
4
5
6
7
8

      071105 12:25:50  INNODB: Operating system error number 27 IN a file operation.
INNODB: Error number 27 means 'File too large'.
INNODB: SOME operating system error numbers are described at
INNODB: http://dev.mysql.com/doc/refmastartn/5.1/en/operating-system-error-codes.html
INNODB: File name /mysql/var/mysql/ibdata/ibdata1
INNODB: File …

Yep, it's true. "MySQL Toolkit" technically infringes on MySQL's trademark. I haven't been given a mandate or cease-and-desist order or anything, but as Marten Mickos (MySQL's CEO) told me, anything that starts with the word "MySQL" sounds like it comes from the company, and this project doesn't come from MySQL.

Suggestions are welcome. I'm totally uninspired. "Toolkit" was the best I could do when I named the project to begin with.

MySQL AB will be exhibiting at next week's Oracle OpenWorld event, November 12-15 in San Francisco; Moscone South Hall, kiosk booth PSP 32.

In the past year, the Independent Oracle Users Group has produced surveys of its membership that show that MySQL is an increasingly popular complementary database within Oracle customers' IT departments. Between one-quarter and one-third of IOUG members report using MySQL in their organizations today -- and a majority plan to grow their adoption of open source database technology in the next year.

For a free copy of the most recent IOUG open source survey, as well as technical MySQL resources for Oracle developers and DBAs, please visit www.mysql.com/oracle.

Thanks to a patch Dane Miller submitted, MySQL Heartbeat can now be used to monitor Slony-I replication lag for PostgreSQL. It works identically: it updates a record on the master and looks for it on the slave. You just have to specify the database driver on the command-line:

mysql-heartbeat [options] --dbidriver=Pg

Most of the tools in the MySQL Toolkit are very MySQL-specific. This one just happened to be an exception, and I'm happy it's useful for more than the original purpose.

Note: Installing Oracle 11gR1 on Ubuntu 8.10 Intrepid Ibex is now published.

After many requests from readers, I’ve put together new, revised version of the Oracle 11g on Ubuntu recipe. This new version is a little different than the first one published: it’s based on a bare-bones install of Ubuntu 7.10 (Gutsy Gibbon) server version instead of the desktop version. As an improvement, I’ve tried to pare down dependencies to a minimal set.

Your feedback is more than welcome — it’s the main reason why I wrote a new version of this HOWTO. I’ve also tested and repeated this procedure twice. Even so, it might still have problems, so please let me know so we can improve it …

Jim Starkey, Senior Software Architect at MySQL, is featured in an online interview over at Dr Dobb's Journal. It's a short interview, but every question reveals insight into the Jim's thoughts on the relational model, SQL, security, standards and more. For those who don't know Jim, he is one of the pionneers of the database industry and has implemented several relational databases in his career. Jim has always been an innovator, inventing the widely accepted notion of Blobs (binary large objects) and multi-generational versioning, a key innovation of InterBase. Jim and Ann Harrison joined MySQL from Netfrastructure, the company they... READ MORE

Jim Starkey, Senior Software Architect at MySQL, is featured in an online interview over at Dr Dobb's Journal. It's a short interview, but every question reveals insight into the Jim's thoughts on the relational model, SQL, security, standards and more. For those who don't know Jim, he is one of the pionneers of the database industry and has implemented several relational databases in his career. Jim has always been an innovator, inventing the widely accepted notion of Blobs (binary large objects) and multi-generational versioning, a key innovation of InterBase. Jim and Ann Harrison joined MySQL from Netfrastructure, the company they... READ MORE