1

I have a pain point with a relatively large application concerning 'tally tables', which are used for keeping track of the number of items against 'campaigns' (placed by a client), and 'publishers' / 'sites' / 'paths' (a site can have multiple paths, and vice versa, paths can belong to multiple sites; sites and paths belong to a publisher) and tracking campaigns (traffic sources - eg. Facebook, ads, etc).

The table uses a UNIQUE constraint across d_tally, campaignid, publisherid, pathid, siteid, device and tracking_campaignid, allowing for the use of INSERTON DUPLICATE KEY UPDATE queries for writing stats.

The table contains an AUTO_INCREMENT Primary Key 'tallyid' to allow the entities being tallied to record the tally they were stored against, which saves recreating the unique constraint in multiple places in the code as well as easier verification that the tally tables match the lead_campaign table. This could potentially be removed, but would require rewriting and testing of code that relies upon it.

Most of the remaining fields in the table are simply counts, which are generally SUM()'d.

A new row is created for each hour (we have to deal with timezones, but fortunately only ones which are an exact hour off UTC - there's a possibility this might change in the long term, but I can deal with that issue once I've fixed the current issues).

The table is currently generating ~10 million rows per month.

Table schema:

CREATE TABLE `campaign_tally_sources` (
`tallyid` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`dt_created` DATETIME NOT NULL,
`d_tally` DATETIME NOT NULL,
`campaignid` INT(10) UNSIGNED NOT NULL,
`publisherid` INT(10) UNSIGNED NOT NULL,
`pathid` INT(10) UNSIGNED NOT NULL,
`siteid` INT(10) UNSIGNED NOT NULL,
`device` ENUM('desktop','mobile','tablet','unknown') NOT NULL DEFAULT 'unknown' COLLATE 'utf8_unicode_ci',
`tracking_campaignid` BIGINT(20) UNSIGNED NOT NULL,
`invoiceid` INT(10) UNSIGNED NULL DEFAULT NULL,
`accepted` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`adjusted_accepted` INT(10) NOT NULL DEFAULT '0',
`rejected` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`adjusted_rejected` INT(10) NOT NULL DEFAULT '0',
`error` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`submission_accepted` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`adjusted_submission_accepted` INT(10) NOT NULL DEFAULT '0',
`submission_rejected` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`adjusted_submission_rejected` INT(10) NOT NULL DEFAULT '0',
`submission_error` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`cost` DECIMAL(10,4) UNSIGNED NOT NULL DEFAULT '0.0000',
`adjusted_cost` DECIMAL(10,4) NOT NULL DEFAULT '0.0000',
`commission` DECIMAL(10,4) UNSIGNED NOT NULL DEFAULT '0.0000',
`adjusted_commission` DECIMAL(10,4) NOT NULL DEFAULT '0.0000',
`impressions_positive` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`adjusted_impressions_positive` INT(10) NOT NULL DEFAULT '0',
`impressions_negative` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`adjusted_impressions_negative` INT(10) NOT NULL DEFAULT '0',
`reversals_rejected` INT(11) UNSIGNED NOT NULL DEFAULT '0',
`reversals_cost` DECIMAL(10,4) UNSIGNED NOT NULL DEFAULT '0.0000',
`reversals_commission` DECIMAL(10,4) UNSIGNED NOT NULL DEFAULT '0.0000',
`conversions` INT(10) UNSIGNED NOT NULL DEFAULT '0',
`reversals_conversions` INT(10) UNSIGNED NOT NULL DEFAULT '0',
PRIMARY KEY (`tallyid`),
UNIQUE INDEX `tally_tracking` (`d_tally`, `campaignid`, `pathid`, `siteid`, `tracking_campaignid`, `publisherid`, `device`),
INDEX `dt_created` (`dt_created`),
INDEX `campaignid` (`campaignid`),
INDEX `d_tally` (`d_tally`),
INDEX `pathid` (`pathid`),
INDEX `siteid` (`siteid`),
INDEX `tracking_campaignid` (`tracking_campaignid`),
INDEX `invoiceid` (`invoiceid`),
INDEX `device` (`device`),
INDEX `publisherid` (`publisherid`)
) COLLATE='utf8_unicode_ci' ENGINE=InnoDB;

Example of a common query (obtain all stats for the specified period for a specific publisher, by site):

