|View Issue Details|
|ID||Project||Category||View Status||Date Submitted||Last Update|
|0011499||mantisbt||performance||public||2010-02-13 00:29||2014-06-11 14:51|
|Status||closed||Resolution||no change required|
|Target Version||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.|
|Thanks for this hint. We also had this problem and never thought that the e-mail table caused it. Now everything runs fine!|
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
Last edited: 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)
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.
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.
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)
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
|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|