On Fri, Jun 14, 2002 at 03:13:04PM +0500, Alexander Barkov wrote: > Kreso wrote: > > On Wed, Jun 12, 2002 at 08:28:27PM +0500, Alexander Barkov wrote: > > > >>What does "mysqladmin processlist" display? > >> > >>Are there any active queries being executed by > >>mnogosearch? > > > > > > Well, I switched the query logging on. In the query log file I can see: > > > > $ ./indexer -Cw -t "ECA" & > > $ tail -f /var/log/mysqld.log > > 020613 10:55:26 4 Connect mnogosearch@localhost on mnogosearch > > 4 Query SELECT rec_id FROM url WHERE rec_id<>0 > > AND (url.tag LIKE 'ECA') LIMIT 128 > > 4 Query DELETE FROM ndict2 WHERE url_id > > in > > >(76815,76816,76817,76818,76819,76820,76821,76822,76823,76824,76825,76826,76827,76828,76829,76830,76831,76832,76833,76834,76835,76836,76837,76838,76839,76840,76841,76842,76843,76844,76845,76846,76847,76848,76849,76850,76851,76852,76853,76854,76855,76856,76857,76858,76859,76860,76861,76862,76863,76864,76865,76866,76867,76868,76869,76870,76871,76872,76873,76874,76875,76876,76877,76878,76879,76880,76881,76882,76883,76884,76885,76886,76887,76888,76889,76890,76891,76892,76893,76894,76895,76896,76897,76898,76899,76900,76901,76902,76903,76904,76905,76906,76907,76908,76909,76910,76911,76912,76913,76914,76915,76916,76917,76918,76919,76920,76921,76922,76923,76924,76925,76926,76927,76928,76929,76930,76931,76932,76933,76934,76935,76936,76937,76938,76939,76940,76941,76942) > > 4 Query DELETE FROM ndict3 WHERE url_id > > in > > >(76815,76816,76817,76818,76819,76820,76821,76822,76823,76824,76825,76826,76827,76828,7682 > > ... > > (many lines deleted) > > > > > > with the heavy disk I/O activity. Now, this does not look like very efficient > > implementation, does it? Everything works very slow, indexer obviously > > executes enormous number of queries in order to accomplish simple(?) task > > of deleting all entries which correspond to a certain tag. > > Do you have any ideas how to make it faster? The problem > is that it is not enough just to remove records from "url" > table. You have also to delete correspondent records in > all "ndidcXX" tables. As far as MySQL doesn't support > delete from select, we have to do it in a such kind of > loop. If you have any ideas how to improve it, you are > welcome to share them!
Well, as far as ndictxx tables are concerned, I would not like to propose any ad hoc solutions which you have probably tested already. In any case, I would try to avoid any solution with mysql which leads to SELECT looping, even at the price of search speed. Would it be better to use single ndict table instead, for faster record deletion? Or maybe some other database, like postgresql which supports nested selects, subselects etc with ndictxx? Kresimir > > > > Which points > > to the fact that certain trade-offs were made regarding search speed > > vs. update speed. > > > > However, what concerns me more is that the whole process > > died after three hours without finishing the task. I wonder how much > > is the setup with multiple dictionaries tested with higher number of URLs? > > Which possible mysql tuning parameters would you recommend in order > > to speed up the deletion process? Is it so that during the > > deletion process indexer stores the state variables internally which > > then grow with the number of URLs which are to be deleted? > > > > I think that key buffer size mostly affects the speed of such > operation. Try to increse it to 128M or 256M or even more. > ___________________________________________ If you want to unsubscribe send "unsubscribe general" to [EMAIL PROTECTED]
