Giving a bunch of mysql instances something you do everyday and you might think ….. how should I do it? Write a bunch of selects and inserts manually? nahh that takes s**tload of time, should I run binlogs collected from a live system on my test server? nahh thats not practical nor is it real since it doesn’t contain selects, should I gather the general query log and try that out? nahhh  …..

MySQL has been kind enough to supply us with their mysql_slap which does the job for us and given I needed to do a proof of concept on monitoring a group of 4 circular replicated servers I wrote a small script which does the job of slapping them with a varying level of concurrancy, iterations, number of queries and connections for as long as you like.

Here it is and I hope some of you might find it useful for slapping their own test servers :).

#!/bin/bash

NumberOfConcurrentLoads=4
…

The open source vendor definition debate rumbles on. How open source could save the US government $3.7bn. Red Hat plans MASS migration to JBoss. Open source content management invades the US. Exploiting the attribution loophole in the GPLv3. And more.

Definition debate rumbles on
Roberto Galoppini joined the open source vendor definition debate, with a perspective looking at the impact on community engagement, and also caught up with David Dennis, senior director of product marketing at Groundwork, about the company’s strategy, noting that not all open source core vendors are created equal.

Meanwhile Tarus Balog of OpenNMS, who …

Just a small tip for using Bazaar on OpenSolaris: set the BZR_SSH environment variable to openssh if you don't have the Python module Paramiko installed.
For example, in your ~/.bashrc:

export BZR_SSH=openssh

Could be useful if you're pulling MySQL stuff from Launchpad and use (Open)Solaris. Could also be useful on other Unix-like operating systems when the default is missing (Bazaar uses Paramiko as default).

One of the most important features of MySQL is the support for pluggable storage engines. Since users use MySQL in different ways, one storage engine may not fit everyone 's needs. There are lots of engines for a user/application to choose from. Applications (and users) access MySQL using a uniform interface irrespective of what kind of storage engine is being used. As with any kind of software layering, it is possible to lose optimization opportunities as we cross layer boundaries. In this blog I will discuss one such lost opportunity.

Internally MySQL uses the PSAPI (public storage engine api) to communicate with the storage engines. The MySQL server uses a fixed row format. Storage engines are free to use whatever row format they choose (for ex, InnoDB uses a different row format). The advantage of using a different row format is that specialized storage engines could store rows optimally. For example, a storage engine for DSS could store …

One of the most important features of MySQL is the support for pluggable storage engines. Since users use MySQL in different ways, one storage engine may not fit everyone 's needs. There are lots of engines for a user/application to choose from. Applications (and users) access MySQL using a uniform interface irrespective of what kind of storage engine is being used. As with any kind of software layering, it is possible to lose optimization opportunities as we cross layer boundaries. In this blog I will discuss one such lost opportunity.

Internally MySQL uses the PSAPI (public storage engine api) to communicate with the storage engines. The MySQL server uses a fixed row format. Storage engines are free to use whatever row format they choose (for ex, InnoDB uses a different row format). The advantage of using a different row format is that specialized storage engines could store rows optimally. For example, a storage engine for DSS could store …

This isn’t exactly MySQL stuff.. but it relates in the sense that I am looking to move from our current workflow setup for MySQL Magazine to something that is (in order of importance):

• Able to generate pdf output along with attendant hyperlinks,etc.
• Able to effectively create a professional document with input from a number of authors
• Open Source if it meets the above criteria

I have been more than a little unhappy with previous setups (we tried openoffice and now currently Word to Adobe distiller). After the six freaking hours I wasted on the last go around because columns where right justified and looked perfect in Word, but when distilled in pdf the output came out with random spacing (including INSIDE WORDS), I am ready to chunk this approach and try something new.

One of the authors kindly offered to work with me on getting a latex setup. While I am grateful for the …

There’s a change of behaviour in MySQL 5.1.31 for Row Based Replication, if you have InnoDB transactions that also write to a MyISAM (or other non-transactional engine) table. It’s a side effect of fixing Bug #40116. Take this simple example:

Transaction 1: INSERT INTO myisam_tbl (item, val) VALUES (1, 0);
Transaction 1: INSERT INTO innodb_tbl (item, val) VALUES (1, -1), (2, -1);
Transaction 1: START TRANSACTION;
Transaction 1: UPDATE myisam_tbl SET val=val+1 WHERE item=1;
Transaction 1: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=1;
Transaction 2: START TRANSACTION;
Transaction 2: UPDATE myisam_tbl SET val=val+1 WHERE item=1;
Transaction 2: UPDATE innodb_tbl SET val=( SELECT val FROM myisam_tbl WHERE item=1 ) WHERE item=2;
Transaction 2: COMMIT;
Transaction 1: COMMIT;

After this, the Master innodb_tbl would look like this:

If you are using InnoDB Hot Backup and a recent version of mysqld (at least 5.0.67 or higher, including 5.1.30, though it may be later versions), your backup will run fine and output OK! at the end, as it should.

Except for one thing.

The binary log file and position do not appear in their rightful place. Here’s a snippet of the output from the backup:

innobackup: MySQL binlog position: filename 'Warning', position (Code 1287):
'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
090210 03:55:04  innobackup: innobackup completed OK!

That’s pretty misleading — looks like the backup completed OK, but it did not show us the binary log position. What about the ibbackup_binlog_info file?

[mysql@db3:~] more ibbackup_binlog_info
Warning (Code 1287): 'TYPE=storage_engine' is deprecated; use 'ENGINE=storage_engine' instead
[mysql@db3:~]

Yikes! What happened to the …

Because I was asked….What should I set my my.cnf parameters to? What are good default values? How much memory should I allocate to the db if I have X amount of ram? What is a good starting point for the mysql config files?

You’re not really Googling for a my.cnf to use are you? You probably are, Shame on you!  The best thing to do is to test before you make changes, and find the best configuration for your application.  But your not going to are you? oh well I might as well accommodate you. I make no claims these will work for everyone. In fact if you hire me later on I may look at you funny after I analyze your system, and may call you funny names behind your back for using the wrong settings.  Because there are a lot of people out their who are only database folks 5 minutes at a time (that’s what these posts are about), they are probably going to stick the my-huge.cnf and go with that anyways.  So why not throw …

I vaguely recall a couple of blog posts recently asking something like "what's the formula to compute mysqld's worst-case maximum memory usage?" Various formulas are in wide use, but none of them is fully correct. Here's why: you can't write an equation for it.

The most popular equation, of course, is the one in the error log after a crash: "It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections". This was never true -- not even when ISAM and friends were the only storage engines, which was before most of us remember. And at a minimum, most servers these days need to consider the added memory for InnoDB: specifically, the buffer pool. (There's also an innodb_additional_mem_pool_size but it's typically set pretty small, like 20M).

But even that's not enough. Staying with InnoDB, we also need to count the data dictionary. This can take many gigabytes on servers with a lot …