SELECT `campaign_tally_sources`.siteid, `publisher_sites`.`parentid`, `campaigns`.currency,
    SUM(`accepted`) AS `accepted`,
    (SUM(`rejected`) - SUM(`impressions_negative`)) AS `rejected`,
    SUM(`error`) AS `error`,
    SUM(`submission_accepted`) AS `submission_accepted`,
    SUM(`submission_rejected`) AS `submission_rejected`,
    SUM(`submission_error`) AS `submission_error`,
    SUM(`campaign_tally_sources`.`cost`) AS `cost`,
    SUM(`campaign_tally_sources`.`commission`) AS `commission`,
    SUM(`impressions_positive`) AS `impressions_positive`,
    SUM(`impressions_negative`) AS `impressions_negative`,
    SUM(`adjusted_accepted`) AS `adjusted_accepted`,
    (SUM(`adjusted_rejected`) - SUM(`adjusted_impressions_negative`)) AS `adjusted_rejected`,
    SUM(`adjusted_submission_accepted`) AS `adjusted_submission_accepted`,
    SUM(`adjusted_submission_rejected`) AS `adjusted_submission_rejected`,
    SUM(`campaign_tally_sources`.`adjusted_cost`) AS `adjusted_cost`,
    SUM(`campaign_tally_sources`.`adjusted_commission`) AS `adjusted_commission`,
    SUM(`adjusted_impressions_positive`) AS `adjusted_impressions_positive`,
    SUM(`adjusted_impressions_negative`) AS `adjusted_impressions_negative`,
    SUM(`reversals_cost`) AS `reversals_cost`,
    SUM(`reversals_commission`) AS `reversals_commission`,
    SUM(`reversals_rejected`) AS `reversals_rejected`
FROM campaign_tally_sources
LEFT JOIN campaigns ON `campaign_tally_sources`.campaignid = campaigns.campaignid
LEFT JOIN `publisher_sites` ON `campaign_tally_sources`.`siteid` = `publisher_sites`.`siteid`
WHERE d_tally BETWEEN '2015-07-01 00:00:00' AND '2015-07-06 23:59:59'
    AND `publisherid` = '1'
GROUP BY siteid, currency;

Query result summary: 308 rows in set (2 min 0.38 sec)

EXPLAIN for the above query:

+----+-------------+------------------------+--------+------------------------------------+-------------+---------+------------------------------------------------+---------+----------------------------------------------+
| id | select_type | table                  | type   | possible_keys                      | key         | key_len | ref                                            | rows    | Extra                                        |
+----+-------------+------------------------+--------+------------------------------------+-------------+---------+------------------------------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | campaign_tally_sources | ref    | tally_tracking,d_tally,publisherid | publisherid | 4       | const                                          | 8495801 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | campaigns              | eq_ref | PRIMARY                            | PRIMARY     | 4       | dbname.campaign_tally_sources.campaignid       |       1 |                                              |
|  1 | SIMPLE      | publisher_sites        | eq_ref | PRIMARY                            | PRIMARY     | 8       | dbname.campaign_tally_sources.siteid           |       1 |                                              |
+----+-------------+------------------------+--------+------------------------------------+-------------+---------+------------------------------------------------+---------+----------------------------------------------+

I already tried putting the currency value (used in the GROUP BY) in to the campaign_tally_sources table directly, rather than referencing the campaigns table, but this appeared to have no impact on the query performance.

My main queries are:

  • How might the indexes be improved?
  • Given that we currently only have 1 publisherid in the table (this may change in the future, hence why it hasn’t been removed), why does MySQL select this as the key to filter by, and not either d_tally or tally_tracking? (Even if I remove the publisherid key or both the key and WHERE criteria for publisherid, it doesn’t use any other key)
  • Would partitioning help speed up queries on this table significantly? Is there a general rule to follow for the point at which partitioning will help?
  • Are there better alternative database engines / database servers for handling these type of statistics / tallys?

Updated information:

I was fairly sure I had already run it relatively recently, but I ran ANALYZE again anyway

Indexes for campaign_tally_sources:

+------------------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                  | Non_unique | Key_name            | Seq_in_index | Column_name         | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| campaign_tally_sources |          0 | PRIMARY             |            1 | tallyid             | A         |    17174185 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          0 | tally_tracking      |            1 | d_tally             | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          0 | tally_tracking      |            2 | campaignid          | A         |      715591 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          0 | tally_tracking      |            3 | pathid              | A         |     1561289 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          0 | tally_tracking      |            4 | siteid              | A         |    17174185 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          0 | tally_tracking      |            5 | tracking_campaignid | A         |    17174185 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          0 | tally_tracking      |            6 | publisherid         | A         |    17174185 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          0 | tally_tracking      |            7 | device              | A         |    17174185 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          1 | dt_created          |            1 | dt_created          | A         |    17174185 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          1 | campaignid          |            1 | campaignid          | A         |        4790 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          1 | d_tally             |            1 | d_tally             | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          1 | pathid              |            1 | pathid              | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          1 | siteid              |            1 | siteid              | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          1 | tracking_campaignid |            1 | tracking_campaignid | A         |       68151 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          1 | invoiceid           |            1 | invoiceid           | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |               |
| campaign_tally_sources |          1 | device              |            1 | device              | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
| campaign_tally_sources |          1 | publisherid         |            1 | publisherid         | A         |          18 |     NULL | NULL   |      | BTREE      |         |               |
+------------------------+------------+---------------------+--------------+---------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

