Bug 258361 - An account with many mails cannot be synced (performance problem)
Summary: An account with many mails cannot be synced (performance problem)
Status: RESOLVED UNMAINTAINED
Alias: None
Product: KMail Mobile
Classification: Applications
Component: general (show other bugs)
Version: unspecified
Platform: Windows CE Microsoft Windows CE
: NOR major
Target Milestone: ---
Assignee: Ludwig Reiter
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2010-11-30 12:23 UTC by Ludwig Reiter
Modified: 2016-09-29 07:52 UTC (History)
4 users (show)

See Also:
Latest Commit:
Version Fixed In:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Ludwig Reiter 2010-11-30 12:23:38 UTC
Version:           unspecified (using Devel) 
OS:                Windows CE

version: svn-1201778

Reproducible: Always

Steps to Reproduce:
Req: A normal account with many mails (some folders with 2000 or 1000 mails)
1. Remove config files.
2. Restart mobile.
3. Start kmail-mobile.
4. Configure the test account.

Actual Results:  
The account is not displayed in the resource list on the left of the main screen.
The mobile gets offline.

Expected Results:  
The account should be synced. The mobile should not get offline.
Comment 1 Volker Krause 2010-12-03 10:16:37 UTC
SVN commit 1203130 by vkrause:

Disable the session timeout for now, at least with such a small value it
causes more harm than good apparently.

BUG: 258271
CCBUG: 258361
CCBUG: 258378


 M  +1 -1      imapresource.kcfg  


WebSVN link: http://websvn.kde.org/?view=rev&revision=1203130
Comment 2 Andreas Holzammer 2010-12-06 17:24:00 UTC
This was a memory problem, which should be fixed now.
Comment 3 Volker Krause 2010-12-06 17:25:33 UTC
Fixed in revision 1204255.
Comment 4 Ludwig Reiter 2010-12-10 14:38:39 UTC
version: 4.6. beta2 (svn-1204839)

Not fixed.

Setup:
Inbox 10 mails
|-> large1 1050 mails
|-> large2 1050 mails
|-> large3 1050 mails

I select the inbox and waited 10(!) minutes, but the inbox says: No mails in this folder.

So not fixed.
Comment 5 Volker Krause 2010-12-13 13:00:39 UTC
Is the akonadi_imap_resource process still running? What does the global status indicator (top left in the app) show? What does the detailed resource status show (Home -> Actions -> Accounts)? Any change when toggling online/offline?
Comment 6 Tobias Koenig 2011-01-04 07:01:05 UTC
Ludwig, any news on this topic?
Comment 7 Ludwig Reiter 2011-01-10 16:36:11 UTC
It needs 25 mins to sync 2000 mails (large1, large2)
In this time the download arrow is displayed. and the account says:
[download arrow] Syncing collection 'large1' [or 'large2']
After 25 mins, I cannot scroll to every mail in large2. (Unread 997, but can only look at mail 1 to 122) and account: Ready.

I think 25 mins is too long to sync 2000 small mails and then I should be able to scroll to every mail in both folders.
Comment 8 Ludwig Reiter 2011-01-10 16:41:31 UTC
After additional 7 min Kontact told me that he is ready and then I can scroll all mails.
So the problem is the sync time (~32 minutes for 2000 mails)
Comment 9 Till Adam 2011-01-10 17:07:32 UTC
Ludwig, please try timing the same folder on both CE and N900, to see if the sync speed is comparable. If i is, we can debug/profile on Linux, which is much easier. It doesn't matter mcuh how you measure, as long as you measure the same on both devices. The absolute numbers don't matter much, only the relative ones.
Comment 10 Ludwig Reiter 2011-01-11 13:28:05 UTC
Tried the same operation with N900: Initial sync of my test account with 
two 1000 mails folders (large1 and large2) under N900: it needs about 2-3 min.
(I encountered another problem: cannot scroll to all mails in large1, but sync is complete and I can scroll all large2 mails. This is a different problem).
There is a factor of 10 between n900 and windows ce and so it is windows ce specific.
Comment 11 Ludwig Reiter 2011-01-12 12:23:15 UTC
Retested again with Windows CE. This time I looked at the CPU load while syncing large1:
At the beginning of the sync:
akonadi agent server ~13 
dbus ~8
akonadi_imap_resource ~6-14

