Ramblings of a web guy

Well, it has been almost a month.  I know I am late to the blogosphere on my thoughts.  Just been busy.

Again this year, the Phorum team was invited to be a part of the DotOrg Pavilion.  What is that?  Basically they just give expo floor space to open source projects.  It is cool.  We had a great location this year.  We were right next to the area where they served food and drinks during the breaks.  We had lots of traffic and met some of our power users.  IMVU.com is getting 1.5 million messages per month in their Phorum install.  They did have to customize it to fit into their sharding.  But, that is expected.  A guy (didn’t catch his name) from Innobase came by and told us that they just launced InnoDB support forums on their site using Phorum.  Cool.  So now MySQL and Innobase use Phorum.  I am humbled by the message that sends to me about Phorum.

Speaking of our booth, we were right next to the phpMyAdmin guys.  Wow, that product has come a long way.  I was checking out the visual database designer they have now.  It was neat.  I also met the Gentoo MySQL package maintainer.  He was in the phpMyAdmin booth.

I was interviewed by WebDevRadio as I already posted.  I was also asked to do a short Q&A with the Sun Headlines video team.  They used one part of my clip.  I won’t link to that.  No, if you find it good for you.  I need to be interviewed some more or something.  I did not look comfortable at all.

There were lots of companies with open in their name or slogan.  I guess this is expected pandering.

I attended part of the InnoDB talk given by Mark Callaghan of Google.  It appears that Google is serious about improving InnoDB on large machines.  That is, IMO, good news for anyone that likes InnoDB.  If I counted right, they had more than 5 people who at least part of their job is to improve InnoDB.

I gave my two talks.  The first had low attendance, but the feedback was nice.  It was just after the snack break in the expo hall and I was in the farthest room from the expo hall.  That is what I keep telling myself. =)  The second was better attended and the feedback seemed good there.  I was told by Maurice (Phorum Developer) that I talked too fast and at times sounded like Mr. Mackey from South Park by repeating the word bad a lot.  I will have to work on that in the future.  I want to do more speaking.

On the topic of my second talk, there seemed to be a lot of “This is how we scaled our site” talks.  I for one found them all interesting.  Everyone solves the problem differently.

Next year I am thinking about getting more specific with my talk submissions.  Some ideas include: PHP, MySQL and Large Data Sets, When is it ok to denormalize your data?, Using memcached (not so much about how it works), Index Creation (tools, tips, etc.).

In closing, I want to give a big thanks to Jay Pipes and Lenz Grimmer from MySQL.  Despite Jay’s luggage being lost he was still a big help with some registration issues among other things.  Both of them helped out the Phorum team a great deal this year.  Thanks guys.

A coworker found out how secure Amazon’s MP3 store is.  Even big guys like Amazon make errors in their web site security.

So, I clicked purchase and the album immediately started downloading. It was at this point that I had the thought cross my mind: “Did I update my credit card info?”

Well, no, I didn’t. Before the album finished downloading, I was trying to change the method of payment. Turns out, for a digital purchase, you can’t do such a thing. So, I waited and wondered was was going to come of this…

When I first started installing MySQL for myself, it was quite handy to have the example my.cnf files in the source package.  I was a noob to the MySQL configuration.  Even after I became more experienced, I would use them as a starting point.  However, I now find that they are so behind the times they are not as useful.  Here are some of the comments from the files.

my-small.cnf

# This is for a system with little memory (<= 64M) where MySQL is only used
# from time to time and it’s important that the mysqld daemon
# doesn’t use much resources.

my-medium.cnf

# This is for a system with little memory (32M - 64M) where MySQL plays
# an important part, or systems up to 128M where MySQL is used together with
# other programs (such as a web server)

my-large.cnf

# This is for a large system with memory = 512M where the system runs mainly
# MySQL.

my-huge.cnf

# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.

I end up using the large or huge files as a starting point for every server I set up by hand.  The small and medium should be renamed underpowered and teeny-tiny.  Who has less than 64MB of RAM on a server now?  Can you even buy sticks of memory that small in any modern system?  Most come with 256MB sticks minimum.  And they never come with just one stick.

I will use the large example as a starting point for a server that has 2GB of RAM and will be running an entire site on one server.  I use huge for any server that runs only MySQL.  And even then, most of them have 4GB of RAM or more.

I don’t know if anyone at MySQL has plans on tweaking these files or not.  Perhaps those good guys at the MySQL Performance Blog or Percona could create some example my.cnf files.  I could put some out there, but I fear their sole purpose would be for someone to point out what I am doing wrong. =P  Hey, they work for me.  Hmm, maybe this would make a good MySQL Forge section.  A whole area of user contributed my.cnf files.  They could be architecture specific and everything.  What runs best on Solaris?  Linux?  BSD?  Windows?  32-bit?  64-bit?

