August 2011

Disclosure: Truth About MySQL 2012 Conference Planning

I love Percona Live.  I think it is a great meeting of the minds.  However, I do not think it is a good replacement for the big April MySQL conference.  In fact, neither does Baron Schwartz:

"The conference is organized and owned by MySQL, not the users. It isn’t a community event. It isn’t about you and me first and foremost. It’s about a company trying to successfully build a business, and other companies paying to be sponsors and show their products in the expo hall."
Baron Schwartz, April 23, 2008.
http://www.xaprb.com/blog/2008/04/23/like-it-or-not-it-is-the-mysql-conference-and-expo/

Switch "MySQL" for "Percona", and that is exactly what Baron said in today's announcement:

"Emphasis on business. We need a place where vendors, both open-source and closed-source, can showcase their products and services. This is the hand that feeds all of us. It’s good for Percona’s business, and it’s good for everyone else’s too."

...except in 2008, Baron was calling for a community conference because what was good for business was not good for the community.  

Let me pull out that last sentence from the quote.  Read it to yourself, and tell me if you feel warm fuzzies replacing "Percona" with "Oracle" or "MySQL":

"It’s good for _______’s business, and it’s good for everyone else’s too."

Here is a question for you:  Do you think Oracle will send lots of engineers to talk about current and future plans for MySQL to a conference that is open about saying "It is good for our business?"  

It does not matter if it is Percona, Blue Gecko, or PalominoDB, if Oracle has any business sense (and they make quite a bit of money, so signs are that they have business sense) they will not send engineers to a competitor's company-branded conference.

What does Percona's founder, Peter Zaitsev, have to say about the conference?  He's not really happy about it either:

"I would like to see the conference which is focused on the product users interests rather than business interests of any particular company (or personal interests of small group of people), I would like it to be affordable so more people can attend and I’d like to see it open so everyone is invited to contribute and process is as open as possible. "
Peter Zaitzev, April 23, 2008
http://www.mysqlperformanceblog.com/2008/04/23/conference-for-mysql-users/

Peter, I would like to see that too.  In fact, a small group of folks, including Giuseppe and myself, tried to make that happen, and we had SkySQL, MariaDB, Oracle and IOUG all supporting us. 

Giuseppe called for disclosure about the conference, so I will disclose this:  Baron was not truthful when he said "To the best of our knowledge, no one else was planning one".

Giuseppe asked for full disclosure, so here is a copy and paste of a Skype conversation I had with Percona's Tom Basil on June 29th, 2011:

 

 

Sheeri K. Cabral 6/29/11 12:10 PM 

I think we should develop some ideas in case O'Reilly doesn't end up having a MySQL conference....last year the announcement was late, and it was in May, and I'm starting to think they might not be doing a conference this year, since we haven't heard anything yet.

6/29/11 12:10 PM

if that's the case, I'd like to have a conference anyway, and I'd like to explore options with you, because we need a community-run conference (not Collaborate, but maybe a co-located summit).  And obviously you've had success with Percona Live, but a multi-day conference is really different.

6/29/11 12:11 PM

(FWIW I told Colin Charles in May that I was willing to help co-chair the conference, so I'm still willing to give my support in that area).

Tom Basil 6/29/11 12:14 PM 

Sheeri, can't talk now

Sheeri K. Cabral 6/29/11 12:14 PM 

*nod*  can we schedule a conf call maybe?

Tom Basil 6/29/11 12:14 PM 

Headed out in just few min

6/29/11 12:14 PM

yes, next week

6/29/11 12:14 PM

We tried to schedule conference calls but for almost 6 weeks we were pushed back.  
I had indeed offered to chair or co-chair a conference with Colin Charles, but I simply cannot lend a lot of logistical support to a Percona-branded conference.  I volunteer a lot, but it is for the benefit of the MySQL Community, not for the benefit of a company I do not work for.  
I hope that my openness and candor does not blacklist me from Percona events; I am a popular and sought-after speaker at MySQL events and it will be a loss to the community if that happens.
Percona should keep its Percona Live series.  However, the world's most popular open source database deserves and needs exactly what Peter, Percona's founder, said just a few years ago: "the conference which is focused on the product users interests rather than business interests of any particular company".
I believe Percona Live Santa Clara will be a successful event, and I will try to be a part of it.  I hope it will be as successful for the community as it will be for Percona's business.

 

Final Videos of Open DB Camp Online:

The final videos from Open DB Camp back in May in Sardinia, Italy are now online.  The full matrix of sessions, videos and slides can be found on the schedule page.

Hands on JDBC by Sandro Pinna - video

"MySQL Plugins, What are They? How you can use them to do wonders" by Sergei Golubchek of MariaDB - video

The State of Open Source Databases by Kaj Arnö of SkySQL - video

Coming soon, videos from OSCon Data!

Upcoming Free IOUG Webinar: Securing MySQL

Next week I will give a free IOUG webinar on Securing MySQL on Wednesday, August 10, 2011 from 11:00 AM - 12:00 PM CDT (17:00 GMT):

Securing MySQL is extremely important, but often is not done properly. I will explain the different ways to secure MySQL. In addition to securing users and privileges, file permissions and encrypted connectivity will be discussed. The MySQL server options that contribute to MySQL security will be pointed out, along with tips for eliminating unsecure external scripts. For those who want more auditing capabilities, this session will explain how to see all login attempts (successful and not) and how to lock out accounts with repeated failed logins. The session will conclude with guidelines about how to create security policies for your organization.

To register for this webinar, visit https://www1.gotomeeting.com/register/979260992.  

mydumper & myloader : fast backup and restore

At PalominoDB we do not normally use mysqldump for regular backups, but only in some circumstances (for example MySQL upgrade).
Lately we gave a try to mydumper as an alternative to mysqldump, and results are quite promising.
We found that mydumper performs very fast exporting both small and large datasets!!
We also found that the with large datasets restore with myloader doesn't perform a lot better than simple restore from mysqldump SQL dump: this depends from the storage engine and not from the client used to restore.

On a box we run 2 tests:
1) with a dataset that was fitting in the InnoDB buffer pool;
2) with a dataset larger than the InnoDB buffer pool.

