Twitter has had many outages recently. On May 17th, 2008 http://blog.twitter.com/2007/05/devils-in-details.html was posted and says:

What went wrong? We checked in code to provide more accurate pagination, to better distribute and optimize our messaging system?basically we just kept tweaking when we should have called it a day. Details are great but getting too caught up in them is a mistake. I’ve been CEO of Twitter for two months now and this an awesome lesson learned. We’re seeing the bigger picture and Twitter is back. Please contact us if something isn’t working right (with Twitter that is).

(in other news, that post was made on May 17th and does not show up on http://blog.twitter.com, which it should, between the May 16th and May 19th posts. I found a reference in other posts and had to search the site to find that post).

A real “awesome lesson learned” is “do not tweak production without testing first.” In every job I have had I have first learned and then taught the concept of “test everything possible.” Which Twitter has not learned yet, because http://blog.twitter.com/2008/05/not-true.html, posted on Tuesday May 20th, states:

We caused a database to fail during a routine update early this afternoon.

As someone who has years of experience working with MySQL, and before that was a systems adminsitrator; as someone who was referred to as “the MySQL Queen” yesterday (by someone who wanted me to test their product, so yes, they were flattering me); I can assure you:

no matter how “routine” a change is, if you do it on production without testing it first, you are playing with fire, and 95% of the fires caused by not testing first are completely preventable.

I will repeat this, because repetition is important to learning concepts.

no matter how “routine” a change is, if you do it on production without testing it first, you are playing with fire, and 95% of the fires caused by not testing first are completely preventable.

With a proper testing environment, 19 out of 20 “whoops, didn’t expect THAT from a routine change!” issues are caught. And I can tell you that often “routine changes” cause unexpected results.

Now, I was online during an outage, and http://twitter.com/home was showing their “site isn’t working” page for at least 3 hours between 2 and 5 am EDT yesterday (Tuesday, May 20th, 2008).

So…..there is no read-only copy around that Twitter could use? Maybe I cannot tweet, but I should at least be able to read what was done before!

Of course, since last week Twitter has done the opposite — often I can see the most recent 20 or so posts, but not anything prior. Now, I understand that it is hard to get all the histories for the people I follow. But it only needs to be done once, and could then be cached — “Posts from who Sheeri follows on 5/20″. It would not be difficult, and I would be OK with the functionality changing such that “once you follow a new person, their tweets prior to when you followed them do not show up in the history.”

Alternatively, you could go the snarky way and say: http://www.techcrunch.com/2008/05/20/twitter-something-is-technically-wrong/ states:
What would be great is if Twitter just moved their blog to another platform so that it doesn?t fail when users need it most.

I am not a huge user of rails. But I will say that given the content of the public announcements, the platform is not the problem. It is the code release process that is the problem. Maybe there’s “agile development” happening, paired programming and code reviews. But there is not adequate testing.

Twitter — if you truly need scaling help, please ask for help — I know Pythian would be happy to help. However, if it really is as it seems — that basic good practice is not being followed — I would like to remind you that backups are really important too, just on the off chance that backups are not happening.

InnoDB maintains two checksums per buffer pool block. Old formula of checksum, and new formula of checksum. Both are read, both are written. I guess this had to be some kind of transition period, but it obviously took too long (or was forgotten). Anyway, disabling checksums code entirely makes single-thread data load 7% faster - though in parallel activity locking contention provides with some extra CPU resources for checksum calculation.
Leaving just single version of checksum would cut this fat in half, without abandoning the feature entirely - probably worth trying.

Update: Benchmarked InnoDB checksum against Fletcher. Results were interesting (milliseconds for 10000 iterations):

Algorithm: InnoDB Fletcher - 826 453 -O2: 316 133 -O3: 42 75

So, though using Fletcher doubles the performance, -O3 optimizes InnoDB checksumming much better. How many folks do run -O3 compiled mysqld?

At this weeks' geekdinner some people wondered what was up with T-Dose, and guess what .. their CFP has been out for ages.

Last year I just catched the end of Bert's talk and Some Abstract Type has also been spotted there before.

No reason to miss this year's edition.

As announced by Eduardo, I have started playing with The Aquarium.
That does not mean that I sleep with the fishes, but that I am playing along with the group. And besides, dolphins are not fishes, but nonetheless they should be at ease in an aquarium.

This blogging is part of MySQL integration in Sun. Slowly but surely we are becoming aware of our surroundings and we are engaging the rest of the Sun communities.

We are learning.

Twitter appears to depend on MySQL and Sun. So much so that a failed (MySQL?) database server was worthy of downtime, a blog post and several hours of recovery. Is it time to stage an intervention before PostgreSQL or Oracle get the account? Should we send in Percona, Proven Scaling and the other expert consultants?

Was there no backup server to replace the failed db server? Twitter previously suffered a long outage from a long recovery when using Joyent after a storage server running ZFS had internal corruption. I guess that even ZFS needs a filesystem repair tool.

Sheeri's post got me to thinking about this. I don't think we are singling out Twitter. It is just that their production problems have gotten a lot of publicity and post-mortems are an effective way for DBAs to learn. We should thank them for sharing details as most of us would not be able to do that.

The reason for the database server crash was too many connections. How might that cause a crash? I can only guess. This problem should not cause a crash.

• max_connections was set too high and the process ran of of address space from all of the thread stacks or file descriptors for all of the sockets for the connections
• max_connections limit was reached and someone killed the mysqld process rather than connecting as a user with SUPER privileges and killing many of the connections

Why did the server require several hours to recover? According to this post, they use InnoDB. So the long recovery time is either file system repair after a server crash, but that should be fast if they use Sun and ZFS, right? So I will guess that the problem was a long crash recovery time. The most likely culprit for slow crash recovery is undo of a long running insert/update/delete or DDL statement, and the way to avoid that is to not do those statements when you can't afford the slow recovery. InnoDB doesn't do parallel crash recovery, Oracle does, sometimes it pays to pay. The other culprit for slow crash recovery is that the server doesn't have sufficient random IO bandwidth to handle the recovery workload, and that workload is rarely tested. InnoDB has a few features that allow the amount of pending IO to be limited. You can set innodb_max_dirty_pages_pct. Oracle more features in that area that allow the DBA to control the expected time to recover after a crash.

One feature that InnoDB needs is the ability to change the rate of background IO. The limits are currently set with the assumption that the server can do 100 IOPs max and most servers today can do much more than that. The upcoming InnoDB patch at code.google.com will have a variable by which the server IO capacity can be set to allow the background IO rate to be tuned.

I hope we get more details because we can learn from this.

One of the things people tend to forget is the ability for web servers to compress content before sending it back to client. Client’s browser then uncompresses the data and displays it to the user. Pretty much all of the recent browsers support gzip compression. In this post, I will go over [...]

Following a link from the High Scalability blog, I found this really great article about scalability practices, as told by Randy Shoup at eBay. Randy is very good at explaining some of the more technical aspects in more or less plain English, and it even helped me find some wording I was looking for to help me explain the notion (and benefits) of functional partitioning. He also covers ideas that apply directly to your application code, your database architecture (including a little insight into their sharding strategy), and more. Even more about eBay’s architecture can be found here.

addthis_url = 'http%3A%2F%2Fwww.protocolostomy.com%2F2008%2F05%2F29%2Fscalability-best-practices-ebay%2F'; addthis_title = 'Scalability+Best+Practices%3A+eBay'; addthis_pub = 'jonesy';

Marten Mickos, former MySQL CEO, now Senior SVP with Sun Microsystems, is expected in Rome, at the local University, called "La Sapienza" (= Wisdom or Knowledge). The event, on May 30, is a mix of private and public occurrences. Marten will meet local customers and Sun officials, before speaking in front of an audience at the University.

More information at the other speakers' blogs. Ivan Zoratti and Giuseppe Maxia

Storage Snapshots are excellent tools in arsenal of a system administrator to create quick and consistent backups of their databases and applications. Snapshot is a “picture” of a filesystem at a point-in-time. In most modern snapshot implementations, this “picture” is not a full copy of the data, but rather a set of pointers to the data.

Here is a list of current industry leading snapshot technologies:

• LVM Snapshots: Snapshot capability built into the Linux Volume Manager (LVM) - the default option on a Linux system.

• ZFS Snapshots: Snapshot capability built into the ZFS filesystem on Solaris and OpenSolaris

• NetApp SnapShots: A feature of the WAFL® (Write Anywhere File Layout) filesystem

• Windows VSS: Volume Shadow Copy Service from Microsoft introduced in Windows Server 2003. VSS is also available with Windows XP, Vista, and 2008 Server.

• VxFS Snapshots: Snapshot capability built into the Veritas File System

• EMC SnapView: Snapshot capability on the EMC CLARiiON storage systems

• IBM FlashCopy: Available in the IBM Storage Subsystems (DS8000, DS6000, ESS 800, SAN VC)

• EqualLogic Smart Copy: Feature in the Dell EqualLogic PS Series

Our Zmanda Recovery Manager for MySQL product uses storage snapshots as one of the technologies to do a quick full backup of the MySQL database. We currently support LVM, ZFS, NetApp, VSS and VxFS Snapshots. Rest are coming soon…

I am happy to announce that the first Workbench plugin written by the community was published recently.Daniel Haas has written a code generation plugin for Propel, a database abstraction layer for PHP. You can download the plugin from Daniel’s blog that can be found here it is published under the GPL. He also provides the necessary steps to install the script. This is amazing in several ways. First, we have not yet published a comprehensive tutorial how to write plugins and scripts for Workbench because we have been fully loaded with other stuff. Vlad did a tutorial on this at the MySQL Users Conference but the Wiki pages have not been written yet. So Daniel took the hard way, figuring out a lot of things all by himself. Second, we are working on a better interface for plugins that makes writing them much easier. Again, this has not happened yet.Starting with MySQL Workbench 5.1 we will provide all this and in addition we are going to launch a dedicated section on this page that allows people to share their Workbench plugins (think Firefox Extensions). At the same time Sergei is working on an easier way to install plugins with simple drag’n'drop from the web browser.We are looking forward to a large set of plugins that will make Workbench even more powerful for developers in the future. 

We introduced a new library to use with our update-feature inside Workbench. With the new lib (yassl) it’s possible to use SSL encryption for the authentication/download of new SE releases. This was not possible with libcurl because we’re not allowed to use/bundle openSSL. As this update is included staring with 5.0.22 all SE users will have to download version 5.0.22 manually from the enterprise pages one more time. Please logon to https://enterprise.mysql.com/software/gui_tools.php with your mysql username and password, download the latest release and launch the installer manually. Sorry for the inconvenience.

I'm proud to announce that after significant wrangling (just suggesting did the trick, actually ;-), I've found Sebastian Bergmann willing to visit Australia, and teach a 3-day workshop Quality Assurance in PHP Projects. It's scheduled 4-6 August in Melbourne.

Many applications using MySQL are written in PHP... this three-day workshop will introduce PHP Developers to writing unit tests for the backend and system tests for the frontend of a web application as well as managing the quality from development to deployment and maintainance using tools such as PHPUnit, Selenium RC, phpUnderControl, PHP_CodeSniffer, and PHP_Depend. Sebastian is the author of PHPUnit, and long-time contributor to PHP itself.

Pricing is AUD 1695 + GST. Since this is a workshop, the number of seats will be limited to 10-12. Registrations before June 15th receive $50 off.

Around the course dates Sebastian will also be available for consulting and/or in-house training in the Australia and New Zealand region, simply contact Open Query to discuss.

We’re looking for experienced OO-PHP programmers. Read all about it here (in Danish only; sorry ’bout that).

As of today, the third, final and largest code release for Open High Availability Cluster has been made available.  This now means that all of the Open HA Cluster code is available as free software.   This will allow developers and admins to access and build complete HA solutions built on source code from the OpenSolaris project.

Right before the launch I grabbed some time with Meenakshi Kaul-Basu, the engineering director at Sun responsible for Availability products, and whose group the Open HA Cluster falls under.  Take a listen to Meenakshi's explanation of the event and her insight:

My interview with Meenakshi (9:29)  Listen (Mp3) Listen (ogg)

Some of the topics we tackle:

• The first phase of the open code release was the Cluster agent in June of last year.  Phase two was in December and and was for the Geographic edition.
• This third release represents nearly two million lines of code (and this number doesn't include the test code).
• In addition to code it also comes with docs and internationalization functionality as well as the above mentioned automated testing.
  
• Open HA cluster is tightly integrated with built-in OpenSolaris features like ZFS technology.
• Integration of Open HA Cluster with free software apps like ,Apache Tomcat,  MySQL, PostreSQL, Glassfish...
  
• Where can you get started? (Here) 
  
• How to join the discussion (HA cluster forum)

BTW: for a bevy of engineering details, check out Oasis, the Sun Cluster group blog.

Pau for now...

I usually get strange looks when I complain about memory handling inside InnoDB. It seems as if terabytes of RAM are so common and cheap, that nobody should really care about memory efficiency. Unfortunately for me, I do.

Examples:

• The infamous Bug#15815 - buffer pool mutex contention. The patch for the bug added lots of small mutexes, and by ‘lots’ I mean really really lots - two mutexes (and rwlock structure) for each buffer pool page. That makes two million mutexes for 16GB buffer pool, um, four million mutexes for 32GB buffer pool, and I guess more for larger buffer pools. Result - 16GB buffer pool gets 625MB locking tax to solve a 8-core locking problem. Solution? Between giant lock and armies of page mutexes there lives a land of mutex pools, where locks are shared happily by multiple entities. I even made a patch, unfortunately it gets some ibuf assertion after server restart though at first everything works great :)
• InnoDB data dictionary always grows, never shrinks. It is not considered a bug, as it isn’t memory leak - all memory is accounted by (hidden) dict_sys->size, and valgrind doesn’t print errors. 1-column table takes 2k of memory in InnoDB data dictionary, a table with few more columns and indexes takes already 10k. 100000 tables, and 1GB of memory is wasted. Who needs 100000 tables? People running application farms do. Actually, there even is a code for cleaning up data dictionary, just wasn’t finished, and is commented out at the moment. Even worse, the fix for #20877 was a joke - reducing the in-memory structure size, still not caring about structure count. And of course, do note that every InnoDB partition of a table takes space there too…

So generally if you’re running bigger InnoDB deployment, you may be hitting various hidden memory taxes - in hundreds of megabytes, or gigabytes - that don’t provide too much value anyway. Well, memory is cheap, our next database boxes will be 32GB-class instead of those ‘amnesia’ 16GB types, and I can probably stop ranting :)

You may have noticed a new author at TheAquarium: Giuseppe Maxia, The Data Charmer, a long time member of the MySQL community team. Giuseppe also writes at (@Blogspot, @Blogs.Sun.Com) and will help us cover the happenings in the MySQL community.

A very warm welcome, Giuseppe!

Andi and James will also start posting to the TheAquarium soon and Arun has also resumed his contributions. This should help with our coverage of topics - and will return me some of my free time!

MySQL Server does not require you to specify name of the index if you're running ALTER TABLE statement - it is optional. Though what might be good practical reasons to specify the key name or omit ?

Things what you should be looking at is how MySQL names indexes automatically as well as what maintaining the indexes.

Lets first speak about naming. If you do not specify index name MySQL will name index by the first column of index created, if there is such index already it will add numeric suffix to it, for example:

PLAIN TEXT SQL:

1. mysql> CREATE TABLE t1(i int, j int);
2. Query OK, 0 rows affected (0.01 sec)
3.  
4. mysql> ALTER TABLE t1 ADD KEY(i,j);
5. Query OK, 0 rows affected (0.03 sec)
6. Records: 0  Duplicates: 0  Warnings: 0
7.  
8. mysql> ALTER TABLE t1 ADD KEY(i);
9. Query OK, 0 rows affected (0.00 sec)
10. Records: 0  Duplicates: 0  Warnings: 0
11.  
12. mysql> SHOW CREATE TABLE t1 \G
13. *************************** 1. row ***************************
14.        TABLE: t1
15. CREATE TABLE: CREATE TABLE `t1` (
16.   `i` int(11) DEFAULT NULL,
17.   `j` int(11) DEFAULT NULL,
18.   KEY `i` (`i`,`j`),
19.   KEY `i_2` (`i`)
20. ) ENGINE=MyISAM DEFAULT CHARSET=latin1
21. 1 row IN SET (0.00 sec)

Such automatically table generated names are not really helpful for multiple column indexes - you can't easily see what is the index MySQL trying to use from explain, such as if you have some smart index naming conversion such as idx_i_j for the first index it would be very explanatory.

Next comes the question of maintaining the indexes.
What happens if you try to create the same index without specifying index name ? MySQL will happily create (and maintain) as many duplicate keys as you like without even giving you a warning:

PLAIN TEXT SQL:

1. mysql> ALTER TABLE t1 ADD KEY(i);
2. Query OK, 0 rows affected (0.00 sec)
3. Records: 0  Duplicates: 0  Warnings: 0
4.  
5. mysql> ALTER TABLE t1 ADD KEY(i);
6. Query OK, 0 rows affected (0.00 sec)
7. Records: 0  Duplicates: 0  Warnings: 0
8.  
9. mysql> ALTER TABLE t1 ADD KEY(i);
10. Query OK, 0 rows affected (0.01 sec)
11. Records: 0  Duplicates: 0  Warnings: 0
12.  
13. mysql> SHOW CREATE TABLE t1\G
14. *************************** 1. row ***************************
15.        TABLE: t1
16. CREATE TABLE: CREATE TABLE `t1` (
17.   `i` int(11) DEFAULT NULL,
18.   `j` int(11) DEFAULT NULL,
19.   KEY `i` (`i`,`j`),
20.   KEY `i_2` (`i`),
21.   KEY `i_3` (`i`),
22.   KEY `i_4` (`i`),
23.   KEY `i_5` (`i`)
24. ) ENGINE=MyISAM DEFAULT CHARSET=latin1
25. 1 row IN SET (0.00 sec)

However if you would specify index name MySQL will complain if you try to create index with same name again:

PLAIN TEXT SQL:

1. mysql> ALTER TABLE t1 ADD KEY idx_i(i);
2. Query OK, 0 rows affected (0.02 sec)
3. Records: 0  Duplicates: 0  Warnings: 0
4.  
5. mysql> ALTER TABLE t1 ADD KEY idx_i(i);
6. ERROR 1061 (42000): Duplicate KEY name 'idx_i'

Adding indexes without name specified is very common reason why systems tend to get duplicate indexes. BTW make sure you check yours with mk-duplicate-key-checker.

For example you may have run ALTER TABLE on the SLAVE server to check things out and when run it again on the master to apply changes to all SLAVEs... and so you get two copies of indexes on the slave without any notice.

The other side of the problems comes from the automatic naming - depending on order you add indexes indexes can get different names which makes scripted upgrade and downgrade processes complicated.

If you use auto generated index names you may drop the wrong indexes as part of upgrade process just because somebody was adding custom indexes to the box - this is especially concern for cases when deployment is done by the end user outside of developing organization as in this case there is little control over what customization user may have done.

As a Summary - if is good to name your indexes manually both for documentation purposes and ease of maintenance. This may sound pretty obvious but few organizations using MySQL have consistent process of always naming indexes on production systems.

Entry posted by peter | 9 comments

Add to: | | | |

The survey is off and running!! As Mark Schoonover said, we have had over 100 surveys completed already. That is a great start. All good things seem to have challenges however and kwiksurveys, who is hosting the survey, has decided that this weekend would be great time to do upgrade to some newer faster servers. So that is good right? Well unfortunately they are saying the site will be down from Friday May 30th at 15:00 GMT through Monday June 1st, 09:00 GMT. So please keep this in mind!!

On towards 1000!!

So, Ray Ozzie has gone on the record to suggest that open source could be a bigger threat to Microsoft than Google is. Savio isn't buying that line, and I'm not sure that I do, either.

Let's be clear about what Ozzie actually said:

...[O]pen source [i]s much more potentially disruptive [than Google].

Open source has disruptive potential. Google is disruptive now. Google is making money now in markets that Microsoft covets, while open source is not cutting into a single Microsoft revenue stream. Not one. Red Hat and Novell's SUSE are almost entirely eating away at the Unix market, while MySQL is creating new markets with web properties. Open source? It doesn't (today) make a dent in Office, Windows, XBox, Dynamics, etc.

So why is open source potentially so disruptive to Microsoft? Two reasons.

...

A