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: 9468
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.
5.7 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: 9468
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:
5.7 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: 9468
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.
5.7 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!

User avatar
mattg
Moderator
Moderator
Posts: 21878
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

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

Post by mattg » 2022-01-25 04:21

Doing this broke DDS for me in version 5.7 (ALPHA)

Once I removed the extra (duplicated) index DDS worked fine
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

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

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

Post by jimimaseye » 2022-01-25 09:52

mattg wrote:
2022-01-25 04:21
Doing this broke DDS for me in version 5.7 (ALPHA)

Once I removed the extra (duplicated) index DDS worked fine
How strange. What do you think the cause or difference would be in 5.7? After all, it's only an index, no data is being changed.

[Entered by mobile. Excuse my spelling.]
5.7 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
katip
Senior user
Senior user
Posts: 1040
Joined: 2006-12-22 07:58
Location: Istanbul

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

Post by katip » 2022-01-25 10:06

jimimaseye wrote:
2022-01-25 09:52
How strange. What do you think the cause or difference would be in 5.7? After all, it's only an index, no data is being changed.
5.7 comes ex works with an idx_hm_messagefilename i think??
Katip
--
HMS 5.7, MariaDB 10.4.10, SA 3.4.4, ClamAV 0.103.5

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

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

Post by jimimaseye » 2022-01-25 11:00

katip wrote:
2022-01-25 10:06
jimimaseye wrote:
2022-01-25 09:52
How strange. What do you think the cause or difference would be in 5.7? After all, it's only an index, no data is being changed.
5.7 comes ex works with an idx_hm_messagefilename i think??
Ohh! Ok.

(Not that I doubt Mattg at all, but) if someone else can also do a test on 5.7 and double check and get the same experience as Mattg then we can safely conclude that it is not 'a thing' to be done on 5.7 (and I will update the inital post above).
5.7 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
RvdH
Senior user
Senior user
Posts: 2029
Joined: 2008-06-27 14:42
Location: The Netherlands

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

Post by RvdH » 2022-01-25 14:30

katip wrote:
2022-01-25 10:06
jimimaseye wrote:
2022-01-25 09:52
How strange. What do you think the cause or difference would be in 5.7? After all, it's only an index, no data is being changed.
5.7 comes ex works with an idx_hm_messagefilename i think??
Nope, maybe Mattg created it double
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

User avatar
mattg
Moderator
Moderator
Posts: 21878
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

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

Post by mattg » 2022-01-25 15:31

yes, maybe

I have the following indexes on hm_messages

PRIMARY = messageid
messageid on messageid
idx_hm_messages on (messageaccountid AND messagefolderid)
idx_hm_messages_type on messagetype
messagefilename on messagefilename


I also had the tmp_hm_messages_filename ON messagefilename, which when deleted allowed the DDS to finish
I note that the DDS stops and re-starts hmailserver at the end of the process, with this extra index, it stopped, but wouldn't re-start
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
RvdH
Senior user
Senior user
Posts: 2029
Joined: 2008-06-27 14:42
Location: The Netherlands

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

Post by RvdH » 2022-01-25 15:34

Default table on creation (5.7.0)

Code: Select all

create table hm_messages 
(
	messageid bigint auto_increment not null, primary key(`messageid`), unique(`messageid`),
	messageaccountid int not null ,
	messagefolderid integer not null DEFAULT 0,
	messagefilename varchar (255) not null ,
	messagetype tinyint not null ,
	messagefrom varchar (255) not null ,
	messagesize bigint not null,
	messagecurnooftries int not null,
	messagenexttrytime datetime not null,
	messageflags tinyint not null,
	messagecreatetime datetime not null,
	messagelocked tinyint not null,
   messageuid bigint not null
) DEFAULT CHARSET=utf8;

CREATE INDEX idx_hm_messages ON hm_messages (messageaccountid, messagefolderid);

CREATE INDEX idx_hm_messages_type ON hm_messages (messagetype);
CIDR to RegEx: d-fault.nl/cidrtoregex
DNS Lookup: d-fault.nl/dnstools
DKIM Generator: d-fault.nl/dkimgenerator
DNSBL Lookup: d-fault.nl/dnsbllookup
GEOIP Lookup: d-fault.nl/geoiplookup

User avatar
katip
Senior user
Senior user
Posts: 1040
Joined: 2006-12-22 07:58
Location: Istanbul

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

Post by katip » 2022-01-25 15:54

RvdH wrote:
2022-01-25 14:30
Nope, maybe Mattg created it double
indeed, DBscripts don't create it. i should have done mine.
but how did i invent such a nice name? really don't remember. :P :lol:
Katip
--
HMS 5.7, MariaDB 10.4.10, SA 3.4.4, ClamAV 0.103.5

User avatar
mattg
Moderator
Moderator
Posts: 21878
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

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

Post by mattg » 2022-01-26 12:29

RvdH wrote:
2022-01-25 15:34
Default table on creation (5.7.0)
So I should NOT have the

messagefilename on messagefilename
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

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

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

Post by jimimaseye » 2022-01-26 12:48

Dont think so. Not by default.

That index is the temporary index suggested in this thread (to speed up). It seems you have added it already.

[Entered by mobile. Excuse my spelling.]
5.7 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

Post Reply