SQL query optimaliseren voor autoresponder

[juggle]

Beste Tweakers,

Voor een autoresponder systeem heb ik de volgende tabellen (deze heb ik iets versimpeld en alleen de relevante kolommen meegenomen voor het overzicht. De autoresponder stuurt automatisch e-mail berichten naar inschrijvingen op basis van de inschrijfdatum (start_responder). Per minuut haal ik met 1 query alle berichten op uit een queue tabel waarin alle berichten staan gequeued voor alle autoresponders. Na een succesvolle verzending verwijder ik deze uit de queue tabel en worden deze elders opgeslagen als verzonden.

De query is echter behoorlijk traag (meer dan 6 seconden) en gebruikt meer dan 100% cpu. Ik kom er echter maar niet uit hoe ik deze query ofwel kan opbreken in meerdere simpele queries, danwel optimaliseren. Hieronder volgt de tabellen structuur en de query die ik daarop loslaat:

autoresponder (+/- 1500 records)
Bevat alle autoresponders

autoresponderid

listid

title

created

modified

PRIMARY: autoresponderid


autoresponder_message (+/- 5000 records)
Bevat alle berichten die onder een autoresponder vallen. Een autoresponder is dus een verzameling (sequence) van een aantal berichten die opeenvolgend worden verzonden. In de followup kolom staat een interval in minuten. (Bijvoorbeeld, verzend na 15 minuten = 15, verzend na 24 uur = 1440)

messageid

autoresponderid

title

body

followup

PRIMARY: messageid
INDEX: autoresponderid

autoresponder_queue (+/- 370.000 records)
Hierin staan de contacten (inschrijvingen) gelinked aan de autoresponder berichten. Op het moment dat een contact zich inschrijft op een bepaalde mailinglijst worden de id's van autoresponder berichten in deze tabel toegevoegd samen met het contactid.

queueid

autoresponderid

messageid

contactid

PRIMARY: queueid
INDEX: (autoresponderid,messageid,contactid)
UNIQUE (messageid,contactid) duplicates

contacts (+/- 2.000.000 records)
Hierin staan de contacten (inschrijvingen). De kolom start_responder bevat een timestamp waarop de autoresponder bepaalt welke berichten moeten worden verzonden. Meestal is dit de inschrijfdatum.

contactid

listid

subscribed

name

email

start_responder

PRIMARY contactid
INDEX (listid,subscribed,start_responder) AS autoresponder

In mysql laat ik de volgende query hierop los:

SQL:

SELECT
                            q.*,
                            c.email AS contact_email,
                            c.name AS contact_name,
                            m.messageid,
                            m.subject,
                            m.body
                        FROM
                           autoresponder_queue AS q,
                           contacts AS c,
                           autoresponder_message AS m
                        WHERE
                            q.messageid = m.messageid
                        AND
                            q.contactid = c.contactid
                        AND
                            c.subscribed = 1
                        AND
                            c.start_responder < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL m.followup MINUTE), '%Y%m%d%H%i%s')

Een explain van de query krijg ik de volgende resultaten:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	extra
1	SIMPLE	q	ALL	duplicates	NULL	NULL	NULL	368747	Using where
1	SIMPLE	m	eq_ref	PRIMARY	PRIMARY	8	database.q.messageid	1
1	SIMPLE	c	eq_ref	PRIMARY	PRIMARY	4	database.q.contactid	1	Using where

De query levert ongeveer tussen 2 en 10 resultaten per minuut op. Op zich logisch want er hoeven niet heelveel berichten steeds verstuurd te worden.

In de explain zie je echter dat hij de indexes niet gebruikt. Wanneer ik force index gebruikt performed de query hetzelfde. Het probleem zit hem met name in dat hij de volledie queue tabel scant op berichten. Op zich geen probleem zou je zeggen, maar dit proces neemt veel tijd in beslag.

Hebben jullie enig idee hoe ik deze procedure kan versimpelen?

[Speedener]

Volgens mij heeft een index op de tabel contacts op de volgende kolommen, in de volgende volgorde heel veel zin:

INDEX (contactid,subscribed,start_responder) AS autoresponder

Veel meer zin dan de huidige key, die nu niet gebruikt kan worden in de beschreven query, omdat listid niet gebruikt wordt in de query en die kolom vooraan de index staan.

Edit:

En een index op de queue tabel zal ook zin hebben op de kolommen:
INDEX (contactid,messageid)

[ Voor 13% gewijzigd door Speedener op 01-04-2014 11:46 ]

[juggle]

Bedankt voor je reply,