TEST #1

We created 128 tables of 1M rows each, for a total dataset of 31GB on disk:
shell$ time ./sysbench --test=tests/db/parallel_prepare.lua --oltp-tables-count=128 --oltp-table-size=1000000 --mysql-table-engine=innodb --mysql-user=root --num-threads=12 run real 22m0.013s
user 204m22.054s
sys 0m37.430s

Doing the backup with mydumper:
shell$ time ./mydumper -t 8 -B sbtest
real 0m29.807s
user 2m35.111s
sys 0m26.102s

... and with mysqldump:
shell$ time mysqldump --single-transaction sbtest > sbtest.sql
real 6m24.607s
user 5m19.355s
sys 0m46.761s

Within this test, mydumper looks around 13 times faster than mysqldump.

We also tried compression, but I/O was fast enough to make compression only an unnecessary overhead: in other words, on that hardware and with this dataset, mydumper with compression was slower than mydumper without compression.

To complete the test, we tried recovery time, after deleting and recreating and empty database:
shell$ mysql -e "drop database sbtest ; create database sbtest"
shell$ time ./myloader -t 8 -d export-20110720-090954
real 9m12.548s
user 0m55.193s
sys 0m28.316s

shell$ mysql -e "drop database sbtest ; create database sbtest"
shell$ time ( echo "SET SQL_LOG_BIN=0;" ; cat sbtest.sql ) | mysql sbtest
real 46m46.140s
user 9m3.604s
sys 0m48.256s

With this dataset, restore time using myloader was 5 times faster than using the SQL dump from mysqldump.

TEST #2

Test #2 is very similar to test #1 , but with some different in the dataset:
48 tables instead of 128 tables;
10M rows on each table instead of 1M rows;
a total dataset on disk of 114GB instead of 31GB.

First, we created the tables with sysbench:
shell$ time ./sysbench --test=tests/db/parallel_prepare.lua --oltp-tables-count=48 --oltp-table-size=10000000 --mysql-table-engine=innodb --mysql-user=root --num-threads=12 run
real 107m24.657s
user 689m2.852s
sys 2m11.980s

Backup with mydumper:
shell$ time ./mydumper -t 8 -B sbtest
real 7m42.703s
user 15m14.873s
sys 2m20.203s

The size of the backup is quite big because not compressed: 91GB
On average, mydumper was writing on disk at around 200MB/s.

Backup with mysqldump:
shell$ time mysqldump --single-transaction sbtest > sbtest.sql
real 32m53.972s
user 20m29.853s
sys 2m47.674s

mydumper was again faster than mysqldump , but not as much as in the previous test: only 4 times faster.

Was now the time to measure recovery time:
shell$ mysql -e "drop database sbtest ; create database sbtest"
shell$ time ./myloader -t 6 -d export-20110720-171706
real 130m58.403s
user 4m5.209s
sys 1m51.801s

shell$ mysql -e "drop database sbtest ; create database sbtest"
shell$ time ( echo "SET SQL_LOG_BIN=0;" ; cat sbtest.sql ) | mysql sbtest
real 204m18.121s
user 34m33.520s
sys 3m43.826s

myloader is just a bit more than 50% times faster than importing the SQL dump from mysqdump

Conclusion from second test:
a) With larger dataset, mydumper slows down because the system does more I/O as the dataset doesn't fit in memory, but still way faster than mysqldump.
b) With larger dataset, load time with myloader slowed down a lot. Although, the root cause of the performance drop isn't mydumper , but:
- more I/O (dataset + dump don't fit in RAM);
- InnoDB inserts rate degenerates with bigger tables.

On the topic of InnoDB inserts rate degeneration with big tables , probably another blog post will follow.

Notes on hardware and configuration:
CPU: 2 x 6cores with HT enabled
96 GB of RAM
FusionIO

innodb_buffer_pool_size=64G
innodb_log_file_size=2047M
innodb_io_capacity=4000
innodb_flush_log_at_trx_commit=2
(binlog disabled)

More Videos from Open DB Camp

I have gotten to uploading more of the videos from Open DB camp in Sardinia, Italy back in May:

Henrik Ingo speaks about Xtrabackup Manager - video

Linas Virbalas speaks about "Flexible Replication: MySQL -> PostgreSQL, PostgreSQL to MySQL, PostgreSQL to PostgreSQL" - video - slideshare slides

MySQL to MongoDB replication (hackfest results) - video 

Robert Hodges of Continuent speaks about Multi-Master Replication: Problems, Solutions and Arguments - video

There are a few more videos from Open DB Camp to put up, then I start to put up the content from OSCon Data!

Syndicate content
Website by Digital Loom