Ramblings of a web guy

I have always had an issue with PHP Sessions. Albeit, a lot of my issues are now invalid. When they were first implemented, they had lots of issues. Then the $_SESSION variable came to exist and it was better. Then memcached came to exist and you could store sessions there. That was better. But, still, after all this time, there is one issue that still bugs me.

When you start a session, if the user had no cookie, they get a new session id and they get a cookie. You can configure that cookie to last for n seconds via php.ini or session_cookie_set_params(). But, and this is a HUGE but for me, that cookie will expire in n seconds no matter what. Let me explain further. For my needs, the cookie should expire in n seconds from last activity. So, each page load where sessions are used should reset the cookie’s expiration. This way, if a user leaves the site, they have n seconds to come back and still be logged in.

Consider an application that sets the cookie expiration to 5 minutes. The person clicks around on the site, gets a phone call that lasts 8 minutes and then gets back to using the site. Their session has expired!!!! How annoying is that? The only sites I know that do that are banks. They have good reason. I understand that.

My preference would be to either set an ini value that tells PHP sessions to keep the session active as long as the user is using the site. Or give me access to the internal function php_session_send_cookie(). That is the C function that sends the cookie to the user’s browser. Hmm, perhaps a patch is in my future.

In the short term, this is what I do:

setcookie(
ini_get(”session.name”),
session_id(),
time()+ini_get(”session.cookie_lifetime”),
ini_get(”session.cookie_path”),
ini_get(”session.cookie_domain”),
ini_get(”session.cookie_secure”),
ini_get(”session.cookie_httponly”)
);


That will set the session cookie with a fresh ttl.

Ok, going to dig into some C code now and see if I can make a patch for this.

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.

Wow! For the second time this year we had snow for the kids to play in. It was all gone by noon, but we got out early and took some pictures. Click the picture below to see more.

I first saw it on dealnews, and read more on Google News.  So, selling lame stuff for really high prices doesn’t pay the bill?  Huh.  Who knew?

I have decided to try out Twitter.  Mostly it is a curiousity about what it does and how it works.  The recent outages baffle me.  It seems like a very simple application.  I am hoping to find the thing that makes it a tough thing to scale out and/or up.  (Other than it being built on Rails that is.)

http://twitter.com/brianlmoon

My coworker Rob is building a little project at home.  After some bad experience with cheap black box servers, he got some used Dell’s.  Now, he has done this.  Heh, is pretty cool.

You know those NyQuil commercials where the guy tells his wife “You will sleep like you did before you had kids.”  Well, that is so true.  Before I had kids I laid my head down, slept and then rose in the morning fresh.  I only needed about 5 hours sleep.  But, after I had kids and started worrying about them all night long, I could not do that.  I can still do it when I am away from home which is odd.

Now, after all this time with kids (I have 5 in case you don’t know) they hardly ever wake in the middle of the night.  But, my mind is conditioned to wake on the slightest noise.  We moved out into a quieter (aka more rural) part of the state 3 years ago.  Hardly any traffic on the road late at night.  I find myself waking at the slightest noise: the icemaker, a dog barking, our dog getting up and stretching, or that very rare car.

So, after a restless night, I got the kids up and ready for school at 6AM like usual.  But, I was determined to get some more sleep.  So, I stuck in my ear buds and turned on my iPhone’s iPod.  While it was not solid uninterrupted  sleep, it was better than what I had all night.  I need to find some soft music or soothing sounds that will run for several hours.  That may provide the best sleep.  That and some more comfortable earbuds for sleeping.  It is either that or take down our nice quiet Hunter ceiling fan and get the cheapest, loudest one I can find.  That always worked in our older houses.

Earlier this week Terry Chay wrote about his little notebook. I have to wonder if that inspired this xkcd comic or not. Is that your real reason for writing in that notebook Terry?

A coworker of mine has rediscovered the usefulness of a notebook. Yesterday while at our datacenter, he kept notes in his notebook while I typed on my iPhone.

I have self diagnosed myself with dysgraphia. It really does hurt me to write. So, I only do it when I have no other choice. I will type you a book. But, I don’t want to write the shortest note.

It is that time of year when folks promise themselves to get into better shape.  This is usually my time every year to do that.  The holidays always put some extra weight on me.

I know from going to conferences that there are some health conscious geeks out there.  Well, I found a geeky way to help with that.

Some guys here in Huntsville have started a fitness and health tracking site called Gyminee.  They are in the same business incubator that dealnews was in several years ago.  It is all Rails and MySQL.  It is quite convenient for me and some other guys at work since they built an iPhone interface.  Basic accounts are free and fairly full featured.  There are some advanced features that do require a small membership fee.  If I would just quit buying one latte a month it will pay for it.

For the record, I have no stake in this place.  I am just a user and I happen to have met the guys to give them some pointers on some things on their site.

You can read the full announcement in the news post at phorum.org.

Summary:

When we officially started on 5.2 in March of 2006, we had several goals. I think we achieved most of those. We also accomplished some unplanned things. Our hackathon (me and maurice slept about 8 hours in 4 days I think) at MySQL Conference 2007 helped a lot. Remember, you can help us get there again by donating to our fund.

Some highlitghts:

New Template
New API layers and more hooks
Better MySQL support
Improved bundled modules
New announcement system
New Search, inside and out

In addition to all this, there are things like more caching options, the new hybrid read view, the new feed options (the feed code was rewritten from scratch) and better error messages so that users are less confused.

We hope you all enjoy Phorum 5.2. It is the next step for this project that is over 10 years old now. And please, keep making Phorum all your own.