Ik denk niet dat jou voorgestelde indexes zin hebben. Hij moet dan nog steeds een volledige table scan doen op de contacts tabel met de resultaten uit de queue tabel.

Om te testen heb ik de listid toegevoeged aan de query. Hij stelt nu wel voor om de autoresponder key te gebruiken maar pakt nog steeds de primary key als index.

Daarnaast staat er een key op (messageid,contactid) duplicates. Die was ik vergeten te vermelden. Deze heb ik er bijgezet.

Ik zelf denk meer te zoeken om de resultaten uit de autoresponder_queue tabel te zien verminderen. Hij scant nu all records. Dit zou best verminderd kunnen worden naar alleen de berichten die vooraan in de sequence zitten.

[ Voor 20% gewijzigd door juggle op 01-04-2014 12:29 ]

[Paul]

Start-responder is een datum of bevat het ook tijd?

Je zou een boolean kolom toe kunnen voegen 'is_after_start' en

SQL:

c.start_responder < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL m.followup MINUTE), '%Y%m%d%H%i%s')

kunnen vervangen door een check op c.is_after_start

Dal schedule je 1x per dag (op het rustigste moment) een

SQL:

UPDATE contacts
    SET is_after_start = TRUE
WHERE
    start_responder < DATE_FORMAT(DATE_SUB(NOW(), INTERVAL m.followup MINUTE), '%Y%m%d%H%i%s'

*code uit de losse pols

Al weet ik dus niet hoe duur die vergelijking op datum is... Welke SQL-server betreft het? Op MS SQL kun je het execution plan opvragen, dan zie je precies op welk onderdeel hij zo lang bezig is.

[ Voor 15% gewijzigd door Paul op 01-04-2014 13:02 ]

[pedorus]

autoresponder_queue is nu niet echt een queue natuurlijk. Wat weerhoudt je ervan om een kolom when_to_send met index daarop te maken? Dit geeft een wat minder genormaliseerd model natuurlijk, maar dat stukje lijkt sowieso al wat ongenormaliseerd, en qua performance scheelt dit waarschijnlijk zeer veel.

[juggle]

Hoi Paul,

Ik draai op mysql 5.5.27. De start_responder bevat een timestamp in de vorm: YYYYMMDDHHIISS. De is_after_start wordt te bewerkelijk ben ik bang.

[juggle]

Hoi Pedorus,

Daar zat ik inderdaad ook aan te denken, de followup zou dan kunnen verschuiven naar de autoresponder_queue tabel. Toch is dit niet geheel wenselijk, want, de responder_start kan aangepast worden. Daarnaast kunnen ook berichten verschoven worden qua interval waardoor je dus steeds de followup moet aanpassen in de queue tabel. De queue tabel heet meer een queue omdat er een cronjob loopt die in keer in de minuut in deze tabel kijkt welke berichten er staan "gequeued" voor een bepaald contact.

[OPLOSSING]

Het blijkt dat als je een Mysql functie gebruikt in een where statement, Mysql geen indexes gebruikt. In dit geval gaat Mysql de functie DATE_SUB(NOW(), INTERVAL followup MINUTE) voor elke rij uitvoeren en controleren op de database.

Ik heb het nu deels opgelost door gebruik te maken van derived tables. Dus eerst een query op de messages table om followup te converteren naar een timestamp. En dan vergolgens op basis van de timestamp de inschrijvingen ophalen:

SQL:

SELECT
                q.*,
                c.email AS contact_email,
                c.name AS contact_name,
                c.start_responder,
                m.subject,
                m.body,
                m.plaintext,
                m.fromname,
                m.fromemail,
                m.replyto,
                m.send_responder
            FROM
               autoresponder_queue AS q,
               (
                SELECT 
                    messageid,
                    uniqueid,
                    format,
                    subject,
                    body,
                    plaintext,
                    fromname,
                    fromemail,
                    replyto,
                    DATE_FORMAT(DATE_SUB(NOW(), INTERVAL followup MINUTE), '%Y%m%d%H%i%s') AS send_responder
                FROM
                    autoresponder_message
               ) as m,
               contacts AS c
            WHERE
                q.messageid = m.messageid
            AND
                q.contactid = c.contactid
            AND
                c.subscribed = 1
            AND
                c.start_responder < send_responder

Bovenstaande query heeft de performance met 50% verbeterd en de uitvoertijd gehalveerd. De query draait nu net 3.5 seconden en tikt even 70% cpu aan. Voor dit soort queries volgens mij best acceptabel.

[ Voor 65% gewijzigd door juggle op 02-04-2014 09:18 ]