Giulio Ferro wrote: > Jake Anderson wrote: >> Giulio Ferro wrote: >>> I'm having serious problems in a production environment with >>> slow queries. >>> S.o.: freebsd 7 stable amd64. DB: mysql5.1.28 compiled from source >>> >>> The query that slows the db is: >>> SELECT messageblk, is_header FROM dbmail_messageblks WHERE >>> physmessage_id = xxxx ORDER BY messageblk_idnr >>> >>> >>> I've set up the db as instructed in the dbmail wiki. Everything was >>> smooth >>> for several days, than these queries have begun to slow the db server. >>> >>> I know this probably depends on the db and not on dbmail, but any >>> suggestion >>> on how to solve this would be greatly appreciated... >>> >>> Thanks. >>> >>> _______________________________________________ >>> DBmail mailing list >>> [email protected] >>> https://mailman.fastxs.nl/mailman/listinfo/dbmail >> try doing an explain select, but really that should be one of the >> fastest queries in the thing. >> how much ram do you have in the machine and how big is the database? >> >> explain SELECT messageblk, is_header FROM dbmail_messageblks WHERE >> physmessage_id = xxxx ORDER BY messageblk_idnr > > This is the explain: > > +----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+ > > | id | select_type | table | type | > possible_keys | key | key_len > | ref | rows | Extra | > +----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+ > > | 1 | SIMPLE | dbmail_messageblks | index | > physmessage_id_index,physmessage_id_is_header_index | PRIMARY | 8 > | NULL | 2360882 | Using where | > +----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+
That don't smell right. At all! 2360882 rows is totally fucked. That query shouldn't have to scan more than two or three rows. Your indexes are possibly messed up. The physmessage_id_index isn't being used at all, afaict. try: show create table dbmail_messageblks; maybe that will tell us something. -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl _______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
