Massive speed improvements -- details within

[insta]

First, the configs then the mods.
 * Machine is a Dell CS24-SC, with dual Xeon L5420 (2.5GHZ quads) and 24GB of DDR2 667 RAM and a single 500GB 7200RPM drive.
 * Software is Ubuntu 13.04 64-bit with MariaDB on MyISAM tables.

The MyISAM part is important, because the single biggest speed improvement came from using a feature that was only available on MyISAM with my version of MariaDB (5.5) -- and that's FULLTEXT indexes.  As was mentioned before several queries were missing some indexes and fixing those helped speed, but with a small modification to the PHP and adding the fulltext indexes we (me & KDad) have gotten some massive speed improvements.

When I was originally voluntold to help fix the performance of our indexer, the kind of tweaks were down to adjusting buffer caches and going to InnoDB -- inconsequential things that were really just masking the underlying performance problems.  Our searches return in well under a second, even for complete gibberish queries like "flobbleywop" (a good hit like "oakenfold" is about 0.75 seconds).  These results are with a backfill running as well with 3.4 million releases and 58 active groups, and at this point we are limited by IO on the HDD.

Now, the downside.  These modifications only work if you're able to use FULLTEXT indexes, which for most versions of MariaDB is only MyISAM.  The good news is that its fast enough it doesn't really matter.


What to change:

* Convert your tables to MyISAM
* Run the following SQL to add the missing indexes I identified so far

Code: [Select]

CREATE INDEX ix_releases_searchname ON releases (searchname);
CREATE INDEX ix_partrepair_attempts ON partrepair (attempts);
CREATE INDEX ix_binaryblacklist_groupname ON binaryblacklist (groupname);
CREATE INDEX ix_binaryblacklist_status ON binaryblacklist (status);
CREATE INDEX ix_releases_groupid ON releases (groupid);
CREATE INDEX ix_groups_id ON groups (id);
CREATE INDEX ix_category_parentid ON category (parentID);
CREATE INDEX ix_category_id ON category (id);
CREATE INDEX ix_movieinfo_title ON movieinfo (title);
CREATE FULLTEXT INDEX ix_releases_searchname_ft ON releases (searchname);
* Open releases.php in a text editor and follow this pattern:
Search for "and releases.searchname like".  This will get you to the right area.  The code comes in blocks of 3 and is repeated all down this file (srsly guys wtf).  You can't just copy+paste my code below because the variable names will be overwritten, you have to modify your code to look the same for every instance.

Code: [Select]

if ($intwordcount == 0 && (strpos($word, "^") === 0))
    $searchnamesql = sprintf(" and releases.searchname like %s", $db->escapeString(substr($word, 1)."%"));
elseif (substr($word, 0, 2) == '--')
    $searcnamehsql = sprintf(" and not match(releases.searchname) against (%s)", $db->escapeString(substr($word, 2)));
else
    $searchnamesql = sprintf(" and match(releases.searchname) against (%s)", $db->escapeString($word));

What I did was change the "and releases.searchname like %s" to "and match(releases.searchname) against (%s)" and then removed the "%". & ."%" parts from the escapeString call.  You have to modify the second and third line of each block, and there's about 8.  The "match/against" code is specific to full text indexes and is the reason for the speed.



I'm open to any questions or critiques   Feel free to try it on your own.  I realize the indexes I added may be redundant in some cases, and I'll eventually get around to tuning those once the DB size becomes important again.


edit: thank you to everyone who's tried this and provided feedback!

[KDad]

I was under the impression the InnoDB was far superior until insta helped me with the DB config.  The searches before would literally take 30+ seconds tmux stopped. The improvement gained with these new indexes and switching back to MyISAM is nuts!!! Night and day change. Hopefully this is something that will be able to help the community and prove further that nZEDb is superior to NN anyday.  I can switch to open registration if anyone wants to check it out.

[BeZazz]

Hi,
Thanks for that. I am bout to see how it goes.

I am pretty sure that 1 or 2 of the "id" need to be in uppercase.

Sorry I dint pay much attention was on phone when I was doing it.

EDIT: Definitely made a difference

[insta]

Hi,
Thanks for that. I am bout to see how it goes.

I am pretty sure that 1 or 2 of the "id" need to be in uppercase.

Sorry I dint pay much attention was on phone when I was doing it.

EDIT: Definitely made a difference

MySQL is case-insensitive with regards to table & column names.  This applies to indexes as well.

Glad to hear you got such a boost!  Were your numbers anything like ours?

[Bart39]

while i agree that doing the above has made massive speed increases for searching, the negative impact of going from innodb back to myisam is table locks and the impact that has on the scripts actually processing releases etc.

any suggestions ? or is it just wait for 5.6 where full text searches are available for innodb as well ?

[insta]

From what I've been seeing with our backfills, there's not really any slowdown.  We did change the update frequencies from 30 to 120 seconds (it would probably still be fine at 30 seconds but made debugging the other queries harder, no reason to change it back yet), but past that any downsides to table locks are generally mitigated by the fact that the queries themselves finish so much faster.

If you're uncomfortable using MyISAM tables you can get MySQL 5.6, it is available now.

[Bart39]

From what I've been seeing with our backfills, there's not really any slowdown.  We did change the update frequencies from 30 to 120 seconds (it would probably still be fine at 30 seconds but made debugging the other queries harder, no reason to change it back yet), but past that any downsides to table locks are generally mitigated by the fact that the queries themselves finish so much faster.

If you're uncomfortable using MyISAM tables you can get MySQL 5.6, it is available now.

problem is that i'm seeing most queries stuck waiting for table locks especially on updating releases

[wtf911]

i just wanted to say this sounds very promising and i hope to see it added. maybe you could create a pull request? i know when using phpmyadmin on the analysis page it suggested indexes to speed things up. again good work.

[BeZazz]

Glad to hear you got such a boost!  Were your numbers anything like ours?

I did not add code to test but I can say I went from waiting for ages sometimes to almost instant now.

[Saner]

you made a type I believe ( missing ; )

CREATE INDEX ix_groups_id ON groups (id);

[Saner]

patch file for releases.php



Done on phone, so it may be wrong (it seems quicker, but as I have just dumped my database, changed from percona to mariadb and reimported the database it may just be me)  so USE AT YOUR OWN RISK!

[insta]

For the most part that's right, although the lines like:

Code: [Select]

sprintf(" and match(releases.searchname) against (%s)", $db->escapeString(substr($word, 1)."%"));
shouldn't be using match/against, as MySQL doesn't understand the '%' at the end.  Since the constant term is at the beginning of the column this is a query that's ok using the LIKE operator and a regular index and it will be very fast.  The original index list above includes a plain-jane index on the column for that reason.

That said, thank you for providing a patch

[extide]

Nice dude, I have always been a fan of MyISAM myself. Besides the whole row locking vs table locking thing, MyISAM is actually really really fast and lightweight. InnoDB always feels like you are lugging along a whole bunch of extra bloat/crap for some reason. I will make these changes to my setup at some point, but my DB is a bit modified so I will have to go thru this all manually.

Is anyone planning on merging this into the main source? I can possibly work on that if needed.

[kevin123]

I added the indexes, everything else no since it requires myisam and not everyone is going to use myisam.

[insta]

Well, technically, it requires FULLTEXT indexes.  You could merge them in and say it requires MyISAM if 5.5 and lower, MyISAM/InnoDB at 5.6 and higher.  Providing minimum version numbers for dependencies is nothing unique to software.

Just the regular indexes by themselves aren't going to do much but they do help some.