HOW TO: Increase Data Directory Synchroniser speed on an external database

This section contains user-submitted tutorials.
Post Reply
User avatar
jimimaseye
Moderator
Moderator
Posts: 8011
Joined: 2011-09-08 17:48

HOW TO: Increase Data Directory Synchroniser speed on an external database

Post by jimimaseye » 2016-05-22 12:40

Data Directory Synchroniser ("DDS") is a utility provided as standard with Hmailserver that checks that all the email (.EML) files within the data directory exists in the Database and, if not, either imports or deletes those found missing (depending on your preferred choice). (See here for more information: https://www.hmailserver.com/documentati ... rguide_dds).

The speed of DDS is, though, quite slow and is impacted depending on the amount of physical .EML files there is to check.

The following procedure will enable a significant increase in the speed of DDS by adding an index to the database. Under normal circumstances, it is only possible to perform it on an external database due to need to access the database as a Database Administrator (this is not possible for the internal database). To access the internal database would require extra 3rd party software such as DatabaseBrowserPortable (archived here). So if you are running the internal database and do not have such software then stop reading now. However, access with DatabaseBrowserPortable is possible and the same tweak can be made (you will need to decrypt the database password from your hmailserver INI file to access the internal database. See footnote below**). If you want the benefit of easy access to Administration of the database in the future, you might also consider upgrading to an external database - see here for a guide; the internal is small and against EULA for commercial applications. And you cant do things like this to it. :wink:

!!! DO NOT ATTEMPT THIS PROCEDURE IF YOU ARE NOT COMPETENT or familiar with database administration. You can totally break the SQL database !!! You have been warned.

EXAMPLE OF BENEFIT

On my system there are 33,000 Email files. A DDS check takes 10 minutes.

With this index in place, it takes only 33 seconds.

That is a significant increase, I think you'll agree.

PROCEDURE

In this tutorial I am using and referring to the MySQL database (version 5.5). The SQL commands will be the same with MSSQL and PostgreSQL databases but the access to them databases will be different. As the database Administrator, you are responsible for knowing how to access this. Some installations may also have a GUI equivalent (an Administration Console GUI) such as Workbench (for MySQL) and of course the index could be added using that (and not the command line method, below). Of course, being an experienced and confident administrator of your database, you will know what is best for you to perform the index creation (and deletion) according to these guidelines.

Note: The stage of actually running DDS is stage (5) below.

1, cd to the MySQL bin directory (eg, C:\Program Files\MySQL\MySQL Server 5.5\bin )

2, Launch the MySQL command line administration prompt with following command (where 'root' and 'password' are your access details to your database

Code: Select all

mysql -uroot -ppassword
C:\Program Files\MySQL\MySQL Server 5.5\bin>mysql -uroot -ppassword
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 161
Server version: 5.5.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
3, Connect to your database (where "Hmaildata" is the name of your database):

Code: Select all

connect Hmaildata
mysql> connect Hmaildata
Connection id: 146
Current database: Hmaildata
4, Create an index on the messagefilename column:

Code: Select all

CREATE INDEX tmp_hm_messages_filename ON hm_messages (messagefilename);
mysql> CREATE INDEX tmp_hm_messages_filename ON hm_messages (messagefilename);
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0
This has now created an index.

5, At this point you can run your DDS.

6, It is advisable to then delete the index after you have completed the DDS run.*

MySQL:

Code: Select all

drop index tmp_hm_messages_filename on hm_messages ;
MSSQL and Internal Database (MSSQLCE):

Code: Select all

DROP INDEX hm_messages.tmp_hm_messages_filename
PostgreSQL (untested!):

Code: Select all

DROP INDEX tmp_hm_messages_filename;
mysql> drop index tmp_hm_messages_filename on hm_messages ;
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
*Note: adding such an index to the tables could in theory be detrimental to performance on heavy load (busy) servers as it means the index is checked and updated for each and every email record write/change (incoming, outgoing, moving to a folder, being deleted - they all involve writing and deleting from the hm_message table and the index). For this reason, it is advisable to delete the index. However, you may test and leave the index in place and then monitor your system (if you feel you might be a light user) to see if there is any noticeable difference or benefit. (You never know, it might also improve it. It remains untested either way.)



** FOOTNOTE:
To access the internal database using DatabaseBrowserPortable you wll need the database password:
  • a, open your HMAILSERVER.INI file and select the 'password' string under
    [Database]
    Password=a2bd344fc6608264d2d6b3ca2f89303
    b, Run hmailsever/Addons/Utilities/DecryptBlowfish.vbs and where prompted enter the string copied. This will create and display a short string of characters (this is the password). Copy this string of characters.

    c, Run DatabaseBrowserPortable, and choose 'Connections - Add' and point to your internal Data directory database (remember to choose MSSQL CE as database type). Enter "Hmailserver" as the User name and the above decrypted string as the password.
Done.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

Gordonh1970
Normal user
Normal user
Posts: 42
Joined: 2016-01-29 13:50
Location: UK
Contact:

Re: HOW TO: Increase Data Directory Synchroniser speed on an external database

Post by Gordonh1970 » 2016-06-30 19:06

Jim,

After having huge success implementing your ClamD + SaneSecurity solution (I love but did break it by trying to implement the 64 bit version of Clamwin, had to remove that then do a fresh install of everything in your post but following your instructions made it very, very straightforward :D ) I thought I'd try to do a little with DDS

I'm running on a Windows Server 2008 R2 machine with MySQL Server v5.7.13 abd the DDS bit works fine, and is less than a minute after adding the table in

My only question is "would this be a task that is worth repeating regularly (say weekly) and if so, can you run DDS from Task Scheduler with optional arguments to allow for the password, options etc"

Thanks for any ideas

User avatar
jimimaseye
Moderator
Moderator
Posts: 8011
Joined: 2011-09-08 17:48

Re: HOW TO: Increase Data Directory Synchroniser speed on an external database

Post by jimimaseye » 2016-06-30 19:35

Hi Gordon

Simple answer is no. I personally dont see any reason why you would need to do DDS on such a schedule. DDS is there as a utility to help you 'recover' from situations that otherwise shouldnt have happened in normal circumstances (ie strange unaccounted crashes that may have occurred). If you see your system as working and without problems, and you dont have any reason to think there have been problems, then its not worth it running it so frequently. (I run it about twice a year - just whenever I think about doing it just because 'its been a while'). In other words, unless you have reason to believe that something has gone wrong and you MIGHT benefit from running it, I wouldnt bother doing it so frequently. (You may have noticed in the past when you run it that it doesnt actually do or find anything.)

That said, even if I were to suggest to do it as a regular housekeeping exercise, I personally wouldnt know how to go about doing it as a scheduled task. :mrgreen:
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

User avatar
jimimaseye
Moderator
Moderator
Posts: 8011
Joined: 2011-09-08 17:48

Re: HOW TO: Increase Data Directory Synchroniser speed on an external database

Post by jimimaseye » 2016-07-06 22:02

UPDATE:

Initial guide modified to include instruction relevant to users of Internal Database too.
HMS 5.6.6 B2383 on Win Server 2008 R2 Foundation, + 5.6.7-B2415 on test.
SpamassassinForWindows 3.4.0 spamd service
AV: Clamwin + Clamd service + sanesecurity defs : https://www.hmailserver.com/forum/viewtopic.php?f=21&t=26829

nieuwhier
New user
New user
Posts: 16
Joined: 2012-10-30 15:48

Re: HOW TO: Increase Data Directory Synchroniser speed on an external database

Post by nieuwhier » 2018-03-20 11:20

thx!!

Adding CREATE INDEX tmp_hm_messages_filename ON hm_messages (messagefilename); to the internal DB dropped the time from 45 minutes for 425 mails to 7 seconds!
You can leave the index in the database, that causes no harm!

Post Reply