This is a hack I’ve heard about a couple times now:

Paul wrote a script that reads from the logfile the queries that are going to be executed moments later. He parses the queries and constructs new select queries that populate the cache with the data that speeds up the upcoming writes. He claims, if I remember correctly, a three to four times speed-increase.

Here’s the problem in a nutshell. The master can write transactions in parallel but slaves can only write them in series. [1]

This means you have a lot of optimizations on the master (TCQ and NCQ being examples) that aren’t possible on the slave.

What this patch would do is precache the data so it’s already available in memory. Since you’re pre-reading the binary log you can run SELECTs in parallel on the SLAVEs so that the cache is …

I’ve written before about how to make MySQL replication reliable. One thing I think you need to do to make statement-based replication reliable is eliminate temporary tables. I found an elegant way to replace temporary tables with real tables in the systems I maintain. This article explains how. The problem Temporary tables are anathema to reliable MySQL replica servers. If you have a temporary table and the replica crashes in between accesses to the temporary table, when you restart replication the temporary table no longer exists, and you are in trouble.

Someone should really check the label before mass producing. Doesn't this look like SQL from SQL Server????

This is more of an essay than a blog post, but this subject comes up time and again, and since I tripped across this interesting blog post by Pedro Timóteo about why he has decided not to be a sysadmin any more, I thought now’s as good a time as any to comment on what [...]

Just when we thought it couldn?t get any simpler for organizations than downloading a pre-packaged messaging and collaboration solution like Zimbra, Red Hat?s RHX program finds a way. The Zimbra team is excited to participate in Red Hat?s RHX program. Zimbra?s open source community members have often asked if they could download & evaluate, procure and get support for both the Red Hat infrastructure and Zimbra?s rich Ajax-based messaging and collaboration solution ? all online and from a single source. The RHX program fulfills the community?s wishes by providing a pre-integrated and Red Hat certified Zimbra solution for RHEL.

Zimbra is among the charter members of RHX along with other open source market leaders like …

Red Hat Exchange went live minutes ago. We at Zmanda are thrilled to be one of the RHX launch partners: Alfresco, CentricCRM, Compiere, EnterpriseDB, Groundwork, Jaspersoft, Jive, MySQL, Pentaho, Scalix, SugarCRM, Zenoss, Zimbra and Zmanda. Congratulations to Matt Mattox and rest of RHX team at Red Hat.

Just noticed YouTube is down. Anyone else experiencing this?

In many cases you don't want your the result from your SQL statement in just a plain listing, but organized in columns, or whith data grouped on status, period or whatever. This can often be accomplished with a combination of the IF function and SUM or another group function.

For example get your sales per customer grouped by period:
SELECT customer_no AS CUSTNO, cust_name AS CUSTNAME,
sum(if(period = '200701',amount,0) as Jan_Amount,
sum(if(period = '200702',amount,0) as Jan_Amount
FROM otd.salestrans_hist s
WHERE period IN ('200701','200702')
GROUP BY customer_no, cust_name

Or number of open and closed records in each class:
SELECT class, sum(if(status='Open',1,0)) as open,
sum(if(status='Closed',1,0)) as closed
FROM table_name
GROUP BY class

In the examples I have used the MySQL IF function. You could also use CASE, which is SQL …

I've learned how to write MySQL UDF's (User Defined Functions). The interface isn't terrible complicated, tho it's annoying and incomplete in many ways.

I'm going to use the UDF mechanism to shoehorn my AgentX SNMP subagent into 4.0, 4.1, and 5.0, since they don't have the daemon plugin interface that 5.1 has.

But now that I know how to write UDFs, what functions have you always wished you could call from SQL?