We were forced to cut page viewing from for example 3000 pages to first 25 pages because of degradatin performance with every next page.
I will look into this sphinx pagination thing but sphinx got issues itself (returning false matches sometimes etc.) so im afraid it will not be 100% exact in pagination as it should be.
Another thing yesterday (07.02.2010) problem was minimalized maybe from not biggest traffic that day or changes that were made (packet size from 16 back to 1 and threads cache size from 190 to 150 with restarts of both mysqld’s).
Web server didn’t break whole day, and choke’s was for 95% and maximum 97% of disk capabilities.
17:59 - only for 4 seconds disk usage was 60%, 90%, 71%, 76%
20:26 - minimum 4 seconds 75%, 95%, 97%, 79% dont know more…
only 1 query from that times in slow query log )
17:59 similiar like query described above LIMIT 784, 16;
20:26 similiar like query described above LIMIT 784, 16;
Here we go our problem is probably disk seek times because of big LIMIT’s.
) quite happy now, maybe some advices on sphinx pagination and its reliability.
Did you ever encounter sphinx returning false matches? For example exactly like described here:
http://www.sphinxsearch.com/forum/view.html?id=4922
We too searching with phrase like big number for example: 2256797652
and results are found in posts which dont have this number at all.
I know i should use probably SetLimits(xxx/xx) involving somehow this 864 id’s (above query) but not yet sure.
UPDATE: confirmed it 100% by tests ) big LIMIT causing disk usage jumping from 0% to 99% and on high traffic this causing massive disk blocking which resulting in queries queueying for minutes.