In the middle of the sync:
kmail mobile 6-7
(sometimes) dbus ~8

So the bottleneck is not the CPU I would suppose.
Comment 12 Andre Heinecke 2011-01-14 19:01:04 UTC
	A	 server/src/storage/akonadidb_mobile.qrc	 [License: Trivialfile.]


	A	 server/src/storage/akonadidb-mobile.xml	 [License: Trivialfile.]

commit 8a2400f131cba56c6c8ce37e27073c23210d7fb2
Author: Andre Heinecke <aheinecke@intevation.de>
Date:   Fri Jan 14 17:56:56 2011 +0000

    Optimize WinCE db performance by removing indices.
    
         SQLite benchmarks on Windows CE have shown that creation
         and maintenance of indices are expensive. It has to be
         evaluated if they are used by SQLites query planner
         with the statements by Akonadi anyhow. Tests have shown an
         improvement for initial sync on WinCE of ~40% which is
         also the difference we saw benchmarking many insert statements.
    
         CCBUG: 258361

diff --git a/server/CMakeLists.txt b/server/CMakeLists.txt
index da8e912..036e803 100644
--- a/server/CMakeLists.txt
+++ b/server/CMakeLists.txt
@@ -32,21 +32,27 @@ endif ( MYSQLD_EXECUTABLE )
 
 ########### next target ###############
 