One thing I would for sure like to see is example files for InnoDB dominant servers.  Most of our servers all run primariy InnoDB tables.  None of these above examples covers InnoDB.  They have comments, but no preconfigured values.  I have seen more than one server using InnoDB tables without any custom configuration in their my.cnf.  In the end that is the fault of the server admin/owner no doubt.

What do you say?  Anyone up for a MySQL Forge section for my.cnf files?

While I was at the MySQL Conference, I sat down with Michael Kimsal of WebDevRadio and recapped the two talks that I gave at the conference.  I have uploaded the slides so you can follow along if you want.

One to a Cluster - The evolution of the dealnews.com architecture.

MySQL Tips and Tricks - Some simple tips and some of the more advanced SQL we use in Phorum.

Thanks Michael.  Any time you need a guest, just let me know.

I was reading a post about The Swag Report and realized that I stayed so busy at the Phorum booth (and a little at the memcached booth) and preparing for my talks, I did not bother to go around and collect any swag from the conference.  So, if you are a vendor and want to mail me some swag that I missed, you can send it to: Brian Moon, 198 S. Hillcrest Rd., Odenville, AL  35120.  Of course, I expect nothing.  But, ya never know what product I might pimp because of a t-shirt. =)

So, I mentioned before that I found out about index_merge at the MySQL Conference. I was wondering why I had not heard more about it since it came out in 5.0.3. When talking with some MySQL people about it, I received mixed results. So, I decided to kind of run my own tests on some data and see what I could figure out.