EXPLAIN for the above query:

+----+-------------+------------------------+--------+------------------------------------+-------------+---------+------------------------------------------------+---------+----------------------------------------------+
| id | select_type | table                  | type   | possible_keys                      | key         | key_len | ref                                            | rows    | Extra                                        |
+----+-------------+------------------------+--------+------------------------------------+-------------+---------+------------------------------------------------+---------+----------------------------------------------+
|  1 | SIMPLE      | campaign_tally_sources | ref    | tally_tracking,d_tally,publisherid | publisherid | 4       | const                                          | 8587260 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | campaigns              | eq_ref | PRIMARY                            | PRIMARY     | 4       | coreglead_v2.campaign_tally_sources.campaignid |       1 |                                              |
|  1 | SIMPLE      | publisher_sites        | eq_ref | PRIMARY                            | PRIMARY     | 8       | coreglead_v2.campaign_tally_sources.siteid     |       1 |                                              |
+----+-------------+------------------------+--------+------------------------------------+-------------+---------+------------------------------------------------+---------+----------------------------------------------+

There's very little change in the results. My guess is that the sampling method InnoDB uses (from what I've read) means it's guessing that the cardinality of publisherid is the same as d_tally when this isn't the case, but I have no idea if there's a method I can use to influence MySQL in its decisions on this.

Result of query: select count(distinct publisherid), count(distinct d_tally), count(1) total from campaign_tally_sources;

  • count(distinct publisherid) = 4 (As already mentioned, we basically only have 1 publisher at the moment. The other 3 items are test publishers with an extremely low occurance rate)
  • count(distinct d_tally) = 3,233 (this correlates with the 4 and a bit months the product has been live)
  • total = 17,267,806
5
  • 1
    The plan is bad, index on d_tally should be used unless too many records are in that timespan from BETWEEN. Can you add results of "show index from campaign_tally_sources;" ? It should not matter for InnoDB but maybe your statistics are really bad. Partitioning will not usually help you much with performance and has too many drawbacks imho. You might use index on (publisherid, siteid, currency, d_tally) if you move currency to this table, but thats a hack so try other means.
    – jkavalik
    Aug 11, 2015 at 12:12
  • 1
    The indexes overview says that cardinality of d_tally is 18 - "same" as publisherid - those are not exact numbers but still it is really low for a datetime column. Is it possible that you have so many duplicates (so few unique/distinct values) in that column? If not, try running "analyze table campaign_tally_sources;" and see if it changes to something more reasonable. (I would guess it will be similar to dt_created unless it is supposed to have many duplicates)
    – jkavalik
    Aug 11, 2015 at 12:50
  • 1
    You can try running the query with use index (d_tally) added after FROM campaign_tally_sources to see how explain and time changes. But it seems like d_tally really has so many duplicates. You can verify the numbers with select count(distinct publisherid), count(distinct d_tally), count(1) total from campaign_tally_sources;. If the numbers are bad then without moving currency I can only suggest trying with index over (publisherid, d_tally). I am not a big fan of partitioning, but maybe using separate partitions for each publisherid might help in the future, but not with this yet.
    – jkavalik
    Aug 12, 2015 at 9:04
  • Can you check SELECT count(1), count(distinct d_tally) FROM campaign_tally_sources WHERE d_tally BETWEEN '2015-07-01 00:00:00' AND '2015-07-06 23:59:59' = how many rows are read to produce those 308 rows of results? Sorry for this "way" of problem solving, I am trying to understand the structure. You might try to set innodb_stats_sample_pages to bigger value to see if cardinality and estimates after "analyze table" become better but I do not have very good experience with it..
    – jkavalik
    Aug 12, 2015 at 9:59
  • It seems to me that moving currency and adding index (publisherid, siteid, currency, d_tally) (the inclusion of d_tally has meaning only if your mysql version can do checks on index condition ) might be the right solution - that way the GROUP BY can be executed without temporary and filesort. But you will still have to read and sum many rows from the table so the query will probably never be very fast.
    – jkavalik
    Aug 12, 2015 at 10:05

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.