Pythian Group

OpenSQLCamp was a huge success! I took videos of most of the sessions (we only had 3 video cameras, and 4 rooms, and 2 sessions were not recorded). Unfortunately, I was busy doing administrative stuff for opensqlcamp for the opening keynote and first 15 minutes of the session organizing, and when I got to the planning board, it was already full….so I was not able to give a session.

• Comparing Non-Relational Databases: MongoDB, Tokyo Tyrant, CouchDB by Igal Koshevoy of Pragmaticraft
• Drizzle Client Rewrite – Clark Boylan leads the requirements and design discussion for rewriting the Drizzle Client
• Drizzle Plugin Hacking
• Drizzle is Not MySQL with Changes – Brian Aker gives a talk on Drizzle, and how Drizzle is not MySQL.
• Fractal Trees – by Bradley Kuszmaul of Tokutek
• Geographic Operators in SQL (PostGIS) by Webb Sprague
• Goal-Driven Performance – Peter Zaitsev speaks about how to achieve goal-driven performance.
• Graph Engine for MySQL – by Antony Curtis
• Intro to Cassandra
• An Intro to CouchDB: What caught Ubuntu’s eye by Mike Miller (Cloudant)
• Maria – MySQL founder Monty Widenius talks about Maria, and the past, present and future of MySQL.

Read the rest of this entry . . .

OpenSQLCamp was a huge success! Not many folks have blogged about what they learned there….if you missed it, all is not lost. We did take videos of most of the sessions (we only had 3 video cameras, and 4 rooms, and 2 sessions were not recorded).

All the videos have been processed, and I am working on uploading them to YouTube and filling in details for the video descriptions. Not all the videos are up right now….right now all the lightning talks are up.

• All the lightning talks belowin one continuous video (just over 54 minutes total)
• The Graph Engine (Antony Curtis)
• Cluster/J, a new set of Java APIs to MySQL Cluster 5.1 (John David Duncan)
• Sphinx, the fulltext storage engine (Peter Zaitsev)
• iiBench, the Indexed Insertion Benchmark (Bradley Kuszmaul of Tokutek)
• JJtree in Coco
• Integrating OSS with Windows (Tom Hanrahan of Microsoft)
• Trainwreck, an agent for MySQL replication (Domas Mituzas)
• Column Stores (David Lutz of Infobright)
• I Play With Data, about doing real statistical calculations of data, when SQL gets in the way (Joseph di Paolantonio)
• Your Guide to NoSQL (Brian Aker)
• PL/Parrot The call for PL/Parrot in Postgres is put out there (Jonathan “Duke” Leto)

Read the rest of this entry . . .

Today I’ve been trying to reproduce in an 11g database one of the problems I faced with 10g—one on those problems Riyaj described in his Multi-Column Correlation and Extended Stats in Oracle 11g post. And the fun part is that I wasn’t able to reproduce it. Yet just setting optimizer_features_enable='10.2.0.4' made it show up again. It was as though Oracle can detect Multi-Column Correlation without Extended Statistics. How is this possible?

You don’t need any complicated schemas to check this out for yourself; just one table as below:

create table x (
         a number,
         b number,
         c number);

begin
  for i in 1..1000 loop
    for j in 1..10 loop
      insert into x values (j,j,j);
    end loop;
  end loop;
end;
/

commit;

exec dbms_stats.gather_table_stats(-
                 user,-
                 'X');

Execute a query with a WHERE clause containing two correlated columns:

explain plan for select c from x where a=1 and b=1;

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |   100 |   900 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| X	 |   100 |   900 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

The optimizer estimates the number of rows returned as 100, which is equivalent to considering that each one of the conditions in the WHERE clause returns 1000 rows, and that those columns are not correlated. (In that case that’s obviously wrong, because a equals b). So, no change with 10g? Read the rest of this entry . . .

So, how does one gather statistics on indexes? With InnoDB it’s one thing, with MyISAM it’s another thing. This post however, will focus on MyISAM. There are various ways, each having their own drawbacks and positive sides.

What about ANALYZE TABLE? It’s great — very fast in InnoDB. But with MyISAM a lock occurs while scanning the table.

OPTIMIZE TABLE? Still relatively quick with MyISAM, unless the table in question is huge. InnoDB requires an ALTER TABLE — it might take forever, depending on the server configuration, as OPTIMIZE TABLE for InnoDB maps to a ALTER TABLE tableName ENGINE=InnoDB

We all know how ANALYZE TABLE, REPAIR TABLE, and OPTIMIZE TABLE work with MyISAM. However, there’s a less explored way, for MyISAM — the myisam_stats_method.

Read the rest of this entry . . .

I got to troubleshoot an amazing situation a few weeks ago. I think, we can all agree that assuming we are on a supported configuration of a production release of Oracle, it is essentially inconceivable that allowing a single query to run on your system can flip another query’s plans and cause major performance issues (and in this case even downtime!)

There have been plenty of posts about bind peeking. Alex Gorbachev wrote about it last year, and so did Jonathan Lewis. It’s a well known issue. However what hasn’t been written about is when it is expected to strike and cause you headaches.

A single query can change the plan of a number of other queries, but just sometimes.

One day you come to work (or get woken up) and you have a badly performing system. You look into it and it comes to one query. Nothing has changed, nothing has been modified.

I found the query no problems. I did an explain plan, comes up with an index range scan. Given the execution time however, it can’t be a range scan, it’s taking way too much time. Looking at v$sql_plan reveals the full table scan. At this point I was almost sure what the problem was.

How did I fix it? I did an “alter system flush shared_pool;” the query got reparsed, a new child cursor was created and all new executions of the query were using the expected and much faster index range scan.

Easy fix, but what happened? Nothing has been changed for months!

Sometimes it’s coincidence. The query just happens to run at the right time, or some job is delayed and queries run in a different order and the wrong plan is selected. Those are generally easy to catch as they happen early in the life of a system.

Sometimes it’s load. You decide to run an additional application, which causes more SQL statements to be aged out and re-parsed, creating a higher chance of parsing with “bad” values. Although the additional load will be blamed for the “slow” performance, the root cause will eventually be discovered. In a way, it will be expected that something will go bad when you increase the load.

And sometimes it’s a single ad hoc query with a new predicate that starts the slowly-ticking time bomb.

Here is how it happens . . .

Read the rest of this entry . . .