MySQL Blogs
Access to source code doesn't trump vendor control
Slides (german) from MySQL Connectors talk at the Hamburg MySQL Meetup
On monday I gave a presentation at MySQL user group meeting in Hamburg on the team I’m working for: the MySQL Connectors team. The presentation was given in german. Unfortunately about 99.99999% of all PlanetMySQL had no chance to join the meetup, but you can find the slides (german language) here for download (PDF). The slides give an overview on the team, its products and its activties. You will also find introduction material on those Connectors (drivers) I am working on: Connector/OpenOffice.org, Connector/C++ and all the PHP stuff.
(...)
Read the rest of Slides (german) from MySQL Connectors talk at the Hamburg MySQL Meetup (38 words)
Swinging the Drizzle beat on IPC ...
Following the recent news rumours about Monty leaving MySQL, I just wanted to tell you that Brian Aker is giving a keynote speech about "To Drizzle MySQL" on this year's International PHP Conference at Mainz, Germany which is happening from October 27th to October 31st:
One of the most common databases to developers, MySQL, got forked again, to bring back the original spirit of Open Source to the MySQL Community. The International PHP Conference invited Brian Aker, one of the leading forces behind this move, to talk about how "to Drizzle" MySQL.
If you want to join Brian's keynote and all the other superb sessions, don't miss the chance to reserve your seat for as low as EUR 199,00.
Monty quit the job
Kristian Köhntopp posted the following (German speaking) blog entry: "Monty on the run" stating that Monty quit his job at MySQL/Sun. He wonders where Monty's new job will be...
If you followed the last months, Brian Aker and some other people forked the MySQL codebase and created the Drizzle project. I'm wondering if Monty might spearhead the Drizzle project soon?! If yes, does this move harm the commercial database field? What might Enterprise customers think? Is Community everything or would this move make any harm to MySQL as an affordable OpenSource database itself? We'll see...
Monty Widenius, One of MySQL?s Founding Fathers, Leaves Sun/MySQL
ValleyWag reports that MySQL’s Monty Widenius is no longer “MySQL’s”. Some folks have known that Monty has not been happy in his current position; this leads me to believe the rumor is true (though of course an official announcement is the only confirmation).
So what does this mean for MySQL? Well, honestly, if a product falls apart because one out of 300 employees leaves, it was probably doomed anyway. There are plenty of capable employees left, and being owned by Sun means that there are many more resources they can tap as well.
What will the official company announcement be? My prediction is (more…)
MySQL Conference & Expo 2009 - CFP open
Is it that time already? MySQL Conference & Expo 2009 has opened the CFP.
Submit (well) early and often. It’s always an exciting (and exhausting) conf. Good technical, relevant content is what makes it good. Getting to talk to people who do amazing things, people who use your software, people looking to use it, people who want to chat about how you can learn off each other.
Any suggestions for what you’d like to hear from me (Cluster, Drizzle et al) are welcome - either via private mail or comments here.
OpenOffice.org 3 dev release
So after seeing Paul Fenwick rave about the presenter screen for OO 3, I decided to grab the debs and give it a go.
It still is very slow opening large presentations (i.e. mine), but it does look nicer at least… well… at least some of the widgets do.
Will report back when I’ve had a bit more time to fiddle with it.
Sheeri?s Sordid Past
I confess — I have not always been an exclusive MySQL user. I have fooled around with other DBMSs. I was young, inexperienced, and I needed the money, I swear!
This comes about because I was doing some electronic de-crufting….From a file last modified on 10:50 am on 2005-06-30:
> more addcatalog.sh #!/bin/sh db2 catalog tcpip node $1 remote $2 server 50000 db2 terminate db2 catalog database sample as $2 at node $1 db2 terminate # [db2inst1@midgard db2inst1]$ db2sql92 -a db2inst3/password -d coworkernameAnd from the same time-frame there’s also:
How quickly you should expect to see bugs fixed
Over a year ago I wrote about pretty nasty Innodb Recovery Bug. I ran in the same situation again (different system, different customer) and went to see the status of the bug… and it is still open.
You may thing it is minor issue but in fact with large buffer pool this bug makes database virtually unrecoverable (if 10% of progress in 2hours qualifies as that). It is especially nasty as it is quite hard to predict. Both customers had MySQL crash recovery happening in reasonable time… most of the times until they run into this problem.
So what is the point ? Have modest expectations about when your favorite MySQL bugs are fixed (This is actually Innodb one, so Innobase/Oracle is responsible for fixing it not MySQL/Sun but there are MySQL bugs not fixed for years too). Look for workarounds or ways to fix things yourself.
In particular case workaround was rather easy - reducing Innodb buffer pool size to 4GB instead of 24G and disabling innodb_flush_method=O_DIRECT so OS cache can be used for IOs. This made database to complete crash recovery in 30 minutes.
Entry posted by peter | No comment
Memcached Talk from LinuxWorld
http://download.tangent.org/talks/Getting%20More%20Out%20of%20Memcached%20with%20Libmemecached.mp3
The Study guide I mention can be found here:
http://download.tangent.org/talks/Memcached%20Study.pdf
The talk is general in nature, but it does have some points on how to work with MySQL and Memcached in the same applications stack.
Forrester and the Mural OpenSource MDM Community
We just launched Mural, our Open Source MDM (Master Data Management) project but Forrester's latest Wave Report already says: "Sun Microsystems debuted in the top slot among Strong Performers with solid data deduplication, architecture, and open-source options".
An MDM system allows a single, consolidated, presentation from multiple data sources. Mural brings the experience from JavaCAPS, and adds OpenSource and the benefits of bundling GlassFish Server and MySQL to the mix. I need to write some spotlights on Mural, stay tuned.
New database layer in Drupal 7 to support replication, PDO and SQLite
One of the sessions at DrupalCon I attended was Larry Garfield's talk about "Drupal Databases: The Next Generation", which gave me a good insight into the current state of the Drupal database layer and how they plan to overhaul it for Drupal 7. The key points that I took away:
- A new API based on PDO
- Object-oriented, requiring PHP5
- Support for using prepared statements
- A unified access API
- A query builder
- More support for other database systems (currently Drupal supports MySQL and PostgreSQL only). In particular, they are keen on adding SQLite support, to ease local development.
- Support for master-slave replication (by randomly distributing reads among the hosts)
- Support for using different database types in parallel (e.g. using SQLite for read-only tables, MySQL for everything else)
The slides and a video of the presentation are available, if you want to check it out. There is a task list on the Drupal.org web site that keeps track of the ongoing activities.
MySQL 5.1 Use Case Competition: Adding support for MySQL 5.1 Events to phpMinAdmin
The MySQL 5.1 Use Case Competition is in full swing - we've already received a number of cool and interesting submissions, which we will turn into articles that will be published on the MySQL Developer Zone over the course of the coming weeks. Today we received a note from Jakub Vrána from the Prague, Czech republic. He's the author of phpMinAdmin, a MySQL management tool written in PHP. Here's what he wrote:
In the beginning of September 2008, I have implemented MySQL 5.1 Events to the database management tool phpMinAdmin. I've used the Windows version of MySQL 5.1.26 for the development.
As phpMinAdmin tries to be full-featured, yet compact management tool, I have implemented Events to allow users of MySQL 5.1 manage it.
The implementation was quite straightforward, it took only 4.5 kB of PHP code. Events management is well described in MySQL documentation and easy to understand.
During the development, I've reported three bugs: Bug#39163, Bug#39165, Bug#39173. I have been positively surprised by the speed of reaction to these reports
Thank you very much for your submission and the support, Jakub! We appreciate it.
If you're reading this and are using MySQL 5.1 and any of it's new features: have you considered telling your story yet? You may even win something when doing so!
My angst about using Google Chrome
MySQL Conference & Expo Opens Call for Participation
Conference program chair Colin Charles and the program committee invite proposals for conference sessions, panel discussions, and tutorials. More than 2,000 attendees are expected to participate in over 120 sessions at next year's event.
Proposals can be submitted here. The deadline runs through October 22, 2008.
Co-presented by O'Reilly Media and Sun Microsystems, the MySQL Conference & Expo is the largest gathering of MySQL developers, users, and DBAs worldwide. The event reflects MySQL's wide-ranging appeal and capabilities. The open atmosphere of the MySQL Conference & Expo helps IT professionals and community members create the best database applications, tools, and software through expert instruction, hands-on tutorials, and interaction with readily available MySQL developers. Users gain the knowledge they need to rapidly build solid applications with MySQL that scale with the enterprise. New to the 2009 program will be MySQLCamp, a place where any and all participants can create an "unconference" within the larger event.
ANALYZE: MyISAM vs Innodb
Following up on my Previous Post I decided to do little test to see how accurate stats we can get for for Index Stats created by ANALYZE TABLE for MyISAM and Innodb.
But before we go into that I wanted to highlight about using ANALYZE TABLE in production as some people seems to be thinking I advice to use it.... a lot. In fact I should say I see more systems which have ANALYZE abused - run too frequently without much need than systems which do not run ANALYZE frequently enough.
First it is worth to note MySQL only saves very basic cardinality information for index prefixes for index stats and these rarely change. There is no histograms or any other skew metrics etc. MySQL optimizer also uses number of rows in the table for many decisions but this is computed live (maintained for MyISAM and estimated during query execution for Innodb). This basic information means it does not change whole that quickly at extent to affect optimizer plans.
If you look at the stats accuracy along running ANALYZE TABLE after initial table population and when there are significant changes makes sense. For Innodb as index stats are computed first time table is accessed after restart this often means "never" because MySQL servers are restarted frequently enough. Even once per 3 months is often enough for many workloads. Add to this Innodb stats are less accurate by nature which means you can allow more data change while your
index stats remain as good as new.
Looking at stats accuracy is however a wrong way to look at the problem. Your index stats are a bit off, so what ? What really matters is not how accurate stats are but how good plans you're getting for your queries. If you're getting as good plans as with perfect stats why bother updating them ?
Also note many simple "queries" (using constants for index accesses) will not use index cardinality data at all but will estimate number of rows during query execution.
I typically look at ANALYZE TABLE and adding it to the table if I see having it run helps to get good plans. If query plans are good or bad independently of it being run there is need to bother - for bad plans use FORCE INDEX or change the query and report MySQL Optimizer Bug
But now lets see in the difference of behavior of ANALYZE TABLE for MyISAM vs Innodb.
I used the following simple table for tests:
PLAIN TEXT SQL:- CREATE TABLE `antest` (
- `i` int(10) UNSIGNED NOT NULL,
- `c` char(80) DEFAULT NULL,
- KEY `i` (`i`),
- KEY `c` (`c`,`i`)
- ) ENGINE=MyISAM DEFAULT CHARSET=latin1
I have populated it with data with following true cardinality:
PLAIN TEXT SQL:- mysql> SELECT count(DISTINCT c) FROM antest;
- +-------------------+
- | count(DISTINCT c) |
- +-------------------+
- | 101 |
- +-------------------+
- 1 row IN SET (0.36 sec)
- mysql> SELECT count(DISTINCT i) FROM antest;
- +-------------------+
- | count(DISTINCT i) |
- +-------------------+
- | 101 |
- +-------------------+
- 1 row IN SET (0.20 sec)
- mysql> SELECT count(DISTINCT i,c) FROM antest;
- +---------------------+
- | count(DISTINCT i,c) |
- +---------------------+
- | 10201 |
- +---------------------+
- 1 row IN SET (0.43 sec)
Lets see how stats look for MYISAM:
PLAIN TEXT SQL:- mysql> SHOW INDEX FROM antest;
- +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | TABLE | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment |
- +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | antest | 1 | i | 1 | i | A | NULL | NULL | NULL | | BTREE | |
- | antest | 1 | c | 1 | c | A | NULL | NULL | NULL | YES | BTREE | |
- | antest | 1 | c | 2 | i | A | NULL | NULL | NULL | | BTREE | |
- +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- 3 rows IN SET (0.00 sec)
Aha as you can see there is no cardinality stored with table as ANALYZE did not run yet.
PLAIN TEXT SQL:- mysql> SHOW INDEX FROM antest;
- +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | TABLE | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment |
- +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | antest | 1 | i | 1 | i | A | 101 | NULL | NULL | | BTREE | |
- | antest | 1 | c | 1 | c | A | 101 | NULL | NULL | YES | BTREE | |
- | antest | 1 | c | 2 | i | A | 10240 | NULL | NULL | | BTREE | |
- +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- 3 rows IN SET (0.01 sec)
As you can see after running ANALYZE we have exact cardinality for i and c columns, with cardinality for the pair (c,i) looks a bit off but is within 0.5% of the correct value so we can count on MyISAM values as almost exact.
As you see ANALYZE table tool a little bit of time to run (even for this very small table) this is because ANALYZE does index scans to find number of exact values in the table.
Now let us populate antest_innodb table which is same but uses Innodb format:
PLAIN TEXT SQL:- mysql> INSERT INTO antest_innodb SELECT * FROM antest;
- Query OK, 245760 rows affected (54.29 sec)
- Records: 245760 Duplicates: 0 Warnings: 0
- mysql> SHOW INDEX FROM antest_innodb;
- +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | TABLE | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment |
- +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | antest_innodb | 1 | i | 1 | i | A | 245900 | NULL | NULL | | BTREE | |
- | antest_innodb | 1 | c | 1 | c | A | 245900 | NULL | NULL | YES | BTREE | |
- | antest_innodb | 1 | c | 2 | i | A | 245900 | NULL | NULL | | BTREE | |
- +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- 3 rows IN SET (0.00 sec)
Very interesting result - after loading the data with INSERT in Innodb table we do not get NULL cardinality as with MyISAM but instead we get very wrong cardinality which shows us index prefix is unique (245900 is estimate for the row count in the table)
It is worth to note if you do ALTER TABLE Innodb, same as MyISAM will internally run analyze as soon as table is rebuilt and values will be more sensible:
PLAIN TEXT SQL:- mysql> ALTER TABLE antest_innodb type=innodb;
- Query OK, 245760 rows affected, 1 warning (51.87 sec)
- Records: 245760 Duplicates: 0 Warnings: 0
- mysql> SHOW INDEX FROM antest_innodb;
- +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | TABLE | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment |
- +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | antest_innodb | 1 | i | 1 | i | A | 332 | NULL | NULL | | BTREE | |
- | antest_innodb | 1 | c | 1 | c | A | 18 | NULL | NULL | YES | BTREE | |
- | antest_innodb | 1 | c | 2 | i | A | 20491 | NULL | NULL | | BTREE | |
- +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- 3 rows IN SET (0.00 sec)
Note however how much are these values off from reality. The "i" key cardinality is overestimated 3 times, "c" key prefix cardinality is underestimated 5 times and the combined (c,i) key cardinality is overestimated 2 times. So Innodb stats are are very inexact. Fortunately for most queries which use these stats accuracy at the order of magnitude is enough. Sometimes it is not and you're thinking why a hell it could be picking this strange plan.
Let us run ANALYZE TABLE for Innodb couple of more times to see how values change:
PLAIN TEXT SQL:- mysql> analyze TABLE antest_innodb;
- +--------------------+---------+----------+----------+
- | TABLE | Op | Msg_type | Msg_text |
- +--------------------+---------+----------+----------+
- | test.antest_innodb | analyze | STATUS | OK |
- +--------------------+---------+----------+----------+
- 1 row IN SET (0.00 sec)
- mysql> SHOW INDEX FROM antest_innodb;
- +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | TABLE | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment |
- +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | antest_innodb | 1 | i | 1 | i | A | 338 | NULL | NULL | | BTREE | |
- | antest_innodb | 1 | c | 1 | c | A | 18 | NULL | NULL | YES | BTREE | |
- | antest_innodb | 1 | c | 2 | i | A | 20491 | NULL | NULL | | BTREE | |
- +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- 3 rows IN SET (0.00 sec)
- mysql> analyze TABLE antest_innodb;
- +--------------------+---------+----------+----------+
- | TABLE | Op | Msg_type | Msg_text |
- +--------------------+---------+----------+----------+
- | test.antest_innodb | analyze | STATUS | OK |
- +--------------------+---------+----------+----------+
- 1 row IN SET (0.00 sec)
- mysql> SHOW INDEX FROM antest_innodb;
- +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | TABLE | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment |
- +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | antest_innodb | 1 | i | 1 | i | A | 92 | NULL | NULL | | BTREE | |
- | antest_innodb | 1 | c | 1 | c | A | 384 | NULL | NULL | YES | BTREE | |
- | antest_innodb | 1 | c | 2 | i | A | 20491 | NULL | NULL | | BTREE | |
- +---------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- 3 rows IN SET (0.00 sec)
As we see subsequent runs change stats dramatically. For c prefix we got value changed to become 15 times larger. So Innodb stats are both inexact and unstable. So restarting server with Innodb may change stats dramatically and affect some query plans. You also may be getting different plans on different slaves with same data.
Another difference when it comes from handling the statistics comes from NULL handling.
MyISAM has a special variable which controls if NULLs should be considered equal when computing stats:
- mysql> SHOW VARIABLES LIKE "myisam_stats_method";
- +---------------------+---------------+
- | Variable_name | Value |
- +---------------------+---------------+
- | myisam_stats_method | nulls_unequal |
- +---------------------+---------------+
- 1 row IN SET (0.00 sec)
Too see the difference let me set column "c" to NULL in both tables and see how values change:
PLAIN TEXT SQL:- mysql> UPDATE antest SET c=NULL;
- Query OK, 245760 rows affected (11.48 sec)
- Rows matched: 245760 Changed: 245760 Warnings: 0
- mysql> UPDATE antest_innodb SET c=NULL;
- Query OK, 245760 rows affected (1 min 20.19 sec)
- Rows matched: 245760 Changed: 245760 Warnings: 0
- mysql> SHOW INDEX FROM antest;
- +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | TABLE | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | NULL | Index_type | Comment |
- +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
- | antest | 1 | i | 1 | i | A | 101 | NULL | NULL | | BTREE | |
Managing MySQL Configuration Files
Amanda Delivers Windows Backup and Recovery to Community
Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4 Latest Version Leverages Windows Volume Shadow Service (VSS) to Backup Open Files and Open Standard Formats for Data Compression and Encryption.
The Amanda Project, a ZmandaTM sponsored and community-supported open source collaboration, announces the availability of Amanda 2.6.0p2. This latest version provides open file backup and recovery for Windows XP, Vista desktops and laptops and Windows 2003, 2008 servers, enabling system administrators to perform backup without impacting users and applications. By leveraging the standard zip format for backup images, Amanda 2.6.0p2 gives users the flexibility to recover their data with or without Amanda software. This version, complete with a Windows Installer and a 15-minute configuration guide, is available for free download. With this Zmanda-led release, Amanda further extends its leadership as the most comprehensive and popular open source network backup and recovery software protecting major Linux, Unix and Windows platforms. Amanda 2.6.0p2 is built on a modular software architecture optimized for developers looking to extend Amanda to support the latest hardware and software storage technologies.
?Amanda has a strong and thriving community that is anxious for Windows support,? said Paddy Sreenivasan, Amanda project leader at Zmanda. ?With this release, Amanda is now the most stable and reliable open source backup and recovery project for heterogeneous environments.?
Amanda is the world?s most popular open source backup and archiving software, protecting more than half a million servers and desktops worldwide. Amanda allows system administrators to set up a single backup server to back up hosts to a tape or disk-based storage system. Amanda uses native data extraction and compression facilities and can back up a large number of workstations and servers running various versions of Linux, Unix or Microsoft Windows operating systems.
Amanda was awarded the ?Best Open Source Software? in the storage category by InfoWorld in August 2008 and was the only backup finalist in the 2008 SourceForge Community Choice Awards as the ?Best Tool or Utility for SysAdmins.? Amanda was certified by the Department of Homeland Security as being free of security defect.
For more information on Amanda 2.6.0p2, to download the software or to join in this community effort, please visit: http://amanda.zmanda.com.
An enterprise edition of Amanda is offered through Zmanda as an annual subscription. The Amanda Enterprise edition provides users with an easy-to-use web-based Zmanda Management Console, application support for Oracle databases, Microsoft SQL Server and Exchange Server, comprehensive reporting capabilities and three levels of support complete with knowledge base access, email and phone support, case management, software enhancements and security updates. For more information, please visit: http://www.zmanda.com/amanda-enterprise-edition.html.
———————–
Ann Ruckstuhl
ann@zmanda.com

