Percona will be at Southeast Linux Fest tomorrow and Saturday. We’re supporting the event, and we hope to meet you there. If you’ve never been, this is a great regional show that’s grown enormously in the last few years. Peter and I will both be speaking: Peter about optimizing MySQL configuration, and I will talk about inspecting MySQL’s performance and scalability via TCP packet headers.

In addition, the entire Percona Toolkit and Online Tools team (me, Daniel Nichter, Brian Fraser, and Miguel Trias) will be there. This is your perfect opportunity to meet the engineers writing the Percona Toolkit code. Direct access to the developers is a valuable chance to tell us your wishes, explain your gripes or troubles, and get help and advice on how you’re using the tools. We can also tell you what we’re currently working on and what you may expect in future releases. We …

From a Tumblr engineering blog post:

Tumblr is one of the largest users of MySQL on the web. At present, our data set consists of over 60 billion relational rows, adding up to 21 terabytes of unique relational data. Managing over 200 dedicated database servers can be a bit of a handful, so naturally we engineered some creative solutions to help automate our common processes.

Today, we’re happy to announce the open source release of Jetpants, Tumblr’s in-house toolchain for managing huge MySQL database topologies. Jetpants offers a command suite for easily cloning replicas, rebalancing shards, and performing master …

A coworker came to me with a perplexing issue. He wanted to know why these two queries were not returning the same results:

mysql> SELECT COUNT(*) 
    -> FROM parent
    -> WHERE id NOT IN (SELECT parent_id FROM child);
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (7.84 sec)

mysql> SELECT COUNT(*)
    -> FROM parent p
    -> WHERE NOT EXISTS(SELECT 1 
    ->                  FROM child c
    ->                  WHERE p.id = c.parent_id);
+----------+
| count(*) |
+----------+
|     5575 |
+----------+
1 row in set (2.95 sec)

At first (and second, and third) glance these two queries look identical. It obviously is an exclusion join and because the MySQL optimizer is what it is, I decided to rewrite it as a LEFT JOIN to see what results came back:

mysql> SELECT …

We finish our series on data types by talking about ENUM, SET and all the different sql modes.

Conferences
The 1st Latin American Conference about MySQL, NoSQL and Cloud technologies, will be held in Buenos Aires from Tuesday, June 26th through Thursday, June 28th at the Puerto Madero Hilton Hotel.

MySQL Connect will be held in San Francisco on Saturday September 29th and Sunday September 30th. This is a technical conference about MySQL, bringing together MySQL engineers at Oracle and the MySQL Community, and will include sessions about the latest MySQL features and roadmaps.

read more

Here’s a quick Python script for adding columns for all tables in your EER model. This will create a create_time and update_time columns in all tables, but you can modify it to your needs. To execute:

1. go to Scripting -> Scripting Shell…
2. click the New Script toolbar icon at the top left corner
3. select Python Script and specify some name for the script file
4. click Create
5. copy/paste the script there
6. click the Execute toolbar button

Make sure to backup your model before running this!

The code

# get a reference to the schema in the model. This will get the 1st schema in it.
schema = grt.root.wb.doc.physicalModels[0].catalog.schemata[0]
# iterate through all tables
for table in schema.tables:
    # create a new column object and set its name
    column = grt.classes.db_mysql_Column()
    column.name = "create_time"
    # add it to the table …

Yesterday I was asked by a customer for the reason why he had failed to achieve scale with a state-of-the-art "shared-storage" cluster. "It's a scale-out to 4 servers, but with a shared disk. And I got, after tons of work and efforts, 130% throughput, not even close to the expected 400%" he said.

Well, scale-out cannot be achieved with a shared storage and the word "shared" is the key. Scale-out is done with absolutely nothing shared or a "shared-nothing" architecture. This what makes it linear and unlimited. Any shared resource, creates a tremendous burden on each and every database server in the cluster.

In a previous post, I identified database engine activities such as buffer management, locking, thread locks/semaphores, and recovery tasks - as the main bottleneck in the OLTP …

A Columbus, Ohio lawmaker wants to ensure that the public quickly becomes aware of online security breaches.

The legislation bill, soon to be introduced, will require state agencies, businesses, and institutions to report any database security breach to the Ohio attorney general’s office if any Ohio resident’s personal information was accessed.
The bill is jointly sponsored by a Dayton, Ohio, lawmaker.

The proposal also would require that the security breach report be filed no later than 40 days following its discovery. The attorney general’s office would compile the breaches into a public, searchable database.

However, many small and medium organizations suffer from lack of visibility are not even aware that they have been breached.

If …

Edited to add - this is in Percona's patched 5.5, not in Oracle's 5.5.

We have only recently started using MySQL 5.5 here at Mozilla, and while there are features I have known about for a while, I usually hear about them early on and file them as “oh yeah, that is coming later!”. Like how right now we are learning about all the neat stuff in MySQL 5.6. Well, I am sure I knew about the addition of the Rows_read and Rows_sent fields to the PROCESSLIST information, but it was only today that I really am using it.

I am doing an export of a large table, and I did a SHOW PROCESSLIST to see:

Id: 651130
User: export
Host: localhost
db: puppet_dashboard
Command: Query
Time: 1246
State: Sending data
Info: SELECT /*!40001 SQL_NO_CACHE */ /*!50084 SQL_NO_FCACHE */ * FROM `resource_statuses` …

With NoSQL and Hadoop, the database world has undergone a revolution. The fighting reached its peak a couple of years ago, but things have calmed down since, and now is a good time to take stock of old and new style data management technologies.

From this revolution, we can learn a lot about what databases should, and should not be. At the end of this post, I propose a system, called Optiq, that would restore to NoSQL/Hadoop systems some of the good features of databases.

Learning from history

Revolutions tend to follow patterns. George Orwell allegorized the progress of the Russian Revolution in his novel Animal Farm. He described the injustices that were the trigger for the revolution, the new egalitarian value system …

Earlier this week, Andrew Morgan wrote a piece on running MySQL Cluster on Raspberry Pi. Since the term “Cluster” is hideously overloaded, I’ll note that we’re talking about the NDB cluster storage engine here, a very specific architecture originally acquired by MySQL AB from Ericsson (telco).

Raspberry Pi is a new single-board computer based on the ARM processor series (same stuff that powers most mobile phones these days), and it can run Linux without any fuss. Interfaces include Ethernet, USB, and HDMI video, and the cost is $25-50. I’m looking to use one for the front-end of a MythTV setup (digital video recorder and TV system), I can just strap the Raspberry Pi to the back of a TV or monitor to do its …