What's wrong with this graph?

So I am currently working on a MySQL server that is showing behavior as depicted in the following graph.


There can be several possible reasons for these sudden drops. I am working on the issue and will be posting the results. Have you experienced something similar?

Is the Query Cache hurting you?

If you are using memcached, you may want to check your query cache hits : cache inserts ratio. On one of my servers I inherited this ratio was 0.35 indicating that having the query cache turned on was actually harmful. I believe at the very least you need to have a ratio of 1. I was chatting with Sheeri and we both were of the opinion that a ratio of 90 was a very healthy ratio. By turning query cache off by default, we can then selectively cache the queries we need.

Later when I was talking to Ronald, he said he really wanted to be able to query the query cache to see what queries are cached. If will indeed be really cool to see that implemented.

So since turning the query cache off, I have been witnessing more throughput as less resources are being spent to maintain the inserts to query cache (which wasn't being effectively used at all).

I wanted to make all of these a little bit more detailed posts but have so little time. However I did want to post something to may be, expand upon, later. Your comments, questions are welcome and appreciated.

MySQL Camp: Good Time Flies Fast

It's really hard for me to believe how time has just been flying. Ever since I got back from the very awesome MySQL Camp at Google headquarters, I have been planning to write about it but for one reason or another, I couldn't.

Short version is that I had a great time at the MySQL Camp. I got to meet a lot of new and old friends. It was like a dream come true to hang out with everyone. Very special thanks to everyone I met at the conference as I had some very good discussions. Also thanks to:

Kevin Burton of TailRank for his idea
Jay Pipes for his intense hard work to make this event a big success
Leslie "Hacker Herder" Hawthorn, Mickey, Kynan, Chris and others for their excellent hospitality
Jeremy Cole and Eric Bergen of Proven Scaling for the plentiful, unending supply of beer
Brian Aker, Mårten Mickos, Michael "Monty" Widenius, David Axmark, Zack Urlocker, Ken Jacobs for just being there :)
Google for being an excellent host of this first camp and providing gourmet food and T-Shirts
MySQL for making the event possible
everyone for hanging out at the Tied House

I also got to hangout with Sheeri Kritzer, Baron Schwartz, Kaj Arnö, Kevin Burton, Dathan Pattishall and many other cool people.


There were so many cool sessions at the camp. Ronald Bradford has many good blog posts about the happenings of MySQL Camp at Google. I will write more about this as I get some time.

Overall, something that I would definitely love to attend next time.

Tools from Google

Chip Turner is giving a presentation on some of the tools used by Google.

[root@srv31 ~]# mkdir /google
[root@srv31 ~]# cd /google/
[root@srv31 google]# svn checkout http://google-mysql-tools.googlecode.com/svn/t                                                                             runk/ google-mysql-tools
A  google-mysql-tools/compat_logging.py
A  google-mysql-tools/compact_innodb.py
A  google-mysql-tools/compat_flags.py
A  google-mysql-tools/thread_pool.py
A  google-mysql-tools/COPYING
A  google-mysql-tools/mypgrep.py
A  google-mysql-tools/config_helper.py
A  google-mysql-tools/dbspec_lib.py
A  google-mysql-tools/command_pool.py
Checked out revision 2.

Tools that people keep re-writing.

All the code is in Python and released under Apache license.

mypgrep (mytop + grep)

If you have more than 5 dbs then it gets kinda difficult to monitor.
Find queries coming from certain IPs etc.

compactinoodb: deals with fragmentation. 20% to 30% increase in speeds becuase the data gets sequential. Must be done offline. Do it on replica and fail-over to replica. If using one large tablespace file instead of per table tablespace then also you can gain performance.

How often do you fragment? Not too often. It depends on how often you use drop table or deletes.

Another way: Change table to MyISAM and then to InnoDB. But this cannot be done on separate servers. You can kill the dump and import. Killing the alter can be bad.

OPTIMIZE/ANALYZE doesn't free up space. It does defragment but doesn't

Google running 4.0 in a lot of places and "cannot use tablespace per table" in all cases.

ERIC: Check how busy disk is compared to QPS and then decide whether to OPTIMIZE.

mod_ndb: REST Web Services API for MySQL

I am sitting in JD Duncan's session "Introducing mod_ndb, a REST Web Services API for MySQL Cluster" as it is really interesting. I kind of wanted to attend the SolidDB storage engine session and the community session happening concurrently, but the mod_ndb stuff is pretty cool.

I am going to try to get the presentation files hopefully from JD.

In case you are wondering, NDB = Network Database.

The HTTP Database uses HTTP instead of SQL. He is planning to create a good HTTP database in the future.

How to get mod_ndb:

(If you are at Google) http://192.168.22.214/code
otherwise: jdd at mysql dot com

Random Notes:

If you are a "PHP shop" its beneficial to use multithreading.

"sort merge join is better than nested loop join"

YouTube uses "Suse" and mostly InnoDB.

Investigate IO issues with SAN.

"recompile memcache to store BLOBs"

Memcache storage engine?

"Falcon is all about row cache"

At Google's Headquarters

Like everyone else, I am very excited to be at Google headquarters attending MySQL Camp. There are more than 200 attendees at the camp, which is really exciting. During the last couple of days, the number participants literally doubled. Folks from YouTube, Google, Yahoo and of course MySQL are all here.

So far I have met Jay Pipes, Jeremy Cole, Eric Bergen, Paul Tuckfield (YouTube and PayPal:paul at tuckfield), Kaj Arno and Sheeri Kritzer. Can't wait to meet others. Will be posting details soon.

Oh, and yes, we got Google T-shirts. Yay!

Also, not to forget, Ronald was introduced as Roland. Funny!

Going to MySQL Camp

So November has started and I am really excited to be going to the MySQL Camp. It will be great to finally see everyone again. In other exciting news, Ronald Bradford is coming to New York, Yay!

I will get to Mountain View on Thursday night and will also be renting a car. Jay Pipes and Ronald have already made a "call for free ride" however if you are still looking for a ride, feel free to message me. Since Jay Pipes is hanging out at the airport for sometime on Thursday, I will try to give him some company.

I will be staying at Best Western, Mountain View.

As has been pointed earlier, you do need to register for the camp (FREE) for security reasons. I sent my registration last week.

The Participants page has been updated by someone (Jay probably) to show who is registered. You can now add your name there along with the extra information needed.

PS: On the MySQL Camp home page, the link to the Participants page is broken. I tried to edit it to correct the link but got the message:

This page is locked. Only administrators and moderators may edit it.

If someone with appropriate permissions to the Wiki reads this, can we please correct the link. It currently points to http://mysqlcamp.org/%3Cb%3EParticipants%3C/b%3E instead of http://mysqlcamp.org/Participants