Some thoughts on possible ways to speed up set generation in the future

[thorwak]

Some early results (the 1.0.5-update finally finished so I ran a couple of quick tests on this before moving on) rev. 1436 :

Exact same data in the tables as yesterday, nothing added or removed:

- Generating headers on a 5M group (10 minutes in old version), clean state:
10 seconds.

- Generating headers on a 10M group (40 minutes in old version), clean state:
28 seconds.

Running them again after this takes less than a second (they are now in DB cache)

This is very good news since it means it will take no time (that matters) to figure out what stuff is dirty and hence include in gensets. Great

Marking 5M headers (all in a group dirty, not converted to inyint yet) : 40.4 secs

Marking 10M headers (all in a group dirty, not converted to inyint yet) : 79.6 secs (ah, linear, me like )

Generating sets (truncated binaries_xxx table) ...

5M headers, all dirty (used to be 10min 30 secs) :
4 minutes 30 secs! Outstanding!

10M headers, all dirty (used to be over 40 mins) :
11 minutes 52 seconds! Whoa!

Not quite linear, but close. Taking into account that the 5M table was small enough to possible be in the cache, at least partly, it's quite possible you have actually solved the entire problem of things getting out of hand as size grows.

[spearhead]

Glad to hear it seems to work out.

[thorwak]

This is a HUGE improvement, 1.0.5 is gonna be the best version ever 

Even on small groups the improvement was a 100% - 200% speedup. I'm seeing 14.000+ headers / sec in a 10M group, that's better than even on tiny groups, as per my first tests

I won't do the 54M group now, but I'll run it over night. If it scales all the way it should be done in just over 1 hours. Realistically, under 2 hours would be great


Well done identifying the problem, Sir. I take my hat off.

