2014-11-24 16:18 EST

View Issue Details Jump to Notes ] Wiki ]
IDProjectCategoryView StatusLast Update
0011499mantisbtperformancepublic2014-06-11 14:51
Reporterdylanc 
Assigned Tograngeway 
PriorityurgentSeveritymajorReproducibilityalways
StatusclosedResolutionno change required 
Product Version1.1.8 
Target VersionFixed in Version 
Summary0011499: Fix for huge slowdown in e-mail queueing with MySql
DescriptionChange 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 Reproduceupdate 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 InformationI 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.
TagsNo tags attached.
Attached Files

- Relationships
+ Relationships

-  Notes
User avatar

~0024704

runner80 (reporter)

Thanks for this hint. We also had this problem and never thought that the e-mail table caused it. Now everything runs fine!
User avatar

~0028460

carol2000 (reporter)

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
User avatar

~0028699

sansan (reporter)

Last edited: 2011-04-27 10:32

View 4 revisions

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.

User avatar

~0040649

grangeway (reporter)

Notes from Dylan:
"Wow, that’s a blast from the past! The changes I made fixed the huge slow-down I was seeing.

Perhaps on 64-bit machines a LONGTEST field is fast? All I know is that on our servers which were old P4s at the time using the field type absolutely destroyed performance, and as I mention in the notes that particular data type isn’t used in normal databases (at least that was the sentiment at the time I recall) because well, it’s overkill. 
It might also be simply a performance problem in MySQL 4.x and isn’t worth fixing now.

I tried all the other solutions suggested for speeding up the mailing (batching etc) and none of them were relevant to the problem I was having, although they do fix a lot of smaller speed problems. Basically the “huge speed down” I was seeing was *Really* slow, as in minutes to send a single mail. Perhaps there was disk-swapping going on or simply a bug in mySQL, I really don’t know, all I know is that switching the type sped it all back up again to sane speeds.

Also, I seem to recall that looking at the types of fields the XL type maps to for other database solutions showed a discrepancy. XL -> LONGTEXT simply seemed to be an incorrect over-sized mapping to me.
"
User avatar

~0040650

grangeway (reporter)

Marking as no change required - my email to dev list with current thoughts for if this comes up again, or we can reproduce below:

There are indeed reports on the internet of similar behaviour - e.g.
http://forums.mysql.com/read.php?10,378680,378795#msg-378795 [^] . However, the reply I link to from Rick James (I think I recognise that name from being associated with mysql in some way or at least a helpful user)

Looking at

http://nicj.net/mysql-text-vs-varchar-performance/ [^] and http://dev.mysql.com/doc/refman/5.7/en/internal-temporary-tables.html [^]

If I were to hazard a guess, I'd probably be inclined to blame the use of 'order by' in some version of mysql.

Personally, I think I'm inclined to leave as is for now, and will close off this bug report. I'm inclined to modify email_queue_get_ids to always return ID's in the order of creation (we want emails to leave the mysql server in the order the notifications were generated - I'll submit a PR for this, so there's no need to ever do a reverse sort).

If we were to take a stab at avoiding a potential issue here, I'd be inclined to remove the ORDER BY clause completely, on the basis that the default expected state of the email table is to be empty at all times - when it's non-empty i.e. emails waiting to be processed, we always process all emails in one go assuming at working mail server. However, given the probability of a mail server breaking, and the fact that we want the ID's in order, and without the order by clause the order is undefined, I'm not going to remove the clause
+  Notes

- 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
2014-05-27 04:35 grangeway Target Version 1.3.x =>
2014-05-27 04:37 grangeway Note Added: 0040649
2014-05-27 04:37 grangeway Note Added: 0040650
2014-05-27 04:37 grangeway Status acknowledged => resolved
2014-05-27 04:37 grangeway Resolution open => no change required
2014-05-27 04:37 grangeway Assigned To => grangeway
2014-06-11 14:51 atrol Status resolved => closed
+ Issue History