Increase MySQL performance for big lists

[maschoff]

OpenEMM user Thomas Niederreiter sent us a suggestion to increase the performance of MySQL by modifying this table:

Code: Select all

DROP TABLE IF EXISTS `onepixel_log_tbl`;
CREATE TABLE `onepixel_log_tbl` (
  `company_id` int(10) unsigned NOT NULL default '0',
  `mailing_id` int(10) unsigned NOT NULL default '0',
  `customer_id` int(10) unsigned NOT NULL default '0',
  `open_count` int(10) unsigned NOT NULL default '1',
  `change_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `ip_adr` varchar(15) collate utf8_unicode_ci NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

This table registers mail openings. Every time an update is executed on this table the DBMS load is heavy, the table is locked and all following updates have to wait which decreases performance.

His suggestion: Put an index on the combination of company_id, mailing_id and customer_id and change the MySQL engine from MyISAM to InnoDB to allow row-based locking.

We would be interested if someone else with big lists tried this and can report a performance increase. We then might put the changes into the mainline. Thank you!

[emmulator]

Has anyone tried replacing the table engine for this or other tables? We've had to bump up the application connection pool and max_connections for MySQL because of all the table locking on this onepixel table. We're seeing more than 100 connections waiting on this table.

I was also thinking of just eliminating the attempt to update the table and always go straight for the insert. We don't actually care how many times someone loads the pixel, only whether they ever load it at all. So we could just skip the updates and let any duplicate inserts error out, thereby cutting down the overall number of operations on this table by quite a bit.

[Beck]

we are currently using openemm with over 3 million recipients and up to two M recipients per Mailing.

After sending a mailing with so many recipients, we had extrem performance problems with the open- and clicktracking.

So our first step was to change the engine of onepixel_log_tbl to innodb and defining and index on it.

Code: Select all

CREATE TABLE `onepixel_log_tbl` (
  `company_id` int(10) unsigned NOT NULL default '0',
  `mailing_id` int(10) unsigned NOT NULL default '0',
  `customer_id` int(10) unsigned NOT NULL default '0',
  `open_count` int(10) unsigned NOT NULL default '1',
  `change_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `ip_adr` varchar(15) collate utf8_unicode_ci NOT NULL default '',
  KEY `clickIdx` (`company_id`,`customer_id`,`mailing_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

You can change it online with the following SQL Commands:

Code: Select all

ALTER TABLE customer_1_binding_tbl type = InnoDB;
CREATE INDEX clickIdx ON onepixel_log_tbl (company_id, customer_id, mailing_id);

After that, there were no performance issues on the Database anymore.
But soon we got extreme problems with the webservers, causing them to be not responding when many user open the mailing concurrently.

So i tuned the resin a litte bit and increased the max-threads in conf/core.conf

Code: Select all

        <thread-pool>
            <thread-max>500</thread-max>
        </thread-pool>

Now the Performance of the webservers simply rocks

If you have any questions to our setup, just ask and i try to tell you as much as i can [/code]

[emmulator]

Thanks!

It's funny that you should respond when you did -- we had just this week decided to build that same index. We haven't changed the table engine yet, just added the index. The open connections immediately dropped from 130 to 13 and have stayed low. And whereas the mysql process was the top process before, now the OpenEMM Java process spends a lot more time in that top spot, indicating that it is actually able to do more work now.

I highly recommend that the OpenEMM team add this index to the default configuration.

I'll check out that thread configuration too.

[maschoff]

Thanks for your feedback - we will consider your changes for the next release of OpenEMM!

[emmulator]

Beck, ma, anyone:

Is there any reason not to switch all the tables over to InnoDB?

One reason I ask is that we have some cleanup jobs to delete old data from the system, and these are causing some contention.

Since we keep running into performance issues with table locking, I'm wondering if we're not better off just switching the whole database over to the alternative storage engine?

Thanks!

[maschoff]

Actually, we never tried a DB engine other than MyISAM. However, we would be glad to get reports from users who tried it and tell us about their experience. MyISAM is the default DB engine of MySQL and - normally - very fast. This is the only reason why we have choosen it.

[Beck]

We have only changed a few tables to innodb.

These are:
customer_1_binding_tbl
customer_1_tbl
customer_1_tbl_seq
mailtrack_tbl
onepixel_log_tbl

For the customer_1* tables it was necessary due to lock problems with our automatic recipient management.

On the mailtrack_tbl and onepixel_log_tbl we got a better performance and easier handling.

These are the Changes we made so far, don´t know if its better to change all tables - for know the database performs quite well, but i will set additional indices to boost the speed of the interface.

[emmulator]

Thanks for the replies. I'll let you know if I have anything of interest to report back. We'll probably start by converting the same tables as Beck.

[maschoff]

For big databases we also recommend an index on table rdir_log_tbl for the combination of mailing_id, url_id and customer_id:

Code: Select all

CREATE INDEX rlog$mlid_urlid_cuid$idx ON rdir_log_tbl (mailing_id, url_id, customer_id);