after having made the changes of the "type" column for the polymorphic relations from varchar(255) to varchar(14) it didn't appear initially to make a difference. but the next day it was running the delta indexes ALOT faster -
indexing index 'user_delta'... total 23 docs, 917 bytes total 0.031 sec, 29446.71 bytes/sec, 738.58 docs/sec I can't quite believe making that change really sped it up 500x - there might have been some other factors. the full core indexes still take a bit too long too run but it's manageable. I think the remaining slowness but be down to particularly complex indexes - when I've got a moment I'm going to remove them all and add them back in one by one to pinpoint the slow ones. thanks for your help On Mar 13, 12:53 am, Pat Allan <[email protected]> wrote: > Hi Nick > > I'm at a loss for the cause of this... but here's a couple of examples of > speeds on my 3-year-old MacBook Pro, from a client app: > > indexing index 'bookmark_core'... > collected 1178778 docs, 17.7 MB > collected 1178683 attr values > sorted 2.4 Mvalues, 100.0% done > sorted 2.7 Mhits, 100.0% done > total 1178778 docs, 17737277 bytes > total 43.546 sec, 407317 bytes/sec, 27069.34 docs/sec > > indexing index 'item_summary_core'... > collected 34487 docs, 13.8 MB > collected 34487 attr values > sorted 0.1 Mvalues, 100.0% done > sorted 2.0 Mhits, 100.0% done > total 34487 docs, 13818929 bytes > total 74.222 sec, 186183 bytes/sec, 464.64 docs/sec > > Obviously, the second is more complex, with some pretty crazy SQL snippets > for a couple of attributes. That said, it's not using polymorphic > associations, so maybe that's the cause. > > Sorry I don't have any pointers on what should be done next - it sounds like > you've investigated the generated query pretty well already. > > -- > Pat > > On 12/03/2010, at 9:00 PM, Nick Sellen wrote: > > > > > that was meant to read "I haven't noticed it other than when indexing > > though." > > > and out of interest how fast do yours or other peoples indexing take? > > the following just seems a bit slow overall. > > > collected 3734 docs, 24.3 MB > > collected 0 attr values > > sorted 0.0 Mvalues, 100.0% done > > sorted 3.7 Mhits, 100.0% done > > total 3734 docs, 24349107 bytes > > total 200.271 sec, 121580.64 bytes/sec, 18.64 docs/sec > > > my indexes definition ishttp://pastie.org/866294for that index > > > On Mar 12, 8:59 am, Nick Sellen <[email protected]> wrote: > >> and no, it didn't make much speed difference on the server. > > >> my feeling at the moment is that the database design is causing > >> serious cpu crunching. I have noticed it other than when indexing > >> though. > > >> On Mar 12, 8:39 am, Nick Sellen <[email protected]> wrote: > > >>> it's generated normally - the ids range from 1 to 13966 with ~4000 > >>> items - although for the user index which the stats above are from > >>> it's just 1 to 1538 with 1538 items. > > >>> the other two factors which I'm looking at are: > >>> 1. whilst the db box is fast and powerful, dual quad core processors / > >>> 15k SAS disks. the query is bottlenecking on being constrained to one > >>> CPU core - the individual cores aren't THAT powerful and so could > >>> quite conceivably be outdone by my laptop > >>> 2. database design, theres quite a lot of rails polymorphic columns, > >>> which had the default 255 length and utf-8 collation = quite a lot of > >>> bytes. I've changed this to varchar(14) with latin1_general_ci > >>> collation. I didn't have a massive boost on my laptop - a few seconds > >>> quicker, just running it on the server now... > > >>> cheers, > > >>> On Mar 12, 4:54 am, Pat Allan <[email protected]> wrote: > > >>>> Hi Nick > > >>>> How was your production data generated - normally, or by fixtures? (Or > >>>> even: what's the min and max ids in your user table?) If there's a > >>>> really massive window, then Sphinx is making a lot of queries to get > >>>> through all the data, but you can change > >>>> that:http://freelancing-god.github.com/ts/en/common_issues.html#slow_indexing > > >>>> If that doesn't help matters, let us know. > > >>>> Cheers > > >>>> -- > >>>> Pat > > >>>> On 12/03/2010, at 9:09 AM, Nick Sellen wrote: > > >>>>> I've got a case of very slow indexing - 111 seconds to index 9 > >>>>> documents. It's not really thinking sphinx that is responsible for > >>>>> this part anyway - but just looking for any tips or ideas. > > >>>>> - on my laptop it runs fine, e.g.: > > >>>>> indexing index 'user_delta'... > >>>>> total 2 docs, 68 bytes > >>>>> total 0.048 sec, 1425.43 bytes/sec, 41.92 docs/sec > > >>>>> - on the production server one time it ran like this: > > >>>>> indexing index 'user_delta'.. > >>>>> total 9 docs, 320 bytes > >>>>> total 111.667 sec, 2.87 bytes/sec, 0.08 docs/sec > > >>>>> - and another like this: > > >>>>> indexing index 'user_delta'... > >>>>> total 14 docs, 533 bytes > >>>>> total 8.589 sec, 62.06 bytes/sec, 1.63 docs/sec > > >>>>> the indexer is running on seperate box to the db - the db server is a > >>>>> very fast database server (SAS disks etc) so the hardware should be > >>>>> fine, but the load on it is surprisingly high when running the > >>>>> indexing. > > >>>>> I ran a MySQL EXPLAIN command on the generated SQL configuration and > >>>>> it shows all indexes being used. > > >>>>> so, yeah, any experiences people might have that could help would be > >>>>> much appreciated. > > >>>>> cheers! > > >>>>> -- > >>>>> You received this message because you are subscribed to the Google > >>>>> Groups "Thinking Sphinx" group. > >>>>> To post to this group, send email to [email protected]. > >>>>> To unsubscribe from this group, send email to > >>>>> [email protected]. > >>>>> For more options, visit this group > >>>>> athttp://groups.google.com/group/thinking-sphinx?hl=en. > > > -- > > You received this message because you are subscribed to the Google Groups > > "Thinking Sphinx" group. > > To post to this group, send email to [email protected]. > > To unsubscribe from this group, send email to > > [email protected]. > > For more options, visit this group > > athttp://groups.google.com/group/thinking-sphinx?hl=en. -- You received this message because you are subscribed to the Google Groups "Thinking Sphinx" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/thinking-sphinx?hl=en.
