Dspam, optimizing af database (mysql)

Hvis du kører DSPAM/mysql, så ved du måske allerede at databasen har det med at vokse.. lidt uhæmmet.

Du ved måske endda at du burde kører /usr/local/share/examples/dspam/mysql/purge.sql engang imellem, for at ryde op i din dspam database..

Du har måske også læst http://www.howtoforge.com/optimizing_dspam_mysql4.1 som virkelig gennemgår hvordan du laver ændringer i purge.sql (hvis du ikke selv har kunne se hvordan) for at få endnu mere speed ud af det.

Lagde du mærke til tilføjelserne af indexes i toppen?

Her har jeg kørt den ene på en af mine dspam installationer.. når den kører er dspam ikke tilgængelig..:

mysql> alter table dspam_token_data add index(last_hit);
Query OK, 38303129 rows affected (23 hours 34 min 53.97 sec)
Records: 38303129 Duplicates: 0 Warnings: 0

avs avs avs… Og det er en lille dspam database!

Hvor går galt?

Jeg er ikke den store mysql techie, men mysql bruger ikke den smarte key sort funktion, men tager simpelthen row for row og genopbygger hele index og database.. mens vi venter.. og det er ikke sejt når man har en databasestruktur der gør at index ikke kan være i memory.

Nåh, men hvad så? vi vil gerne optimizere databasen, men vi vil ikke vente 24 timer på en alter (evt en alter som ændre flere ting på een gang, men det er stadig 24 timer.. for en lille database)

Løsningen i sin enkelthed er at ignorere unique problemet, så herunder tilføjer vi to indexes uden at verificere genopbygningen med unique indexes
mysql> alter ignore table dspam_token_data add index(innocent_hits);
Query OK, 38304977 rows affected (12 min 32.85 sec)
Records: 38304977 Duplicates: 0 Warnings: 0

mysql> alter ignore table dspam_token_data add index(spam_hits);
Query OK, 38304977 rows affected (14 min 11.57 sec)
Records: 38304977 Duplicates: 0 Warnings: 0
wolla! Indexes bliver så automatisk tilføjet bagefter, med den meget hurtigere key sort metode.

PS: Hvis du nogensinde skulle få den sindsyge idé at dumpe en dspam database, måske på et par GB, for senere at importere den.. SLÅ UNIQUE INDEXET FRA! og tilføj det bagefter!