Bug 223439

It seems that every item fetch job that fetches the headers takes insanely long. The same happens in KMailCVT, which fetches the headers to get the message IDs for duplicate checking.

mysql uses 120% CPU during that time, according to top.

The problem seems to be the part query in FetchHelper::parseStream().

This is an example debug output, with some timing info:

2010-02-02T21:57:55 Executing part query. 
SELECT PimItemTable.id, PartTable.name, PartTable.data, PartTable.version, PartTable.external FROM PimItemTable, PartTable WHERE ( PimItemTable.id = PartTable.pimItemId AND ( PartTable.name IN ( :0, :1 ) ) AND ( PimItemTable.id >= :2 ) AND collectionId = :3 ) ORDER BY PimItemTable.id ASC

 ":0" QVariant(QString, "COLLECTIONID")
 ":1" QVariant(QString, "PLD:HEAD")
 ":2" QVariant(qlonglong, 1)
 ":3" QVariant(qlonglong, 18882)

2010-02-02T21:58:23 Part query done, now building response.

Executing that query in the DB console has the same slowness.
Displaying the part table in the DB browser also takes about a minute here, with ~100k entries in there.

> "Akonadiconsole doesn't have this problem."

Not sure if that is really the case, I'll check again.

For some strange reason, the problem disappeared all of a sudden.
Where "all of a sudden" means after disabling some resources, restarting Akonadi a couple of times and even killing Akonadi + mysql once or twice.

For tracking down this problem, enabling the MySQL slow log might help.
See $KDEDIR/share/config/akonadi/mysql-global.conf, the settings log_slow_queries and long_query_time.

The slow log will appear at $HOME/.local/share/akonadi/db_data/mysql.slow

Torgny made a log at http://www.nyblom.org/pub/kde/mysql.slow, but I don't think it shows the problem - the query of comment#2 is not in there (grep for "ORDER BY Pimitemtable.id").

I can reproduce the problem again, after adding an IMAP resource:

# Time: 100203 12:56:26
# User@Host: [trunk] @ localhost []
# Query_time: 155  Lock_time: 0  Rows_sent: 33  Rows_examined: 968385
SELECT PimItemTable.id, PartTable.name, PartTable.data, PartTable.version, PartTable.external FROM PimItemTable, PartTable WHERE ( PimItemTable.id = PartTable.pimItemId AND ( PartTable.name IN ( 'COLLECTIONID', 'PLD:HEAD' ) ) AND ( PimItemTable.id >= 1 ) AND collectionId = 15 ) ORDER BY PimItemTable.id ASC;

> I can reproduce the problem again, after adding an IMAP resource:

The folder I opened was a local maildir folder, not an IMAP folder.

The query from comment #6 is executed twice when selecting a folder, doubling the delay.

As mentioned, just displaying the parttable in Akonadiconsole is also slow:

# User@Host: [thomas] @ localhost []
# Query_time: 27  Lock_time: 0  Rows_sent: 28918  Rows_examined: 28918
SELECT `id`, `pimItemId`, `name`, `data`, `datasize`, `version`, `external` FROM parttable;

Doesn't happen right now, I'll reopen when it happens again.

Thanks, Thomas. Closing. Reopen when it happens again.