(How's that URD vacation coming )


Edit: As a nice bonus, system load is lower now when running gensets. I can't say exactly how much, but quite a bit. I guess this is because temporary tables are not created, mostly. I bet one could run 2 gensets at the same time now, if one has the CPU for it

[thorwak]

Continuing praise as I test..

20M+ group, oldest set over 1 year (probably 620+ days, hard to tell exactly without diving into sql - set list just shows "1 Y / 1 år)") Anyway, the kind of archive I'm after

Clean state, but no new headers gotten for a week (I have had to avoid doing this since it took over 2.5 hours to generate the sets even if next to nothing new was gotten).

233.000 new headers gotten (low volume group, but still useful with long retention). Time to generate sets (only the new, "dirty" ones) :
1 min 32 secs.

Time saved with the combined improvements: about 98%. 


In this case, I guess most of the time actually went towards figuring out what headers were dirty. You have a point a key on "dirty" may improve this, I'll see if I have time to test later. Anyway, at least for groups of this size it doesn't really matter.. Possibly for 100M and above it becomes important; I don't think it could be slower at least?

[thorwak]

Edit: Yep. Running update again (no new articles found). "Generating sets" took 1 min 15 secs which makes sense (all clean).

[spearhead]

That's good to hear. I'm doing some tests myself, also adding a progress indicator...

[thorwak]

cool, progress bar for this would indeed be very nice!

I did one more test: I marked the entire 20.2M group dirty, truncated binaries_xxxx table for it and generated sets. This is what took 2 hours 26 mins in my first post in this thread. Time now: 29 mins 50 secs, a bit over 11200 headers / second. There seems to be a bit slowdown as tables grow, but it's A LOT better than it used to be. Besides, it's only done once..

Will try a huge one where there is a good moment for it.


Something that came to mind: How you thought about what happens is the user aborts the task? What happens to the clean/dirty states and so on? (I haven't looked at the code so not suggesting it's broken, just a thought that entered my mind..)

[thorwak]

Also, check out the cache hit ratios since switching to the new code base
I've been tuning constantly for a week and gotten it a lot better than it was, but nothing like this. Getting rid of those huge tmp tables has really helped. This is also under heavier than usual updating so it will probably improve even more over time.

Code:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.45
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 72G (Tables: 192)
[!!] Total fragmented tables: 29

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 51m 7s (4M q [731.670 qps], 8K conn, TX: 243M, RX: 1B)
[--] Reads / Writes: 5% / 95%
[--] Total buffers: 506.0M global + 8.3M per thread (25 max threads)
[OK] Maximum possible memory usage: 713.6M (18% of installed RAM)
[OK] Slow queries: 0% (42/4M)
[OK] Highest usage of available connections: 36% (9/25)
[OK] Key buffer size / total MyISAM indexes: 384.0M/7.0G
[OK] Key buffer hit rate: 99.7% (227M cached / 739K reads)
[OK] Query cache efficiency: 60.0% (353K cached / 588K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 384 sorts)
[OK] Temporary tables created on disk: 22% (277 on disk / 1K total)
[OK] Thread cache hit rate: 99% (10 created / 8K connections)
[OK] Table cache hit rate: 73% (221 open / 301 opened)
[OK] Open file limit used: 20% (430/2K)
[OK] Table locks acquired immediately: 100% (233K immediate / 233K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries

Green across the board - not bad

[spearhead]

Something that came to mind: How you thought about what happens is the user aborts the task? What happens to the clean/dirty states and so on? (I haven't looked at the code so not suggesting it's broken, just a thought that entered my mind..)

Shouldn't cause any trouble. Worst case is a couple of sets/binaries get recalculated.

[thorwak]

Ok first piece of bad news.. It seems the new gensets code is matching a lot worse, causing a lot of sets to be broken, that works with the old code. This becomes very clear when expiring incomplete sets. One group expired down to 2.5M articles with the old code, 800K articles with the new code..

Can I influence this with the GENSETS_STEPSIZE? How does it work?

[spearhead]

I noticed this too. Plus on postgres performance is abominable...

[thorwak]

Being slower on pgsql is perhaps even weirder.. Why do you think that is?

[spearhead]

I don't know... maybe something with indexing....

a group of 2.5M arts takes half an hour to gensets for. and psql is at 100%... expire is slower even and load stays at 100% after expire is complete or terminated....

Now trying on mysql...

[spearhead]

Ok first piece of bad news.. It seems the new gensets code is matching a lot worse, causing a lot of sets to be broken, that works with the old code. This becomes very clear when expiring incomplete sets. One group expired down to 2.5M articles with the old code, 800K articles with the new code..

I think I found the culprit. Dunno if this causes the speed issue too.

[thorwak]

I thought expire was slower too - 'til I understand it threw out 90% of the articles because it figured the sets were broken, after I regenerated then

Not knowing how stepsize works, I figured I'd just put it at 40k rather than 4k and see if it makes a difference. I'll have to redownload 5M headers first though to have something to play with

Other than that, it was fantastic . couple of millions headers? pff, seconds

[thorwak]

I think I found the culprit. Dunno if this causes the speed issue too.

Cool, I'll be able to do some testing (will copy the tables before expiring next time..)

[spearhead]

I has not to do with the stepsize really (that is just for memory ... the higher , the more memory needed.)

The culprit is I do a select with offset on the table with dirty > 0 and after that update the table to set dirty = 0 on the sets I just processed. But that would mangle the earlier offset. Silly me

[thorwak]

Well stuff like that is expected while developing I'll wait for a commit and then test.

Edit: I saw the commit, will know in 10 minutes or so if it helps here

[thorwak]

Slightly related: update_disable_keys should be turned off by default now if you ask me.. Using it takes several minutes on huge groups, by far being the most time consuming part of an update (when it re-enables the keys)

[thorwak]

In your latest commit the stepsize was changed to 400 - this made the performance terrible here (dropped to about 10% of before). I changed it back to 4000 and now it seems better. Still way slower than it was before the fix, about 30-35% of the speed. Then again, this may make sense if it missed plenty of headers before 

Will report shortly on the status of the sets..

Edit: Forgot! Way cool with a working status bar 

[thorwak]

Ok! Final speed was about 50% of what it was at before the fixed version. So, actually, the speed is back to where it was before anything was done, which makes sense (for a small group).

Set completion seems to be about what it was before (a few % difference but that could have to do with me having different settings elsewhere).

I'll have to test bigger groups again to see if we still see the more linear time increase since small groups is now "back to normal".

[thorwak]

I got it up 50% by adjusting stepsize to 16000, so running at 75% speed now of all time high.

However, something has happened here:
[!!] None of your MyISAM tables are indexed - add indexes immediately

I think it may be a bit better after I fix this This is new however, after last stop/start of DB (I copied a DB around, I guess it didn't like that)

EDIT: Never mind, this was actually a bug in the mysqltuner script...

[thorwak]

I'm getting contradicting results here.. The completely fresh installation I did is performing well. However, the upgraded older DB is going very slowly /was fine before the bug fix), perhaps on par with what you saw for pgsql. It's another group, but similar amount of headers.. It doesn't make any sense. (Yes, I remembered to adjust stepsize).

I think I'll call it a day, after a while nothing makes sense anymore and one needs to do something else.

Good work today!

[spearhead]

It seems on my test that the revision now in svn is working correctly. Not so sure about the speed tho. I've been trying it out on mysql for now. Dunno yet about psql. But I haven't played yet with the stepsize. I lowered it to see if sets/binaries are generated correctly.
The keys may be an important factor here really.

I'll test it out some more. Later...

[thorwak]

Turns out I'll be home a little longer than I thought, so I'll do some more testing (bored out of my skull here).

Yep, something has happened. 14M headers took 3 hours to process during the night, should have taken more like 5-20 minutes.

One thing I noticed was that the VM I use for urdd + urd ui was completely eating up all my CPU reasources for no good reason. It had two running headers downloads, no ssl or anything (the proxy that handles compression and ssl is in another VM - that one was fine).

Now, it's difficult to remember exactly how things usually are and one tends to start chasing ghosts when there is a problem, but I'm pretty sure this (urdd) VM has no system load whatsoever usually, even when there are lots of tasks running in urd. Either something is temporary strange with it (I'm rebooting everything I can easily reboot now without disrupting other stuff too much to see if that helps) OR something has change since 1.0.4 that has to do with how header downloading is done.

Does any of this ring bill? Might be a reason for stuff suddenly start going so slow when it was extraordinary just a few revisions ago, if you have touched anything there (header download all of a sudden demanding a lot more CPU, and not in the DB but the PHP process itself)


You may also have a point about table keys though. Load is higher again than it was a few revisions ago during gensets. Also, I'm seeing lots of tmp tables again according to mysqltuner.

It could be a combo of the stuff described above. Perhaps there is a need for a new key that has to do with the way you fixed the broken set generation?


I'll do some testing now on data I know exactly how long it should take to be reasonable, and then do the same again with some header DLs going. All on latest svn of course.

I'll post away when I have any results.


edit: typos ( ) and some clarifications.