0011499: Fix for huge slowdown in e-mail queueing with MySql - MantisBT

MantisBT

View Issue Details [ Jump to Notes ] [ Wiki ]

[ Issue History ] [ Print ]

ID

Project

Category

View Status

Date Submitted

Last Update

0011499

mantisbt

performance

public

2010-02-13 00:29

2011-04-27 04:30

Reporter

dylanc

Assigned To

Priority

urgent

Severity

major

Reproducibility

always

Status

acknowledged

Resolution

open

Platform

OS

OS Version

Product Version

1.1.8

Target Version

1.3.x

Fixed in Version

Summary

0011499: Fix for huge slowdown in e-mail queueing with MySql

Description

Change the field types for metadata and body in the e-mail database table to not be longtext, and switch them to mediumtext (still allows for 16 million bytes of data!)

This is a problem in the schema preparation - the email table is defined as XL and this translates to longtext under mysql - looking around on the net shows that people never use longtext in a regular table (it allows for up to 4 terabytes of data and is inefficient if used like a normal field).

The slowdowns aren't visible if using the InnoDB backend for MySql which is why reports of this slowdown are scattered, but even then I'd suggest changing the longtext to mediumtext for efficiency.

This field change should be added to the database upgrade code too.

Steps To Reproduce

update any bug when using MySql 4.x (haven't tried 5.x) and with email notifications on that sends 2-3 notifications. On a modern fast server this can take 30 seconds to a minute for the database update to complete. (the bug is updated immediately - it takes all that time solely to write out the e-mail table)

Additional Information

I was seeing a huge slowdown in the e-mailing notifications performance, and looking on the forums I saw scattered reports of other people hitting the same (30 seconds or more on an update) slowdown. The common response was to switch on the cronjob batching but this actually made no difference because the problem is in the database storage for the batching system itself.

So hopefully this will fix this obscure problem for a lot of people.

Tags

No tags attached.

Relationships

Notes
(0024704) runner80 (reporter) 2010-03-12 03:22	Thanks for this hint. We also had this problem and never thought that the e-mail table caused it. Now everything runs fine!

(0028460) carol2000 (reporter) 2011-03-23 02:42	Thanks, I already changed mantis_email_table->metadata/body from longtext to mediumtext. After restart mysql, unfortunately it's still very slow when update/assign issue. My enviroment: XAMPP+Mantis1.2.4 + Apache 2.2.17 + MySQL 5.5.8 (Community Server) + PHP 5.3.5 (VC6 X86 32bit) + PEAR + XAMPP Control Version 2.5 from www.nat32.com + XAMPP Security + phpMyAdmin 3.3.9

(0028699) sansan (reporter) 2011-04-27 04:30 edited on: 2011-04-27 10:32	We're also having huge slowdowns when updating/assigning a bug to another user. We've tested the proposed fix (changing tables) but it didn't change a thing. - MySQL: 5.0.67 (5.0.67-0ubuntu6.1) - Storage Engine: MyISAM - Apache: 2.2.9 (Ubuntu) - PHP: 5.2.6 (5.2.6-2ubuntu4.6 with Suhosin-Patch mod_ssl/2.2.9 OpenSSL/0.9.8g) [UPDATE] After we changed the "$g_phpMailer_method" variable in "config_local.php" things were up to speed again. We had it on 2 (external SMTP server) and when we changed it to 0 (mail()) and then back to 2 it was working at velocities multiple magnitudes higher than the speed of light.. well... it very fast once again. But why it worked? It could be a slow DNS resolve (we have $g_smtp_host asigned a hostname) but that is speculation on our part.

Issue History
Date Modified	Username	Field	Change
2010-02-13 00:29	dylanc	New Issue
2010-03-12 03:22	runner80	Note Added: 0024704
2010-03-12 09:52	dhx	Status	new => acknowledged
2010-03-12 09:52	dhx	Target Version	=> 1.3.x
2011-03-23 02:42	carol2000	Note Added: 0028460
2011-04-27 04:30	sansan	Note Added: 0028699
2011-04-27 04:31	sansan	Note Edited: 0028699	View Revisions
2011-04-27 10:32	sansan	Note Edited: 0028699	View Revisions
2011-04-27 10:32	sansan	Note Edited: 0028699	View Revisions

MantisBT 1.2.16dev master-1.2.x-8c2bd07 [^]

Copyright © 2000 - 2013 MantisBT Team

Time: 0.0669 seconds.
memory usage: 2,792 KB