The MySQL Conference was held once again at the Santa Clara Convention Center, but hosted by Percona this year. Percona did a great job of organizing the show on fairly short notice, with a good program, full exhibits area, and a few thousand attendees.
(The rumour I heard was that O’Reilly declined to host the conference again without promotional money from Oracle, and Percona decided to go it alone without MontyProgram. Oracle even refused to send speakers.)
My favorite talks were Vadim’s “SSDs and MySQL” lecture, and Giuseppe’s “New replication features in 5.5. and 5.6″ lecture. Hopefully they can update them and present them again next year.
Some suggested improvements for next year:
- BoFs should be two per nite, starting at 6 pm or 7 pm, not one at 9 pm
- every year, I suggest that there be a room for storage and SSD vendors to demonstrate systems
- after the closing keynote, possibly have a hacking session area.
Rick James from Yahoo! talked about improving performance with MySQL.
We used to work in the same group at Yahoo!, so he played good cop and I played bad cop.
The audience had some interesting ideas:
- try using SSL with replication to reduce statement corruption
- monitor query cache for problems like fragmentation. Consider using flush query cache periodically if it’s an issue.
- use partitions if doing heavy deletes periodically. Ie. Delete 10 million rows daily or weekly. Maybe partitions will help?
The MySQL Evolution
Peter Zaitsev and Baron Schwartz, Percona, Inc.
Making LAMP a Cloud
Marten Mickos, Eucalyptus
- the usual Marten talk (stroll through memory lane, capped by how he’ll make money off Open Source)
The New MySQL Cloud Ecosystem
Brian Aker, hpcloud.com
Multi-tenancy – Your neighbours can throw a party anytime. (And your mom won’t be able to see your online pics.)
RedDwarf is an AWS RDS clone.
HP’s RDS based on Percona 5.5. Sweet.
REST interface, cloud staff not allowed to login. Icky.
STEC had a DBT2 demo in their booth.
Introducing EnhanceIO SSD caching software soon.
Andrew Aksyonoff, Author
LIKE – that’s not really search. It’s grep on your database.
Database full-text search is a bastard child. No knobs ie. for short words
Solr and ElasticSearch based on Lucene
Mnogosearch, xapian, ferret, zettair
Keyword search is now a commodity but …
- maintenance, performance, scalability are still important considerations
Expect 300 qps/core for 1 million documents, 1.3 GB, prolly comparable to SOLR
For large sites, indexing speed is more important
Some use cases:
- 200 servers
- CL over 200,000,000 queries per day
- 30 billion documents about 50 TB
- Wikipedia on a 64 GB cell phone
Thinking about auto-cluster configuration but Skynet is not reasonable. Some assistance needed.
Relevance should be more than word frequency. Sphinx has a dynamic expression based ranker.
- same table as Baron, so talked about who’s maintaining Percona Toolkit (2 employees now: Daniel Nichter and somebody in S.A.)
- seems complicated
- what happens if compression algorithm is changed later and you’re applying old logs?
- Facebook PHP profiler Open Sourced but no longer used
- if you use slow query log, log all
- use apache customlog format and load into mysql
What’s New In MySQL 5.5 and 5.6 Replication
Giuseppe Maxia, Continuent, Inc.
5.6 Global Unique Transaction IDs
Must enable following 4 options on master:
- CREATE TABLE SELECT
- MyISAM statements, including on mysql.*, except GRANT/REVOKE statements
- automatic tool included with MySQL Workbench now to check for safe statements.
- MySQL 5.6 is alpha quality for these features.
Production War Stories: Lessons from the Frontlines
- Good idea to QA new mysql release in case of a reserved keyword error, slow replication, etc.
- mysqldump and restore still necessary for new features like barracuda or innodb file per table.
tcpdump -i bond0 -s0 3306 ... pt-query-digest pt-log-player
Database was 2 TB using 32 GB ram and lots of disks.
Using 4x memory with Innodb compression and 8 KB page size and slower disks resulted in 4x improvement.
- schemadoc can be used to create javadoc-style listings from MySQL schemas and comments
Continuent, Robert Hodges, CEO
- airline overbooking example
- Business processes are eventually consistent if you think about it
NuoDB, Robert Buck
- funny comedy sketch with Mr. Bill (in costume) as a database getting physically compressed, replicated, etc.
SSDs and MySQL
Vadim T, Percona
- about 30 people attended, including 4 SSD vendors
- best war story: a Softlayer customer lost his RAID 10 databases when Crucial M4 expired after 5000 hours
- one non-Facebook person was using FlashCache
- one person wondered about using virtualization with many databases on flash
MySQL: Still the Best Choice for Mission-Critical Data
Sam Ghods (Box)
What Comes Next
Mark Callaghan, Facebook
“If my SSD has 100,000 IOPS, and MySQL only uses 10,000 IOPS, can I get a discount from my flash vendor?”
To some extent this panel got high-jacked by a security vendor. Certainly HIPAA users always want more auditing features, but I feel the existing MySQL GRANT system is fine for 99% of installations, and as the first Internet database, is more network-hardened than Oracle or SQL Server.
Upgrading MySQL: Best Practices
Peter Zaitsev, Percona
- it’s a good idea to test a new MySQL version before upgrading in production
- some possible issues:
– new reserved keywords
– different replication performance
- upgrade time is the best time for doing things that require planning like moving to innodb_file_per_table
- since upgrades don’t happen very often for most people, consider calling Percona.
MySQL and SSD: usage and tuning
Vadim Tkachenko, Percona
- benchmarks lie, but SSD benchmarks take lying to a new level: performance depends on controller state and how full the SSD is, so test over a 24 hour period
- excited about Fusion IO’s new Atomic Writes API (InnoDB uses 16 KB pages, which is a multiple of 1K or 4K disk blocks. Therefore 2 writes must be made, 1 to the double-write buffer, then to SSD. With this API, a modified InnoDB will not need the double-write buffer, halving write IO and doubling SSD lifetime.)
Verifying MySQL Replication Safely With pt-table-checksum 2.0
Daniel Nichter, Percona
- pt-table-checksum 2.x is gentler on databases, and less likely to abort for no clear reason
pt-table-checksum -h host -u root --ask-pass
Thursday Closing Talk
- draw for exhibit bingo card attendees with several nice prizes (3 different tablets, $2,000 free hosting from Joyent, headphones)
- show appreciation for organizers, sponsors and attendees
I got my second conference MongoDB mug, so now I can reliably store coffee.
Conference Slides Hosted on Box.com
Percona Presentations for 2012
SphinxSearch Conference 2012
Global Transaction Identifiers Feature Preview
Oracle Blog: MySQL 5.5: What’s New in Replication
MySQL Manual 5.5: CHANGE MASTER TO Syntax