Differences

This shows you the differences between two versions of the page.

Link to this comparison view

performance [2012/02/27 21:34]
bas Removed spam link.
performance [2012/09/07 11:55] (current)
zmi
Line 5: Line 5:
 This a collection of performance enhancements we did on dbmail 2.2 using PostgreSQL 8.1. This a collection of performance enhancements we did on dbmail 2.2 using PostgreSQL 8.1.
  
-==== Drop unneeded indices ====+==== Cleanup indices ====
  
 Currently there are 2 indices dbmail_messageblks_physmessage_idx and dbmail_messageblks_physmessage_is_header_idx which are almost identical, so we can remove one. This saves accesses on every insert/update/delete on that table. It has no penalties, as the dbmail_messageblks_physmessage_is_header_idx has the same information. Currently there are 2 indices dbmail_messageblks_physmessage_idx and dbmail_messageblks_physmessage_is_header_idx which are almost identical, so we can remove one. This saves accesses on every insert/update/delete on that table. It has no penalties, as the dbmail_messageblks_physmessage_is_header_idx has the same information.
Line 21: Line 21:
  
 But I got the tip from the pgsql-admin mailing list, to just restart all db connections (aka restart postgresql *and* dbmail), and this worked. No problem arises from deleting these indices. But I got the tip from the pgsql-admin mailing list, to just restart all db connections (aka restart postgresql *and* dbmail), and this worked. No problem arises from deleting these indices.
 +
 +=== CREATE INDEX for speedup ===
 +
 +  CREATE INDEX zmi_messages_clusterindex ON dbmail_messages (mailbox_idnr, message_idnr);
 +
 +This dramatically speeds up new message delivery.
  
 ==== Optimizing tables with CLUSTER ==== ==== Optimizing tables with CLUSTER ====
Line 28: Line 34:
 === CLUSTER commands for PostgreSQL 8.1, part 1 === === CLUSTER commands for PostgreSQL 8.1, part 1 ===
  
 +  /* for the first statement, you must have created the index as described above */
 +  CLUSTER zmi_messages_clusterindex ON dbmail_messages ;
 +  
   CLUSTER dbmail_mailboxes_owner_name_idx ON dbmail_mailboxes ;   CLUSTER dbmail_mailboxes_owner_name_idx ON dbmail_mailboxes ;
   CLUSTER dbmail_headername_1 ON dbmail_headername ;   CLUSTER dbmail_headername_1 ON dbmail_headername ;
-  CLUSTER dbmail_messages_7 ON dbmail_messages ; 
   CLUSTER dbmail_physmessage_pkey ON dbmail_physmessage ;   CLUSTER dbmail_physmessage_pkey ON dbmail_physmessage ;
   CLUSTER dbmail_aliases_domain_alias ON dbmail_aliases ;   CLUSTER dbmail_aliases_domain_alias ON dbmail_aliases ;
Line 45: Line 53:
  
   # Attention - these two take rather long! Make it during no accesses, as it blocks the table!   # Attention - these two take rather long! Make it during no accesses, as it blocks the table!
-  CREATE UNIQUE INDEX dbmail_messageblks_1 ON dbmail_messageblks (physmessage_id, messageblk_idnr); +  CREATE UNIQUE INDEX zmi_messageblks ON dbmail_messageblks (physmessage_id, messageblk_idnr); 
-  CLUSTER dbmail_messageblks_physmessage_is_header_idx ON dbmail_messageblks ;+  CLUSTER zmi_messageblks ON dbmail_messageblks ;
  
 === CLUSTER commands for PostgreSQL 8.3 === === CLUSTER commands for PostgreSQL 8.3 ===
Line 58: Line 66:
 So you need to change the formatting a little. It's just the parameter order that changed, the command still does the same. So you need to change the formatting a little. It's just the parameter order that changed, the command still does the same.
  
 +  /* for the first statement, you must have created the index as described above */
 +  CLUSTER dbmail_messages USING zmi_messages_clusterindex;
 +  
   CLUSTER dbmail_mailboxes USING dbmail_mailboxes_owner_name_idx;   CLUSTER dbmail_mailboxes USING dbmail_mailboxes_owner_name_idx;
   CLUSTER dbmail_headername USING dbmail_headername_1 ;   CLUSTER dbmail_headername USING dbmail_headername_1 ;
-  CLUSTER dbmail_messages USING dbmail_messages_7 ; 
   CLUSTER dbmail_physmessage USING dbmail_physmessage_pkey ;   CLUSTER dbmail_physmessage USING dbmail_physmessage_pkey ;
   CLUSTER dbmail_aliases USING dbmail_aliases_domain_alias ;   CLUSTER dbmail_aliases USING dbmail_aliases_domain_alias ;
Line 70: Line 80:
   CLUSTER dbmail_headervalue USING dbmail_headervalue_1 ;   CLUSTER dbmail_headervalue USING dbmail_headervalue_1 ;
   # Attention - this two take rather long! Make it during no accesses, as it blocks the table!   # Attention - this two take rather long! Make it during no accesses, as it blocks the table!
-  CREATE UNIQUE INDEX dbmail_messageblks_1 ON dbmail_messageblks (physmessage_id, messageblk_idnr); +  CREATE UNIQUE INDEX zmi_messageblks ON dbmail_messageblks (physmessage_id, messageblk_idnr); 
-  CLUSTER dbmail_messageblks USING dbmail_messageblks_physmessage_is_header_idx;+  CLUSTER dbmail_messageblks USING zmi_messageblks;
  
 ==== Optimizing backup time ==== ==== Optimizing backup time ====
 
performance.txt · Last modified: 2012/09/07 11:55 by zmi
 
DBMail is developed by Paul J Stevens together with developers world-wide