The latest from the MySQL community

Goal:

Getting rid of filesorts and temporary tables by tuning MySQL queries.

Background:

Filesorts and temp tables are a necessary evil in MySQL, used when MySQL must sort the data before returning the output to the user. They are the most common issue with slow queries in MySQL, the main reason being that if the output is too large, you can kiss goodbye in-memory performance, and say hello to disk access.

Common User and Manager symptoms:

1. Sore throats due to excessive swearing at poor database performance.
2. Sore hip pockets due to lack of scalability requiring continuous hardware purchases.
3. Sore users who are sitting on high speed bandwidth and have to wait more than 1.5 secs for a response from any web page. Google has raised the bar, time to ditch the straddle technique and go for the Fosbury Flop.

Appropriate Medicine:

The quickest way to …

In my last post (see it for details) about stored procedure replication I was wrong.
I'm using 5.0.30 and, in fact, CALL statements are not written to the binlog. My knowledge about SP replication was before 5.0.12. This morning I read carefully the manual :-)

But my problem is real. Using multiple rand() inside a SP cause wrong value replication on the slaves.

Here is the binlog content of my previous post example:

corra@localhost[(none)]> show binlog events in 'veleno-bin.000003' from 18403385 limit 4\G
*************************** 1. row ***************************
   Log_name: veleno-bin.000003
        Pos: 18403385
 Event_type: RAND
  Server_id: 1
End_log_pos: 18403420
       Info: rand_seed1=1044525788,rand_seed2=146374381
*************************** 2. row ***************************
   Log_name: veleno-bin.000003
        Pos: …

In my last post (see it for details) about stored procedure replication I was wrong.
I’m using 5.0.30 and, in fact, CALL statements are not written to the binlog. My knowledge about SP replication was before 5.0.12. This morning I read carefully the manual
But my problem is real. Using multiple rand() inside a SP cause wrong value replication on the slaves.

Here is the binlog content of my previous post example:

corra@localhost[(none)]> show binlog events in 'veleno-bin.000003' from 18403385 limit 4G
*************************** 1. row ***************************
   Log_name: veleno-bin.000003
        Pos: 18403385
 Event_type: RAND
  Server_id: 1
End_log_pos: 18403420
       Info: rand_seed1=1044525788,rand_seed2=146374381
*************************** 2. row ***************************
   Log_name: veleno-bin.000003
        Pos: …

Colin has posted the program for the MySQL Miniconf @ LCA2007, featuring (in no particular order) Laura Thomson, Guy Harrison, Jonathan Oxer, Morgan Tocker, Jonathon Coombes, myself, with Stewart Smith, Colin Charles and many other MySQL users also there.

Plenty of interesting stuff and good fun to be had.
Hope to meet you there!

LinuxWorld Magazine ran this article yesterday about open source's move up the stack. (Thanks, Russ, for pointing me to it.) Rather than wondering whether open source has arrived (it has), the article asks, "Where?":

“Open source has won the first battle: It is now listed among the default platform decisions,” says Dave Jenkins, CTO at online outdoor sporting goods retailer Backcountry.com in Park City, Utah. The next step, open source users agree, is moving up the stack and figuring out which open source tools are ready for enterprise deployments.

“Infrastructure open source products are essentially a no-brainer at this point, but the adoption of enterprise applications has been slow,” says Curtis Edge, CIO at The Christian Science Monitor, which revamped its …

Hi -

You can now download MySQL 5.2 Alpha binaries that contain the new Falcon storage engine from the MySQL web site. Right now, we’ve got Linux and Windows builds only, but will be expanding that support shortly. So give our new transactional storage engine a try and let us know what you think via this site or the new Falcon forum.

Thanks!

If we have a replication architecture and we are using stored procedures, we need to pay attention to the use of rand() inside the SPs.

Usually, single queries replicates correctly rand() values. MySQL writes to binlog the random number seed. You can consider the function execution timestamp and random number seed as implicit inputs that are identical on the master and slave.

You can see that below

mysql> create table prova(a double);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into prova values(rand());
Query OK, 1 row affected (0.00 sec)

mysql> show binlog events in 'mysql-bin.000035' from 865\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000035
        Pos: 865
 Event_type: Query
  Server_id: 1
End_log_pos: 957
       Info: use `prova`; create table prova(a double)
*************************** 2. row ***************************
   Log_name: …

If we have a replication architecture and we are using stored procedures, we need to pay attention to the use of rand() inside the SPs.

Usually, single queries replicates correctly rand() values. MySQL writes to binlog the random number seed. You can consider the function execution timestamp and random number seed as implicit inputs that are identical on the master and slave.

You can see that below

mysql> create table prova(a double);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into prova values(rand());
Query OK, 1 row affected (0.00 sec)

mysql> show binlog events in 'mysql-bin.000035' from 865G
*************************** 1. row ***************************
   Log_name: mysql-bin.000035
        Pos: 865
 Event_type: Query
  Server_id: 1
End_log_pos: 957
       Info: use `prova`; create table prova(a double)
*************************** 2. row ***************************
   Log_name: …

Went to the dentist today. No cavities which has been the case for
two decades. I do need to have one part of my gums repaired. The
dentist was trying to take a picture of the gum so that I can see the
required work.

And what appears on the screen?

A windows warning complaining about Sybase crashing via the ODBC driver.

The dentist laughed when he saw my face and asked me if I knew
anything about computers :)

Sometimes you need to think backwards.

Here was the problem. I needed to match up some IP address ranges to the company that owns them. Looking for a simple solution to the problem I came up with storing the IP address block patterns in the database as follows:

ip_pattern
----------------
127.%.%.%
192.168.%.%
10.%.%.%

Any idea why I choose % as the wildcard?

That's right - it's the wildcard operator in SQL for the LIKE statement.

So now when I have have an IP address 192.168.1.1, I can do what I like to call a backwards LIKE query:

SELECT company, ip_pattern
FROM company_blocks
WHERE '192.168.1.1' LIKE ip_pattern

This works on SQL Server and MySQL, and I would think it should work fine on any database server.