W00t! Finally finished the SQL optimizations I started working on a couple of days ago. Searching single terms is now twice as fast as it was; coupled with the previous change, this equates to a four-fold speed increase over the past week! EveKnows.com is now serving up the hot, naked chicks faster than ever.

While I was tweaking the code, I also improved the spider to be more stringent regarding “shifty” galleries. You know the type… auto-bookmarking scripts, pop-up ads, and other nastiness. If Caroline detects anything potentially malicious, the gallery won’t be indexed. I like clean porn galleries, and I’m sure you’ll all appreciate the fact that every gallery on EveKnows is clean and safe to visit, even if you’re not using a secure web browser…

Oh yeah, almost forgot! I noticed a number of galleries creeping into the database which where missing thumbnails. After some debugging, I found the problem in Caroline’s thumbnailing code and fixed it up. Freshly-indexed galleries should all have a thumbnail from now on.

As the EveKnows.com database grows ever larger, search times have been going up. Yesterday I tried some more SQL optimizations to alleviate this, and cut most search times in half. The problem seems to be disk access with large result sets, especially broad, single-word queries such as teen, babe, sex, etc. Finding the initial set of matching galleries isn’t much of a problem since the query uses fast indexing, but we then need to pull all of the details from, say 30,000 galleries, which is a problem. Some testing of the UNIX utilities sar and iostat reveal an incredible number of disk reads for these broad queries, and it’s only going to get worse as the database grows larger.

Until a few moments ago I wasn’t quite sure how to handle this, but I think I’ve just hit on a solution. Currently EveKnows makes two queries, one without a LIMIT command to figure out the total number of results, and then a second one with the LIMIT keyword to pull, say, the first 30 results (or 31-60 if the user is on the second page, etc.). I’m wondering if I adjust the first query to not pull the extra gallery data, if it might be able to work using only indexes. If that turns out to be true, our second, LIMITed query would only need to fetch full results for 30 rows at a time, which could be a substantial speed-up for popular search terms. Hrmm… I’ll try to implement this tonight and let you know the results! ;)

I just made an update to the search result weighting algorithm. To keep the newest galleries on top, sites which were first indexed within the past three days are now given some extra weight. I’m a little worried that the extra processing will slow down the searching algorithm too much, but we’ll try this out for a while and see how it works. If nothing else, it’ll keep new galleries at the top of the search results.

I also made some adjustments to penalize keyword spammers. After a word shows up in a gallery too many times, a negative value is applied to the gallery which will lower it in the search rankings. The more the same word appears, the lower the gallery will sink.

As always, check out the advances at the little porn search engine that could :)

So with Caroline running full-bore, I noticed a frightening thing–the EveKnows.com server was bottle-necked by disk IO. Between the spider downloading thumbnails and inserting galleries and SQL server fetching them and the Apache server handing out web pages, my machine was slowing to a crawl. Since the search database is far too large to fit into memory, each search ends up hitting the disk several times. Couple that with a constant stream of SQL INSERT statements and you get a MySQL daemon that’s constantly waiting for the hard disk to give it what it needs, and an idle CPU waiting for something to do.

After some researching, I discovered a few tricks to relieve the congestion. First, I used hdparm to enable multiple sector counts (16) and write-back caching on my disk. This alone made a huge improvement. Then, I tuned Caroline to make far fewer disk hits; it had been logging data, but I don’t really need the log so I turned that off, and tweaked the way that it opens the stop_list file to do it once per thread rather than once per gallery (which was something I should have done from the beginning, but hey, this is the first search engine I’ve ever written!) Finally, I setup two databases on the SQL server, one to use for the search engine and one for the spider to insert new galleries into. They are identical, save that the spider’s database doesn’t have any indexes, which creates far fewer disk writes when adding galleries. Then I setup a cron job to, once per day, replace the search database with the recently updated version from Caroline, add in the indexes needed to keep the searches effecient, and then go back to spidering.

Thus far, the plan seems to be working well. ‘top’ no longer shows a WA% of 90, and searches are completing in less than one second again while Caroline is running. Woohoo!

With the new threaded model for Caroline, I started to notice memory usage getting out of control. Under the previous forking system, I could expect a typical run to go through 200 MB, but the new model was easily topping 1GB and then dying when the system ran out of physical memory. After some searching, I learned that Perl’s WWW::Mechanize module caches each page in memory for the lifetime of the object (this allows for the ‘back’ feature of Mechanize); cool if you’re trying to mimic a web browser, but totally unusable for a web robot like Caroline. Thankfully there is a stack_depth() method for WWW::Mechanize which controls the number of pages cached. By setting this to 2 I managed to get Caroline’s memory usage back under control. She’s off happily indexing more galleries as I type :)

Today I took a break from tweaking the engine database and gave the interface an overhaul. I know search engines these days are mostly simple affairs, but I figured a decent design couldn’t hurt :p

It’s all valid XHTML + CSS, and should degrade cleanly in older browsers. Let me know if anyone has trouble viewing the site in their browser of choice.

Caroline is the name of the spider used to build the EveKnows.com database. Today I hacked together version 0.4, which moved to the threading model I mentioned earlier. Running multiple processes was a pain and relied on having a few different starting-points; now I can run the spider on a single URL and still experience fast results. Shortly after it began to run, I realized that my host was going to get banned all over the place because requests were being sent too quickly. Caroline/0.4.1 took a step towards fixing this by shuffling up the queue of sites a bit.

Now I’m researching how to respect robots.txt and implement longer pauses when repeatedly hitting the same server. Caroline use Perl’s WWW::Mechanize module to automate HTTP requests, which means I can’t use the LWP::RobotUA module without some serious hacking. This might be the way to go in the long run, but I want to explore other options first.

Wow. Never underestimate the power of a left join. After watching the gallery database grow to 60,000 galleries with 2.5 million indexed words, SQL query times were getting pretty bad. A search for ‘Sexy Teens’ took around 10 seconds; if it’s that bad at 60,000 galleries, imagine how bad it would be at 1,000,000! So I did some research on query optimization today and found a way to rework the main search algorithm. But first, some background…

The EveKnows database consists of a few tables, but the two that matter right now are a table of galleries and a table of keywords. The tables are linked by the gallery_id key; each gallery has an entry in the keyword table for every word on the page, plus an associated score. For example, a gallery with the word ‘goth’ in the title, incoming link, and page text might have a word score of 4 while a gallery with ‘goth’ only appearing once in the page text might have a score of 0.25. When someone queries the search engine, we look at each word in their query, add up the scores for each matching gallery, and return the results sorted so that the highest-scoring galleries are at the top. Sounds simple enough, right?

Well, it is pretty straight-forward, but my god it doesn’t scale well! Once the keyword table topped 1,000,000 entries, it began to get bad. After some research, I found that the slowdown was coming when I joined the keyword and gallery tables together–the resulting table was huge! So, I needed to reduce the size of the final table, but how? Thankfully, MySQL 5 supports subqueries, a method of executing one query inside of another and using the results in the parent query. Thus I was able to eliminate the natural join and use a subquery on the keyword table to pull each matching gallery ID and return them pre-sorted, since the scoring data was included in the subquery. This gives us a list of gallery IDs which we then left join to the full gallery table. Left joins are useful because they don’t take the product of two tables, they simply add the matching rows of the second table to the first. Thus, our result list stays sorted and has all of the gallery details, such as title, URL, summary, etc., added to it in a highly efficient manner.

Final result? That ‘Sexy teens’ search dropped from 10 seconds to less than half a second. W00t indeed!