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 is http://pastie.org/866294 for 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 at > http://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.
