I've been very busy testing and testing MySQL 5.1.12-beta and on the surface, it passes my tests. But first, let me set the stage as far as what we are trying to accomplish at RightMedia...

All of our adserving data is aggregated into our reporting database, which runs MySQL version 5.0.22. The database is so large, that we split it across 6 machines. Each of these machines has a similar configuration: 2 Dual core Opteron CPUs, 16GB of memory and 3 146GB 15k RPM SCSI drives in a RAID-0 set.

Here is what one of our typical tables looks like:

CREATE TABLE `network_daily` (
  `entity_id` int(11) NOT NULL default '0',
  `buyer_entity_id` int(11) NOT NULL default '0',
  `buyer_line_item_id` int(11) NOT NULL default '0',
  `seller_entity_id` int(11) NOT NULL default '0',
  `seller_line_item_id` int(11) NOT NULL default '0',
  `size_id` int(11) NOT NULL default '0',
  `pop_type_id` …

Watch the number of average queries per second

Please pay attention to the load average of the server

I was astonished when I saw these numbers. Does anyone has the same MySQL performance?

The server we are talking about is a replicating slave server (double XEON processor, 1GB RAM) of www.inter.it, during AC Milan-Inter Milan match.

Watch the number of average queries per second

Please pay attention to the load average of the server

I was astonished when I saw these numbers. Does anyone has the same MySQL performance?

The server we are talking about is a replicating slave server (double XEON processor, 1GB RAM) of www.inter.it, during AC Milan-Inter Milan match.

Mike Kruckenberg posted recently a tip he found to create easy text bar charts using the REPEAT function, that he found at:

http://www.squarebits.com/blog/2006/11/generate_simple.html

Today I stumbled across this link:
http://www.webcheatsheet.com/php/dynamic_image_generation.php#diagram

Which, using PHP, creates a graphical bar chart using the data in a MySQL database and a small bit of math.

Both are very neat!

Hot from the Virtual Appliance Turkey Fryer, build 74 of the PostgreSQL Virtual Appliance is available for download here

This appliance features automated use of Virtual Hard disks to allow the nano sized appliance to access any size storage for its database.

This appliance provides an upgrade to PostgreSQL 8.1.4.

Enjoy.

Lisa Dobson has published the 20th edition of Log Buffer, the weekly review of database blogs, on her Oracle Newbies Blog. Twenty-editions-on is a good time to look back. I think I can say that Log Buffer has been quite a successful project so far. It is recognized, read, and enjoyed. Also, I’ve [...]

In some situation (as described below in "how to repeat") the table_rows column
in the information_schema.tables table is not computed correctly.
The problem arise only for tables included in a merge table.

It seems that if you issue a select statement on a "merged table" the following
insert statement does not update the metadata table_rows.

How-to-repeat

First of all let's try the example showing the expected behavior


mysql> create table t1 (a int);
Query OK, 0 rows affected (0.00 sec)



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



mysql> create table t (a int) engine=merge union=(t1,t2) insert_method=last;
Query OK, 0 rows affected (0.01 sec)



mysql> insert into t values(1),(2);
Query OK, 2 …

In some situation (as described below in “how to repeat”) the table_rows column
in the information_schema.tables table is not computed correctly.
The problem arise only for tables included in a merge table.

It seems that if you issue a select statement on a “merged table” the following
insert statement does not update the metadata table_rows.

How-to-repeat

First of all let’s try the example showing the expected behavior


mysql> create table t1 (a int);
Query OK, 0 rows affected (0.00 sec)



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



mysql> create table t (a int) engine=merge union=(t1,t2) insert_method=last;
Query OK, 0 rows affected (0.01 sec)



mysql> insert into t values(1),(2);
Query OK, 2 rows …

I already wrote in the blog as well mentioned in presentation there is often a choice you have to make between having prefix index - which can be significantly smaller in size and having index being covering index, which means query can be executed using only data from the index without reading the row itself.

Today I had a chance to do couple of experiments to see when exactly it works or does not work:

PLAIN TEXT SQL:

1. CREATE TABLE `t` (
2.   `i` int(11) DEFAULT NULL,
3.   `j` char(10) DEFAULT NULL,
4.   `k` int(11) DEFAULT NULL,
5.   KEY `i` (`i`,`j`(5),`k`)
6. ) ENGINE=MyISAM

Now lets see if index can be used as covering index if it has some key parts which are prefixes:

PLAIN TEXT SQL:

1. mysql> EXPLAIN SELECT k FROM t WHERE i=5 \G
…

My blogging hasn't been so active lately. Mostly due to that I've been busy on
other things. One of the things that have kept me busy the last few months
is a project which I highly enjoy. I've been performing a benchmark study
of MySQL Cluster using Dolphin SuperSockets. Performance is one of my
favourite topics and a parallel database like MySQL Cluster has a wide array
of performance challenges that makes it very interesting to optimize it.

I will present the results in two webinars on the 30th Nov and 13 dec. The
webinars will also provide some input to the features in Dolphin
SuperSockets and MySQL Cluster that enables high performance and
real-time characteristics. With these changes to MySQL Cluster and using
the Dolphin SuperSockets MySQL Cluster becomes even more adapted for
all types of real-time applications.
See:
…