I apologize for WordPress’ bad output. =(

The Data

I created a table with 5 million rows. Early tests with MySQL’s Harrison Fisk (HarrisonF) over my shoulder with small data sets showed MySQL would optimize out the indexes in favor of table scans. I wanted to avoid that. This is my table schema:


CREATE TABLE `test2` (
`id1` int(10) unsigned NOT NULL default ‘0′,
`id2` int(10) unsigned NOT NULL default ‘0′,
`id3` int(10) unsigned NOT NULL default ‘0′,
`dt` datetime NOT NULL default ‘0000-00-00 00:00:00′,
`somevar` varchar(255) NOT NULL default ”,
KEY `id1` (`id1`),
KEY `id2` (`id2`)
) ENGINE=MyISAM


The field id1 was filled with random vaules between 1 and 5000. I filled id2 with random values between 1 and 100, except that about half the data has the value 999 in it. This was to emulate the issue we were seeing on the smaller table. We found that if a value was in more than n% of the rows, the optimizer would skip the index. I wanted to test that on larger data sets. id3 was filled with random values between 1 and 1000000. dt was a random date/time between 1999 and 2008. and somevar was a random string chars.

Intersect Merges


mysql> explain select count(*) from test2 where id2=99 and id1=4795;
+—-+————-+——-+————-+—————+———+———+——+——+—————————————————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+—————+———+———+——+——+—————————————————-+
| 1 | SIMPLE | test2 | index_merge | id1,id2 | id1,id2 | 4,4 | NULL | 3 | Using intersect(id1,id2); Using where; Using index |
+—-+————-+——-+————-+—————+———+———+——+——+—————————————————-+


This is the most basic of example. MySQL uses the two indexes, finds where they intersect and merges the data together. This query is quite fast, although a key on the two together would be faster. If you have this showing up a lot, you probably need to combine the two keys into one. I should also note that in this example, only the keys are needed, no data from the tables. This is important.


mysql> explain select sql_no_cache somevar from test2 where id2=99 and id1=4795;
+—-+————-+——-+——+—————+——+———+——-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——-+——+————-+
| 1 | SIMPLE | test2 | ref | id1,id2 | id1 | 4 | const | 930 | Using where |
+—-+————-+——-+——+—————+——+———+——-+——+————-+


As you see, as soon as we ask for data that is not in the indexes, our intersect is dropped in favor of using the key with the least values and simply scanning on those to match the rest of the where clause. This was the case pretty much every time I tried it. I was never able to use an index_merge with intersect when requesting data not available in the key.

Union Merges


explain select sql_no_cache somevar from test2 where id2=99 or id1=4795;
+—-+————-+——-+————-+—————+———+———+——+——-+———————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+—————+———+———+——+——-+———————————–+
| 1 | SIMPLE | test2 | index_merge | id1,id2 | id2,id1 | 4,4 | NULL | 27219 | Using union(id2,id1); Using where |
+—-+————-+——-+————-+—————+———+———+——+——-+———————————–+

mysql> select sql_no_cache somevar from test2 where id2=99 or id1=4795;
26237 rows in set (0.20 sec)

This merge type takes to keys involved in an OR and then merges the data much like a UNION statement would. As you can see, in this case, it did use the index even though we requested `somevar` that is not in the index.

To show the alternative to this, I selected using id3 instead of id1. id3 has no index.


mysql> explain select sql_no_cache somevar from test2 where id2=99 or id3=266591;
+—-+————-+——-+——+—————+——+———+——+———+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+———+————-+
| 1 | SIMPLE | test2 | ALL | id2 | NULL | NULL | NULL | 5000000 | Using where |
+—-+————-+——-+——+—————+——+———+——+———+————-+

mysql> select sql_no_cache somevar from test2 where id2=99 or id3=266591;
25252 rows in set (26.01 sec)

As you can see, this does a table scan even though there is a key on id2. It does you know good.

Sort Union Merge


mysql> explain select sql_no_cache id1, id2 from test2 where id2=99 or id1 between 4999 and 5000;
+—-+————-+——-+————-+—————+———+———+——+——-+—————————————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+————-+—————+———+———+——+——-+—————————————-+
| 1 | SIMPLE | test2 | index_merge | id1,id2 | id2,id1 | 4,4 | NULL | 44571 | Using sort_union(id2,id1); Using where |
+—-+————-+——-+————-+—————+———+———+——+——-+—————————————-+

mysql> select sql_no_cache somevar from test2 where id2=99 or id1 between 4999 and 5000;
27295 rows in set (0.19 sec)

This behaves much like the union merge. However, because one index is using a range, MySQL must first sort one index and then merge the two. Again, if I switch this to an AND instead of an OR, index_merge is not used in favor of scanning the id2 indexed data for matches to the rest of the where clause.

Conclusion

Hmm, after all this, I see why this was not a big announcement. It can only make bad SQL and tables better. Tables and queries that are already optimized using composite indexes will see no benefit from this. At best this will help me with some one off queries or reports that are only run monthly where I don’t want to pollute the indexes with special cases just for those queries.

It is always surprising what I learn when I go to a conference these days. Years ago, I could go to any talk and just suck it all in. Now, it is the little nuggets. The topics as a whole do more to confirm what I have already developed while running the Phorum project and building the infastructure for dealnews.com. That confirmation is still nice. You know you are not the only one that thought a particular solution was a good idea.

One of the confirmations I have had is that the big sites like Flickr, Wikipedia, Facebook and others don’t use exotic setups when it comes to their hardware and OS. During a keynote panel, they all commented that they did not do any virtualization on their servers. Most did not use SANs. Some ran older MySQL versions but some were running quite recent versions. I have kept thinking that I did not have the desire to get to fancy with that stuff and clearly I am not the only one.

One of the little nuggets that will likely change my world is index_merge in MySQL. I feel silly as this has been around since 5.0.3 but I was not aware of it. Basically MySQL will now use more than one key to resolve a where clause and possibly an order by depending on the query. This could lead to me removing several keys from tables in both Phorum and at dealnews.

There were others, but I am tired and trying to get OpenID into the Phorum trunk right now so I will have to think of more later.

So, I am at the MySQL Conference this week with my Phorum co-developers. We got to talking last night about how old Phorum is. We knew it was about 10 years. We pulled up some old archived zip file of version 1.5 and found in the this in the comment block.


* Created 04/16/1998


Whoa! That means that yesterday was the 10th birthday of the Phorum project. I would guess that is the date I originally put the code up on my personal web site for people to download. I remember sending that email to the PHP General mailing list. I told people they could have the code if they would help debug it. Later I officially made a GPL license and then a BSD style license as I became more knowledgeable about the open source and free software world.

So, for kicks we decided to install version 1.6 on the phorum.org site. Keep in mind the release date for that was March 30, 1999. The only hurdles were a default value on an auto increment column in the .sql file, needing register_globals and adding .php3 to be parsed as PHP. That got it up and running. I had hoped to post the URL for fun, but sadly, 5 lines in were sql injection vulnerabilties. Ah, the good ol’ days.

Sadly, I don’t have my emails from 1998. I lost everything in 2001 due to either a hard drive crash or some shady deal I had with someone hosting the Phorum site at the time. I can’t remember. If anyone happens to have UseNet archives or mailing list archives of the PHP General list from April 1998, please let me know. I would love to have that old stuff.

I always enjoy #oscon at the O’Reilly Open Source Convention every year. I propose we use #mysqlconf on freenode during the 2008 MySQL Conference. I am there. Come join me.

So, as I said before, I will be at the MySQL Conference next week.  I am renting a car this year so I don’t have to wait on cabs or deal with them at all.  So, I am mobile and being from a modern Southern US city, used to driving 30 minutes just to go to dinner.  So, where should we go?  Anything good in San Jose?  Should I go all the way to San Francisco?  I am willing to go where ever.  Help me locals, you are my only hope!