+if(NOT WINCE)
+   set (AKONADI_DB_SCHEME ${CMAKE_CURRENT_SOURCE_DIR}/src/storage/akonadidb.xml)
+else(NOT WINCE)
+   set (AKONADI_DB_SCHEME ${CMAKE_CURRENT_SOURCE_DIR}/src/storage/akonadidb-mobile.xml)
+endif(NOT WINCE)
+
 add_custom_command(
   OUTPUT ${CMAKE_CURRENT_BINARY_DIR}/entities.h
          ${CMAKE_CURRENT_BINARY_DIR}/entities.cpp
   COMMAND ${XSLTPROC_EXECUTABLE} --stringparam code header
           ${CMAKE_CURRENT_SOURCE_DIR}/src/storage/entities.xsl
-          ${CMAKE_CURRENT_SOURCE_DIR}/src/storage/akonadidb.xml
+          ${AKONADI_DB_SCHEME}
           > ${CMAKE_CURRENT_BINARY_DIR}/entities.h
   COMMAND ${XSLTPROC_EXECUTABLE} --stringparam code source
           ${CMAKE_CURRENT_SOURCE_DIR}/src/storage/entities.xsl
-          ${CMAKE_CURRENT_SOURCE_DIR}/src/storage/akonadidb.xml
+          ${AKONADI_DB_SCHEME}
           > ${CMAKE_CURRENT_BINARY_DIR}/entities.cpp
   DEPENDS ${CMAKE_CURRENT_SOURCE_DIR}/src/storage/entities.xsl
           ${CMAKE_CURRENT_SOURCE_DIR}/src/storage/entities-header.xsl
           ${CMAKE_CURRENT_SOURCE_DIR}/src/storage/entities-source.xsl
-          ${CMAKE_CURRENT_SOURCE_DIR}/src/storage/akonadidb.xml
+          ${AKONADI_DB_SCHEME}
 )
 
 set(libakonadiprivate_SRCS
@@ -168,7 +174,11 @@ set_source_files_properties(${xesam_xml} PROPERTIES INCLUDE "xesamtypes.h")
 
 qt4_add_dbus_interface( libakonadiprivate_SRCS ${xesam_xml} xesaminterface )
 
-qt4_add_resources( libakonadiprivate_SRCS src/storage/akonadidb.qrc )
+if(NOT WINCE)
+  qt4_add_resources( libakonadiprivate_SRCS src/storage/akonadidb.qrc )
+else(NOT WINCE)
+  qt4_add_resources( libakonadiprivate_SRCS src/storage/akonadidb_mobile.qrc )
+endif(NOT WINCE)
 
 
 automoc4_add_library( akonadiprivate STATIC ${libakonadiprivate_SRCS} )
diff --git a/server/src/main.cpp b/server/src/main.cpp
index a7637e2..8543c6b 100644
--- a/server/src/main.cpp
+++ b/server/src/main.cpp
@@ -53,7 +53,11 @@ void shutdownHandler( int )
 
 int main( int argc, char ** argv )
 {
+#ifndef _WIN32_WCE
     Q_INIT_RESOURCE( akonadidb );
+#else
+    Q_INIT_RESOURCE( akonadidb_mobile );
+#endif
     AkApplication app( argc, argv );
     app.setDescription( QLatin1String( "Akonadi Server\nDo not run manually, use 'akonadictl' instead to start/stop Akonadi." ) );
 
@@ -89,6 +93,11 @@ int main( int argc, char ** argv )
 
     Akonadi::AkonadiServer::instance()->quit();
 
+#ifndef _WIN32_WCE
     Q_CLEANUP_RESOURCE( akonadidb );
+#else
+    Q_CLEANUP_RESOURCE( akonadidb_mobile );
+#endif
+
     return result;
 }
diff --git a/server/src/storage/akonadidb-mobile.xml b/server/src/storage/akonadidb-mobile.xml
new file mode 100644
index 0000000..b50224c
--- /dev/null
+++ b/server/src/storage/akonadidb-mobile.xml
@@ -0,0 +1,185 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+
+    Copyright (c) 2006 Volker Krause <vkrause@kde.org>
+    Copyright (C) 2006 by Tobias Koenig <tokoe@kde.org>
+
+    This library is free software; you can redistribute it and/or modify it
+    under the terms of the GNU Library General Public License as published by
+    the Free Software Foundation; either version 2 of the License, or (at your
+    option) any later version.
+
+    This library is distributed in the hope that it will be useful, but WITHOUT
+    ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
+    FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Library General Public
+    License for more details.
+
+    You should have received a copy of the GNU Library General Public License
+    along with this library; see the file COPYING.LIB.  If not, write to the
+    Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
+    02110-1301, USA.
+
+-->
+
+
+<!--
+  Akonadi Database Schema Definition
+
+  This file is used to generate database access code as well as the tables itself.
+
+  Database element (root node, can contain table and relation elements)
+
+  Table attributes (can contain column, reference and data elements)
+  - name: The entity class name, the table will be named FooTable.
+
+  Column attributes:
+  - name: The column name, will be also used as accessor name in the entity class.
+  - type: The C++ type of this column, the SQL type will be derived from that.
+  - sqltype: The SQL type (optional), overwrites types derived from the C++ type.
+  - default: Default value for entity class constructor.
+  - refTable, refColumn: foreign key, also used to generate accessor methods for 1:n relations
+  - methodName: method name to access referred records, the table name is used if not given
+
+  Indices:
+  - name: The name of the index
+  - columns: The columns covered by this index
+  - unique: bool, indicates if this index must contain unique data
+
+  Reference attributes (indication n:1 relations)
+  - name: Used for naming the method during code generation
+  - table: name of the table a n:1 relation exist
+  - key: column name of the referenced table containing the foreign key
+
+  Initial data:
+  - columns: A comma separated list of column names
+  - values: A comma separated and SQL-quoted list of values
+
+  Relation elements define a n:m relation between two tables.
+  Attributes:
+  - table[12]: Table names
+  - column[12]: Column names
+
+-->
+<database>
+
+  <table name="SchemaVersion">
+    <comment>Contains the schema version of the database.</comment>
+    <column name="version" type="int" default="0" allowNull="false"/>
+    <data columns="version" values="19"/>
+  </table>
+
+  <table name="Resource">
+    <column name="id" type="qint64" allowNull="false" isAutoIncrement="true" isPrimaryKey="true"/>
+    <column name="name" type="QString" allowNull="false" isUnique="true"/>
+    <column name="isVirtual" type="bool" default="false"/>
+    <reference name="collections" table="Collection" key="resourceId"/>
+    <data columns="name,isVirtual" values="'akonadi_search_resource',true"/>
+  </table>
+
+  <table name="Collection">
+    <column name="id" type="qint64" allowNull="false" isAutoIncrement="true" isPrimaryKey="true"/>
+    <column name="remoteId" type="QString"/>
+    <column name="remoteRevision" type="QString"/>
+    <column name="name" type="QString" allowNull="false"/>
+    <column name="parentId" type="qint64" refTable="Collection" refColumn="id" methodName="parent"/>
+    <column name="resourceId" type="qint64" refTable="Resource" refColumn="id" allowNull="false"/>
+    <column name="subscribed" type="bool" default="true" allowNull="false"/>
+    <column name="cachePolicyInherit" type="bool" default="true" allowNull="false"/>
+    <column name="cachePolicyCheckInterval" type="int" default="-1" allowNull="false"/>
+    <column name="cachePolicyCacheTimeout" type="int" default="-1" allowNull="false"/>
+    <column name="cachePolicySyncOnDemand" type="bool" default="false" allowNull="false"/>
+    <column name="cachePolicyLocalParts" type="QString"/>
+    <column name="queryString" type="QString"/>
+    <column name="queryLanguage" type="QString"/>
+    <index name="parentAndNameIndex" columns="parentId,name" unique="true"/>
+    <reference name="children" table="Collection" key="parentId"/>
+    <reference name="items" table="PimItem" key="collectionId"/>
+    <reference name="attributes" table="CollectionAttribute" key="collectionId"/>
+    <data columns="parentId,name,resourceId" values="NULL,'Search',1"/>
+  </table>
+
+  <table name="MimeType">
+    <comment>This meta data is stored inside akonadi to provide fast access.</comment>
+    <column name="id" type="qint64" allowNull="false" isAutoIncrement="true" isPrimaryKey="true"/>
+    <column name="name" type="QString" allowNull="false" isUnique="true"/>
+    <data columns="name" values="'application/octet-stream'"/>
+    <data columns="name" values="'message/rfc822'"/>
+    <data columns="name" values="'text/calendar'"/>
+    <data columns="name" values="'text/vcard'"/>
+    <data columns="name" values="'inode/directory'"/>
+  </table>
+
+  <table name="PimItem">
+    <column name="id" type="qint64" allowNull="false" isAutoIncrement="true" isPrimaryKey="true"/>
+    <column name="rev" type="int" default="0" allowNull="false"/>
+    <column name="remoteId" type="QString"/>
+    <column name="remoteRevision" type="QString"/>
+    <column name="collectionId" type="qint64" refTable="Collection" refColumn="id"/>
+    <column name="mimeTypeId" type="qint64" refTable="MimeType" refColumn="id"/>
+    <column name="datetime" type="QDateTime" default="QDateTime::currentDateTime()">
+      <comment>create/modified time</comment>
+    </column>
+    <column name="atime" type="QDateTime">
+      <comment>read access time</comment>
+    </column>
+    <column name="dirty" type="bool">
+      <comment>Indicates that this item has unsaved changes.</comment>
+    </column>
+    <column name="size" type="qint64" default="0" allowNull="false"/>
+    <!--
+    This index is very expensive to create and in most cases uneccessary.
+    <index name="collectionIndex" columns="collectionId" unique="false"/>
+    -->
+    <reference name="parts" table="Part" key="pimItemId"/>
+  </table>
+
+  <table name="Flag">
+    <comment>This meta data is stored inside akonadi to provide fast access.</comment>
+    <column name="id" type="qint64" allowNull="false" isAutoIncrement="true" isPrimaryKey="true"/>
+    <column name="name" type="QString" allowNull="false" isUnique="true"/>
+    <data columns="name" values="'important'"/>
+    <data columns="name" values="'has_attachment'"/>
+    <data columns="name" values="'spam'"/>
+    <data columns="name" values="'\ANSWERED'"/>
+    <data columns="name" values="'\FLAGGED'"/>
+    <data columns="name" values="'\DELETED'"/>
+    <data columns="name" values="'\SEEN'"/>
+    <data columns="name" values="'\DRAFT'"/>
+  </table>
+
+  <table name="Part">
+    <column name="id" type="qint64" allowNull="false" isAutoIncrement="true" isPrimaryKey="true"/>
+    <column name="pimItemId" type="qint64" refTable="PimItem" refColumn="id" allowNull="false"/>
+    <column name="name" type="QString" allowNull="false"/>
+    <column name="data" type="QByteArray"/>
+    <column name="datasize" type="qint64" allowNull="false"/>
+    <column name="version" type="int" default="0"/>
+    <column name="external" type="bool" default="false" />
+    <index name="pimItemIdNameIndex" columns="pimItemId,name" unique="true"/>
+    <!--
+    This index is very expensive to create and in most cases uneccessary.
+    <index name="pimItemNameIndex" columns="name" unique="false"/>
+    -->
+  </table>
+
+  <table name="CollectionAttribute">
+    <column name="id" type="qint64" allowNull="false" isAutoIncrement="true" isPrimaryKey="true"/>
+    <column name="collectionId" type="qint64" refTable="Collection" refColumn="id" allowNull="false"/>
+    <column name="type" type="QByteArray" allowNull="false"/>
+    <column name="value" type="QByteArray"/>
+    <!--
+    This index is very expensive to create and in most cases uneccessary.
+    <index name="collectionIndex" columns="collectionId" unique="false"/>
+    -->
+  </table>
+
+  <relation table1="PimItem" column1="id" table2="Flag" column2="id"/>
+
+  <relation table1="Collection" column1="id" table2="MimeType" column2="id">
+    <comment>Specifies allowed MimeType for a Collection</comment>
+  </relation>
+
+  <relation table1="Collection" column1="id" table2="PimItem" column2="id">
+    <comment>Used to associate items with search folders.</comment>
+  </relation>
+</database>
diff --git a/server/src/storage/akonadidb_mobile.qrc b/server/src/storage/akonadidb_mobile.qrc
new file mode 100644
index 0000000..839f087
--- /dev/null
+++ b/server/src/storage/akonadidb_mobile.qrc
@@ -0,0 +1,6 @@
+<!DOCTYPE RCC><RCC version="1.0">
+<qresource>
+ <file>akonadidb-mobile.xml</file>
+ <file>dbupdate.xml</file>
+</qresource>
+</RCC>
diff --git a/server/src/storage/datastore.cpp b/server/src/storage/datastore.cpp
index 6e0ba91..ebe5c25 100644
--- a/server/src/storage/datastore.cpp
+++ b/server/src/storage/datastore.cpp
@@ -118,7 +118,12 @@ void Akonadi::DataStore::close()
 
 bool Akonadi::DataStore::init()
 {
-  DbInitializer::Ptr initializer = DbInitializer::createInstance( m_database, QLatin1String(":akonadidb.xml") );
+  QString dbtemplate = QLatin1String(":akonadidb.xml");
+#ifdef Q_OS_WINCE
+  dbtemplate = QLatin1String(":akonadidb-mobile.xml");
+#endif
+
+  DbInitializer::Ptr initializer = DbInitializer::createInstance( m_database, dbtemplate );
   if (! initializer->run() ) {
     akError() << initializer->errorMsg();
     return false;
diff --git a/server/tests/unittest/dbupdatertest.cpp b/server/tests/unittest/dbupdatertest.cpp
index ab6f7c4..1cdc04f 100644
--- a/server/tests/unittest/dbupdatertest.cpp
+++ b/server/tests/unittest/dbupdatertest.cpp
@@ -30,7 +30,11 @@ QTEST_MAIN( DbUpdaterTest )
 
 void DbUpdaterTest::initTestCase()
 {
+#ifndef Q_OS_WINCE
   Q_INIT_RESOURCE( akonadidb );
+#else
+  Q_INIT_RESOURCE( akonadidb_mobile );
+#endif
 }
 
 void DbUpdaterTest::testMysqlUpdateStatements()
@@ -122,7 +126,11 @@ void DbUpdaterTest::testPsqlUpdateStatements()
 
 void DbUpdaterTest::cleanupTestCase()
 {
+#ifndef Q_OS_WINCE
   Q_CLEANUP_RESOURCE( akonadidb );
+#else
+  Q_CLEANUP_RESOURCE( akonadidb_mobile );
+#endif
 }
 
 #include "dbupdatertest.moc"
Comment 13 Tobias Koenig 2011-01-20 18:52:35 UTC
Ludwig, can you restest please with new packages if the changes to the sqlite backend improved performance?
Comment 14 David Faure 2012-01-06 12:17:52 UTC
Git commit d31715db768b477e8e5d7acaf852924f356c42e2 by David Faure.
Committed on 06/01/2012 at 12:33.
Pushed by dfaure into branch 'KDE/4.8'.

KTcpSocket: forward encryptedBytesWritten from QSslSocket.

Very important for the timeout detection in the akonadi imap resource.
write(5MB) gives an immediate bytesWritten(5MB), only encryptedBytesWritten
allows to see some regular activity on the socket.
Related: bug 258271

M  +1    -0    kdecore/network/ktcpsocket.cpp
M  +4    -0    kdecore/network/ktcpsocket.h

http://commits.kde.org/kdelibs/d31715db768b477e8e5d7acaf852924f356c42e2
Comment 15 David Faure 2012-01-06 12:18:23 UTC
Git commit 2bd26e7ea8e5799a60152ef4450edb30c11bd47b by David Faure.
Committed on 06/01/2012 at 12:40.
Pushed by dfaure into branch 'master'.

Fix wrong timeout when uploading/downloading large messages.

This fix requires the ktcpsocket encryptedBytesWritten signal added to kdelibs-4.8[.1?]
Related: bug 258271, bug 258378

M  +5    -0    kimap/session.cpp
M  +1    -0    kimap/session.h
M  +1    -0    kimap/session_p.h
M  +8    -1    kimap/sessionthread.cpp

http://commits.kde.org/kdepimlibs/2bd26e7ea8e5799a60152ef4450edb30c11bd47b
Comment 16 Sebastian Trueg 2012-01-27 13:20:54 UTC
Git commit 0f4f6e97f84fe5cd10ebcafeb6676026f9efd486 by Sebastian Trueg, on behalf of David Faure.
Committed on 06/01/2012 at 12:33.
Pushed by trueg into branch 'KDE/4.8'.

KTcpSocket: forward encryptedBytesWritten from QSslSocket.

Very important for the timeout detection in the akonadi imap resource.
write(5MB) gives an immediate bytesWritten(5MB), only encryptedBytesWritten
allows to see some regular activity on the socket.
Related: bug 258271

M  +1    -0    kdecore/network/ktcpsocket.cpp
M  +4    -0    kdecore/network/ktcpsocket.h

http://commits.kde.org/kdelibs/0f4f6e97f84fe5cd10ebcafeb6676026f9efd486