Opzet MySQL db OK en query aanpassen

[MerijnB]

Ik heb een server waar clients verbinding kunnen doen (een "session"), in een zo'n session doet een client meerdere aanvragen ("requests"). Een request is uniek door de combinatie van verschillende informatie, en kan goed gaan of niet.

Deze akties wil ik graag loggen en hiervoor heb ik een MySQL database opgezet, die ziet er als volgt uit:

Voor elke sessie wordt het IP adres en de tijd dat deze sessie plaatsvond gelogd:

code:

CREATE TABLE IF NOT EXISTS `sessions` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `IP` char(15) NOT NULL,
  `Timestamp` datetime NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `Timestamp` (`Timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Elk request hoort bij een sessie, en logt de sessieinformatie + de failed flag:

code:

CREATE TABLE IF NOT EXISTS `requests` (
  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `SessionID` int(11) unsigned NOT NULL,
  `RequestInfo1` char(3) NOT NULL,
  `RequestInfo2` char(5) NOT NULL,
  `Failed` enum('Y','N') NOT NULL,
  PRIMARY KEY (`ID`),
  KEY `Missing` (`Failed`)
  KEY `FK_SessionID` (`SessionID`),
  CONSTRAINT `FK_SessionID` FOREIGN KEY (`SessionID`) REFERENCES `sessions` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

In de database staat wat test data, waardoor het geheel er zo uit ziet:

Sessions:

code:

| ID |      IP |            Timestamp |
|----|---------|----------------------|
|  1 | 1.2.3.4 | 2018-01-10T12:00:00Z |
|  2 | 1.2.3.4 | 2018-01-11T12:00:00Z |
|  3 | 1.2.3.4 | 2018-01-12T12:00:00Z |

Requests:

code:

| ID | SessionID | RequestInfo1 | RequestInfo2 | Failed |
|----|-----------|--------------|--------------|--------|
|  1 |         1 |            1 |            A |      Y |
|  2 |         1 |            1 |            B |      N |
|  3 |         2 |            1 |            A |      Y |
|  4 |         2 |            2 |            A |      Y |
|  5 |         3 |            2 |            B |      Y |
|  6 |         3 |            2 |            A |      N |

Nu wil ik met een query graag zien welke unieke requests er in de afgelopen 7 dagen zijn mislukt, en hoe vaak, dat is gelukt met de volgende query:

code:

SELECT r.RequestInfo1,
       r.RequestInfo2,
       Count(*) AS Times
FROM   sessions s
       join requests r
         ON s.ID = r.SessionID
WHERE  r.Failed = 'Y'
       AND s.TimeStamp BETWEEN Date_sub(Curdate(), interval 7 day) AND
                               Date_add(Curdate(), interval 1 day)
GROUP  BY r.RequestInfo1,
          r.RequestInfo2
ORDER  BY Times DESC;

En daar komt dit uit:

code:

| RequestInfo1 | RequestInfo2 | Times |
|--------------|--------------|-------|
|            1 |            A |     2 |
|            2 |            A |     1 |
|            2 |            B |     1 |

So far so good.

Nu wil ik graag de query aanpassen, zodat ik alleen de items zie die de laatste keer dat ze requested waren op failed stonden. De combinatie 2 en A hoort daar niet bij, op 10 januari was die gefaild, maar op 12 januari niet. Dit lukt me niet.

Nu heb ik twee vragen:

- Is het ontwerp voor dit doel een beetje OK zo?
- Hoe kan ik de query aanpassen zodat ie doet wat hij moet doen?

Hier een SQL fiddle met de hele zwik.

[ Voor 4% gewijzigd door MerijnB op 12-01-2018 23:28 . Reden: vergeten foreign key erbij te zetten ]

[Verwijderd]

Dat gaat op deze manier sowieso niet lukken omdat je timestamp in de session zit en niet in de request...

Daarnaast zul je je resultaten moeten beperken op basis van een timestamp, en die zie ik in je query (in die context) niet terug.

Als je request dus een timestamp geeft kun je een stuk creatiever zijn.

[MerijnB]

Verwijderd schreef op vrijdag 12 januari 2018 @ 22:53:
Dat gaat op deze manier sowieso niet lukken omdat je timestamp in de session zit en niet in de request...

Aangezien de timestamp per definitie gelijk is voor alle requests binnen een sessie, en er potentieel best veel requests gelogd kunnen worden, heb ik er nu voor gekozen deze met de sessie mee te loggen.

Daarnaast zul je je resultaten moeten beperken op basis van een timestamp, en die zie ik in je query (in die context) niet terug.

Welke context bedoel je? Die van limiteren op de afgelopen week? Want dat zit weldegelijk in de query en werkt ook zoals bedoeld.

Als je request dus een timestamp geeft kun je een stuk creatiever zijn.

Dat had ik inderdaad ook bedacht, maar is dit perse nodig (aangezien dit redundante data is)?

edit:
Ik zie dat ik de foreign key tussen de twee tabellen vergeten was, heb ik er nu bijgezet.

[ Voor 5% gewijzigd door MerijnB op 12-01-2018 23:28 ]

[CurlyMo]

Wat het handigst is binnen MySQL is of een join op een inline view of een subquery in je where statement. Deze dient een lijst terug te geven van de laatste status van elke SessionID. Vervolgen check je of het SessionID wat je nu terugkrijgt daarmee overeenkomt.

In het algemeen:
1. Gebruik kleine letters
2. Als je dan toch InnoDB gebruikt, leg dan ook je relaties (foreign keys) vast.
3. auto_increment en NOT NULL is dubbel of je moet op een of andere manier handmatig een NULL invoeren in je ID kolom.
4. Waarom geen boolean of tinyint(1) voor je Failed kolom?
5. Het leest (voor mij) makkelijker als je een veld direct PRIMARY_KEY maakt i.p.v. dat aan het eind van je CREATE TABLE statement.
6. In RequestInfo1 staan alleen getallen, waarom dan toch een char type?
7. Waarom zit er in je Timestamp veld geen timestamp zoals de naam suggereert?

Voor je vraag of het verder een goed schema is moet je toch meer informatie geven. Als het bijvoorbeeld om een webserver gaat, dan zou ik gewoon gebruik maken van de apache logs.

Verder is de vraag of je de keuze hebt een andere database te gebruiken. Dat scheelt vaak al de helft. PostgreSQL is een mooie open source tegenhanger dan het klote MySQL. Dan kan zo'n query als deze zonder al te moeilijk te doen.

-------------------------

Als je nog geen Window functions hebt, dan zie je hier hoe je de laatste van een groep selecteert:
https://stackoverflow.com/a/1313293

[ Voor 6% gewijzigd door CurlyMo op 12-01-2018 23:49 ]

[MerijnB]

Bedankt voor je uitgebreide post, ik zal hieronder reageren

CurlyMo schreef op vrijdag 12 januari 2018 @ 23:35:
Wat het handigst is binnen MySQL is of een join op een inline view of een subquery in je where statement. Deze dient een lijst terug te geven van de laatste status van elke SessionID. Vervolgen check je of het SessionID wat je nu terugkrijgt daarmee overeenkomt.

Dit heb ik geprobeerd, maar ik heb denk ik niet genoeg kennis, kun je een voorzetje geven of een linkje met relevante info?

In het algemeen:
1. Gebruik kleine letters

Ben een SQL noob, had het nog door een pretty printer gehaald, die maakte er hoofdletters van

2. Als je dan toch InnoDB gebruikt, leg dan ook je relaties (foreign keys) vast.

Was ik vergeten, zie de edit van de 1e post.

3. auto_increment en NOT NULL is dubbel of je moet op een of andere manier handmatig een NULL invoeren in je ID kolom.

Thanks, dit is door gewoon gebrek aan kennis aan mijn kant.

4. Waarom geen boolean of tinyint(1) voor je Failed kolom?

Valid point, geen echte rede en kan ik prima veranderen; biedt dit voordelen? Waarom is dat beter dan deze enum?

5. Het leest (voor mij) makkelijker als je een veld direct PRIMARY_KEY maakt i.p.v. dat aan het eind van je CREATE TABLE statement.

De DDL is door HeidiSQL gegenereerd

6. In RequestInfo1 staan alleen getallen, waarom dan toch een char type?

De data is fictief en versimpeld om dit probleem te illustreren, de echte data ziet er anders uit (wel degelijk alle data zijn strings).

7. Waarom zit er in je Timestamp veld geen timestamp zoals de naam suggereert?

Gebrek aan kennis, is het beter (makkelijker) om een timestamp veld te gebruiken? Het gaat mij om het moment dat iets gebeurd is (datum + tijd), (uit gewoonte) noem ik dit een timestamp.

Voor je vraag of het verder een goed schema is moet je toch meer informatie geven. Als het bijvoorbeeld om een webserver gaat, dan zou ik gewoon gebruik maken van de apache logs.

Is geen webserver; de details zijn long, boring en niet relevant denk ik. De data hierboven beschreven is fictief om specifiek het probleem waar ik tegenaan loop te beschrijven.

Verder is de vraag of je de keuze hebt een andere database te gebruiken. Dat scheelt vaak al de helft. PostgreSQL is een mooie open source tegenhanger dan het klote MySQL. Dan kan zo'n query als deze zonder al te moeilijk te doen.

Dat is zeker een optie (iig om te proberen), waarom kan dit wel in bijv PostgreSQL en niet in MySQL (maw, wat voor soort query kan je wel doen in PostgreSQL en niet in MySQL om dit doel te bereiken)?

[Verwijderd]

MerijnB schreef op vrijdag 12 januari 2018 @ 23:19:
Aangezien de timestamp per definitie gelijk is voor alle requests binnen een sessie, en er potentieel best veel requests gelogd kunnen worden, heb ik er nu voor gekozen deze met de sessie mee te loggen.

Okay, prima - ik kon me voorstellen dat A2 meerdere keren binnen een sessie kan voorkomen, maar als dat niet zo is, dan kan die timestamp prima in de sessie blijven.

Welke context bedoel je? Die van limiteren op de afgelopen week? Want dat zit weldegelijk in de query en werkt ook zoals bedoeld.

Nee, die juist niet. De context waarin je onderscheid maakt tussen de gewenste resultaten en de ongewenste resultaten uit het verleden.

Waar je eens naar zou kunnen kijken is een subquery waarin je de hele mikmak op zichzelf gaat joinen - dat zou makkelijker zijn als request zijn eigen timestamp heeft, maar niet onoverkomelijk. Eigenlijk neem je de JOIN die je nu al hebt en INNER JOIN je die met een tweede (s2, r2) waarbij je in de ON opneemt dat s2.TimeStamp groter moet zijn dan s1.TimeStamp.

Om dat geheel zet je dan een SELECT die de resultaten van die subquery filtert op requests die Failed='Y' zijn en waarbij r2.TimeStamp gelijk aan NULL is.

Oftewel: de request is mislukt en er is geen recentere poging geweest == de meest recente poging is mislukt.

edit: als je in die week echt veel data hebt zou dit zomaar een heel inefficiente manier kunnen zijn.

[ Voor 3% gewijzigd door Verwijderd op 13-01-2018 01:52 ]

[PhilipsFan]

SELECT MAX(ID) FROM blabla WHERE etcetc GROUP BY Sessionid ofzo

of

SELECT whatever FROM blabla WHERE etcetc ORDER BY ID DESC LIMIT 1

Ben geen SQL specialist maar ik zou 1 van deze manieren kiezen. De laatste is waarschijnlijk het goedkoopst.

[ Voor 3% gewijzigd door PhilipsFan op 13-01-2018 02:44 ]

[CurlyMo]

MerijnB schreef op vrijdag 12 januari 2018 @ 23:51:
Dit heb ik geprobeerd, maar ik heb denk ik niet genoeg kennis, kun je een voorzetje geven of een linkje met relevante info?

Het is simpelweg in blokjes opbouwen. Daarom ook de inline views en subquery's. Zorgen dat je daarmee via tussenstappen komt waar je moet zijn.

Ben een SQL noob, had het nog door een pretty printer gehaald, die maakte er hoofdletters van

Zorg dat je geen pretty printer nodig hebt zou ik zeggen.

Thanks, dit is door gewoon gebrek aan kennis aan mijn kant.

Nu niet meer dus. Zolang je het verbod om een handmatige insert te doen op een auto incremented primary key dan maar respecteert.

Valid point, geen echte rede en kan ik prima veranderen; biedt dit voordelen? Waarom is dat beter dan deze enum?

Omdat het beter kan presteren. Integers kosten gewoon minder overhead dan characters. Met name in indexes.

De DDL is door HeidiSQL gegenereerd

Gewoon zelf schrijven.

Gebrek aan kennis, is het beter (makkelijker) om een timestamp veld te gebruiken? Het gaat mij om het moment dat iets gebeurd is (datum + tijd), (uit gewoonte) noem ik dit een timestamp.

Dit klopt toch niet helemaal vanuit mijn kant. Ik dacht dat in MySQL een timestamp het aantal secondes sinds unix time bevatte. Dus sinds 1970-01-01 00:00:01, maar het slaat wel degelijk een datum/tijd combinatie op zoals bij een datetime veld. Alleen is de range beperkt tot de unix time.

Laten we het erop houden dat het goed is je bewust te zijn van de beperkingen van het timestamp type:
https://dev.mysql.com/doc/refman/5.7/en/datetime.html

Is geen webserver; de details zijn long, boring en niet relevant denk ik. De data hierboven beschreven is fictief om specifiek het probleem waar ik tegenaan loop te beschrijven.

Je vraagt of je schema klopt, dat kunnen we met de informatie tot nu toe niet inschatten.

Dat is zeker een optie (iig om te proberen), waarom kan dit wel in bijv PostgreSQL en niet in MySQL (maw, wat voor soort query kan je wel doen in PostgreSQL en niet in MySQL om dit doel te bereiken)?

Ik heb de query wel klaar liggen, maar hier op tweakers is het doel dat je zelf dingen probeert om er zo van te leren. Ik zal als cadeau je wel verleiden PostgreSQL te gebruiken.

Begin eens met drie losse query's die je later kan combineren (zo pak ik het ook aan in deze gevallen):

Subquery 1: Haal per groep de laatste timestamp op binnen je datum range.
Subquery 2 bouwt voort op 1: Haal per groep de request status op voor die specifieke timestamp.

Combineer subquery 1 en 2 met je huidige query waarbij je checkt of het resultaat uit subquery 2 een 'Y' is.

PostgreSQL

SQL:

SELECT
    a.requestinfo1,
    a.requestinfo2,
    count(*) AS times
FROM
    (
    SELECT
        first_value(r.failed) over (partition by r.requestinfo1, r.requestinfo2 order by timestamp desc) last_failed,
        s.timestamp,
        r.*
    FROM
        sessions as s
    INNER JOIN
        requests r
    ON
        s.id = r.sessionid
    WHERE
        s.timestamp BETWEEN (NOW() - interval '7 days') AND (NOW() - interval '1 days')
    ) AS a
WHERE
    failed = 'Y'
AND
    last_failed = 'Y'
GROUP BY
    a.requestinfo1,
    a.requestinfo2

[epic007]

[code]
| ID | SessionID

code:

SELECT r.RequestInfo1,
       r.RequestInfo2,
       Count(*) AS Times
FROM   sessions s
       join requests r
         ON s.ID = r.SessionID
WHERE  r.Failed = 'Y'
       AND s.TimeStamp BETWEEN Date_sub(Curdate(), interval 7 day) AND
                               Date_add(Curdate(), interval 1 day)
GROUP  BY r.RequestInfo1,
          r.RequestInfo2
ORDER  BY Times DESC;

Als je de where r.Failed weghaalt en de count (*) vervangt door max (Timestamp).

Dan ben je er toch bijna?

[CurlyMo]

epic007 schreef op zondag 14 januari 2018 @ 21:06:
[code]
Dan ben je er toch bijna?

Om welk doel te bereiken?

[MerijnB]

Verwijderd schreef op zaterdag 13 januari 2018 @ 01:49:
edit: als je in die week echt veel data hebt zou dit zomaar een heel inefficiente manier kunnen zijn.

Definieer echt heel veel?
Dit is voor mij ook een beetje een experiment, we hebben deze specifieke data nooit zo uitgebreid gelogd, misschien blijkt het veel te veel te zijn om te handlen.

[MerijnB]

CurlyMo schreef op zaterdag 13 januari 2018 @ 12:18:
[...]

Het is simpelweg in blokjes opbouwen. Daarom ook de inline views en subquery's. Zorgen dat je daarmee via tussenstappen komt waar je moet zijn.

Dt is inderdaad wat ik aan het proberen was, alleen kwam ik niet verder.

[...]

Zorg dat je geen pretty printer nodig hebt zou ik zeggen.

[...]

Gewoon zelf schrijven.

Mwah, ik snap dat je dit zegt als je zelf midden een DBA bent oid, voor mij is dit een projectje on the side, en hoewel ik absoluut een persoon ben die wel wil snappen wat hij doet, vind ik het geen must om dit soort dingen zelf uit de losse pols goed te kunnen schrijven tbh.

[...]

Nu niet meer dus. Zolang je het verbod om een handmatige insert te doen op een auto incremented primary key dan maar respecteert.

Prima, maar heeft dit, naast het-is-niet-nodig-want ook nog een technische voor- en/of nadelen?

[...]

Omdat het beter kan presteren. Integers kosten gewoon minder overhead dan characters. Met name in indexes.

Dat is zeker interessant, ik ga hem veranderen.

[...]

Dit klopt toch niet helemaal vanuit mijn kant. Ik dacht dat in MySQL een timestamp het aantal secondes sinds unix time bevatte. Dus sinds 1970-01-01 00:00:01, maar het slaat wel degelijk een datum/tijd combinatie op zoals bij een datetime veld. Alleen is de range beperkt tot de unix time.

Laten we het erop houden dat het goed is je bewust te zijn van de beperkingen van het timestamp type:
https://dev.mysql.com/doc/refman/5.7/en/datetime.html

[...]

Je vraagt of je schema klopt, dat kunnen we met de informatie tot nu toe niet inschatten.

Maar wat voor een informatie heb je dan nog nodig? OP is een versimpelde uitleg van het soort informatie dat er gelogd moet worden, waar deze informatie exact vandaan komt is toch helemaal niet relevant om over een database layout na te denken?

[...]

Ik heb de query wel klaar liggen, maar hier op tweakers is het doel dat je zelf dingen probeert om er zo van te leren. Ik zal als cadeau je wel verleiden PostgreSQL te gebruiken.

I know en dat vind ik ook zo leuk aan Tweakers, ik ben ook heel blij met zetjes in de goede richting

Begin eens met drie losse query's die je later kan combineren (zo pak ik het ook aan in deze gevallen):

Subquery 1: Haal per groep de laatste timestamp op binnen je datum range.
Subquery 2 bouwt voort op 1: Haal per groep de request status op voor die specifieke timestamp.

Combineer subquery 1 en 2 met je huidige query waarbij je checkt of het resultaat uit subquery 2 een 'Y' is.

PostgreSQL

SQL:

SELECT
    a.requestinfo1,
    a.requestinfo2,
    count(*) AS times
FROM
    (
    SELECT
        first_value(r.failed) over (partition by r.requestinfo1, r.requestinfo2 order by timestamp desc) last_failed,
        s.timestamp,
        r.*
    FROM
        sessions as s
    INNER JOIN
        requests r
    ON
        s.id = r.sessionid
    WHERE
        s.timestamp BETWEEN (NOW() - interval '7 days') AND (NOW() - interval '1 days')
    ) AS a
WHERE
    failed = 'Y'
AND
    last_failed = 'Y'
GROUP BY
    a.requestinfo1,
    a.requestinfo2

Thanks, ik ga hier mee aan de slag!

[MerijnB]

epic007 schreef op zondag 14 januari 2018 @ 21:06:
[code]
| ID | SessionID

code:

SELECT r.RequestInfo1,
       r.RequestInfo2,
       Count(*) AS Times
FROM   sessions s
       join requests r
         ON s.ID = r.SessionID
WHERE  r.Failed = 'Y'
       AND s.TimeStamp BETWEEN Date_sub(Curdate(), interval 7 day) AND
                               Date_add(Curdate(), interval 1 day)
GROUP  BY r.RequestInfo1,
          r.RequestInfo2
ORDER  BY Times DESC;

Als je de where r.Failed weghaalt en de count (*) vervangt door max (Timestamp).

Dan ben je er toch bijna?

Sorry, ik zie het niet, dan heb ik geen totalen meer en ik hou geen rekening met de items die failed zijn, hoe brengt dit me dichter bij wat ik zoek

[CurlyMo]

MerijnB schreef op maandag 15 januari 2018 @ 10:19:
Prima, maar heeft dit, naast het-is-niet-nodig-want ook nog een technische voor- en/of nadelen?

Niet echt. Misschien een miniem performance verschil, want hij moet telkens een constraint check doen bij een insert.

Maar wat voor een informatie heb je dan nog nodig? OP is een versimpelde uitleg van het soort informatie dat er gelogd moet worden, waar deze informatie exact vandaan komt is toch helemaal niet relevant om over een database layout na te denken?

Misschien kan je nog iets vertellen over de inhoud van de requests?

[Verwijderd]

MerijnB schreef op maandag 15 januari 2018 @ 10:08:
Definieer echt heel veel?

Een hele hoop!

Dat is mede afhankelijk van de inrichting van je server, maar aangezien het een INNER JOIN op zichzelf is levert die subquery een veelvoud van het aantal records in die week op die doorzocht moeten worden. Bij 4 records is dat 3 + 2 + 1 = 6 unieke combinaties, bij 10 records 9 + 8 + 7 + 6 + 5 + 4 + 3 + 2 + 1 = 45, etcetera. Dat telt lekker aan.

[MerijnB]

CurlyMo schreef op maandag 15 januari 2018 @ 11:01:
Misschien kan je nog iets vertellen over de inhoud van de requests?

Een request bestaat uit 5 verschillende velden die samen iets zeggen over het request, alle velden zijn alfanumeriek. Sommige hebben altijd dezelfde lengte, sommige hebben een dynamische lengte, zeg maar dit:

code:

  `RequestInfo1` char(3) NOT NULL,
  `RequestInfo2` char(5) NOT NULL,
  `RequestInfo3` varchar(255) NOT NULL,
  `RequestInfo4` varchar(255) NOT NULL,
  `RequestInfo5` varchar(255) NOT NULL,

Naast de request wordt er ook metainformatie meegelogd wat iets zegt over de context waarin de requests zijn gedaan (dit heb ik niet in de OP beschreven omdat het niet relevant is voor de vraag).

Het doel is om zowel analytische queries te doen (welke soort requests zijn er gedaan over welke tijdsperiode, etc), als queries op gefailde requests (welke requests zijn er gefailed, wat was de context, zijn er bepaalde requests die vaak gedaan worden die failen, etc).

Een subset van deze informatie wordt nu als platte tekst gelogd, maar als je dan een analyse wil doen is dat een langdurig pain in the butt process, vandaar het onderzoek of dit direct op zo'n manier gelogd kan worden dat we er makkelijker informatie uit kunnen krijgen.

[CurlyMo]

MerijnB schreef op maandag 15 januari 2018 @ 12:54:
[...]


Een request bestaat uit 5 verschillende velden die samen iets zeggen over het request, alle velden zijn alfanumeriek. Sommige hebben altijd dezelfde lengte, sommige hebben een dynamische lengte, zeg maar dit:

code:

  `RequestInfo1` char(3) NOT NULL,
  `RequestInfo2` char(5) NOT NULL,
  `RequestInfo3` varchar(255) NOT NULL,
  `RequestInfo4` varchar(255) NOT NULL,
  `RequestInfo5` varchar(255) NOT NULL,

Naast de request wordt er ook metainformatie meegelogd wat iets zegt over de context waarin de requests zijn gedaan (dit heb ik niet in de OP beschreven omdat het niet relevant is voor de vraag).

Het doel is om zowel analytische queries te doen (welke soort requests zijn er gedaan over welke tijdsperiode, etc), als queries op gefailde requests (welke requests zijn er gefailed, wat was de context, zijn er bepaalde requests die vaak gedaan worden die failen, etc).

Een subset van deze informatie wordt nu als platte tekst gelogd, maar als je dan een analyse wil doen is dat een langdurig pain in the butt process, vandaar het onderzoek of dit direct op zo'n manier gelogd kan worden dat we er makkelijker informatie uit kunnen krijgen.

Is het geheel zo geheim dat je niet kan vertellen wat de daadwerkelijke inhoud is van zo'n request. Want het lijkt er nu op dat je beter een tweede tabel kan maken waarin je de verschillende requests (1 t/m 5) in rijen zet i.p.v. in kolommen.

[MerijnB]

CurlyMo schreef op maandag 15 januari 2018 @ 13:07:
[...]

Is het geheel zo geheim dat je niet kan vertellen wat de daadwerkelijke inhoud is van zo'n request. Want het lijkt er nu op dat je beter een tweede tabel kan maken waarin je de verschillende requests (1 t/m 5) in rijen zet i.p.v. in kolommen.

NDA.

Maar waarom is het beter om deze info in rijen te zetten ipv in kolommen?

[The Eagle]

MerijnB schreef op maandag 15 januari 2018 @ 12:54:
[...]


...
Naast de request wordt er ook metainformatie meegelogd wat iets zegt over de context waarin de requests zijn gedaan (dit heb ik niet in de OP beschreven omdat het niet relevant is voor de vraag).

Het doel is om zowel analytische queries te doen (welke soort requests zijn er gedaan over welke tijdsperiode, etc), als queries op gefailde requests (welke requests zijn er gefailed, wat was de context, zijn er bepaalde requests die vaak gedaan worden die failen, etc).

Een subset van deze informatie wordt nu als platte tekst gelogd, maar als je dan een analyse wil doen is dat een langdurig pain in the butt process, vandaar het onderzoek of dit direct op zo'n manier gelogd kan worden dat we er makkelijker informatie uit kunnen krijgen.

Even een klein stukje geknipt. Komt er dus feitelijk op neer dat je data wel hebt (je logging), maar dat dat ongestructureerde data is waar je niks mee kunt. Dus probeer je nu relationele data te laten genereren omdat je dat wel snapt.
Nofi, maar dan kijk je dus op de verkeerde manier naar je probleem. Ga maar eens naar tools op zoek waar je ongetsructureerde data mee telijf kan. Dit soort cases schreeuwt big data namelijk. En met Pig kom je dan al een heel eind. Of met Splunk, of ELK stack. Maar NIET met een RDBMS. Je logging en auditting is er, gebruik hem dan ook als zodanig en verzin er niet een moloch omheen omdat je zelf de kennis niet hebt om het anders te doen

[CurlyMo]

The Eagle schreef op maandag 15 januari 2018 @ 13:31:
[...]

Komt er dus feitelijk op neer dat je data wel hebt (je logging), maar dat dat ongestructureerde data is waar je niks mee kunt.

Volgens mij hebben we nog niet die informatie om die conclusie te trekken. Wat TS tot nu toe wil kan prima relationeel. Alleen over de aard van de requests en wat er dan daadwerkelijk mee geanalyseerd wordt blijft het vaag wegens de NDA. En dat antwoord is nodig om te weten of een relationele opzet zin heeft.

[MerijnB]

The Eagle schreef op maandag 15 januari 2018 @ 13:31:
[...]

Even een klein stukje geknipt. Komt er dus feitelijk op neer dat je data wel hebt (je logging), maar dat dat ongestructureerde data is waar je niks mee kunt. Dus probeer je nu relationele data te laten genereren omdat je dat wel snapt.
Nofi, maar dan kijk je dus op de verkeerde manier naar je probleem. Ga maar eens naar tools op zoek waar je ongetsructureerde data mee telijf kan. Dit soort cases schreeuwt big data namelijk. En met Pig kom je dan al een heel eind. Of met Splunk, of ELK stack. Maar NIET met een RDBMS. Je logging en auditting is er, gebruik hem dan ook als zodanig en verzin er niet een moloch omheen omdat je zelf de kennis niet hebt om het anders te doen

Best mogelijk dat dit beter kan met NoSQL (dat bedoel je denk ik?), dit is dan ook een experiment (ik weet zelf te weinig van NoSQL om daar nu een goede inschatting over te maken).

Ik schreef btw dat we nu een subset van deze data log, we zijn de informatie die gelogd wordt aan het veranderen en daarom kijken we of dit handiger gedaan kan worden.

[MerijnB]

CurlyMo schreef op maandag 15 januari 2018 @ 13:37:
[...]

Volgens mij hebben we nog niet die informatie om die conclusie te trekken. Wat TS tot nu toe wil kan prima relationeel. Alleen over de aard van de requests en wat er dan daadwerkelijk mee geanalyseerd wordt blijft het vaag wegens de NDA. En dat antwoord is nodig om te weten of een relationele opzet zin heeft.

Ik zie absoluut niet in waarom inhoudelijke informatie over de requests nodig is om zo'n conclusie te trekken? Volgens mij is er nu een heel goed beeld van de soort informatie en de relatie tot elkaar?

Wat voor informatie mist er dan nog?

[GlowMouse]

Moet de query performant zijn, of draait hij slechts af en toe?

[Mud]

MerijnB schreef op maandag 15 januari 2018 @ 12:54:
[...]

Het doel is om zowel analytische queries te doen (welke soort requests zijn er gedaan over welke tijdsperiode, etc), als queries op gefailde requests (welke requests zijn er gefailed, wat was de context, zijn er bepaalde requests die vaak gedaan worden die failen, etc).

Waarvoor gebruik je hier geen BI Tooling voor? Dit soort analyses vertalen naar query's en op je database(s) draaien is echt een nachtmerrie (qua onderhoud en performance)

[Woy]

@MerijnB

offtopic:
Als je binnen 24h iets wilt toevoegen en als laatste gepost hebt, gebruik dan de 'Wijzig' knop bij je laatste bericht.

[GlowMouse]

CurlyMo schreef op zaterdag 13 januari 2018 @ 12:18:

[...]

Omdat het beter kan presteren. Integers kosten gewoon minder overhead dan characters. Met name in indexes.

Onzin, een enum werkt intern vrijwel identiek aan een enum.

Dit klopt toch niet helemaal vanuit mijn kant. Ik dacht dat in MySQL een timestamp het aantal secondes sinds unix time bevatte. Dus sinds 1970-01-01 00:00:01, maar het slaat wel degelijk een datum/tijd combinatie op zoals bij een datetime veld.

Je haalt opslag en representatie door elkaar. Een timestamp is het aantal secondes sinds 1/1/1970, ook in MySQL.

[CurlyMo]

Mud schreef op maandag 15 januari 2018 @ 14:15:
[...]


Waarvoor gebruik je hier geen BI Tooling voor? Dit soort analyses vertalen naar query's en op je database(s) draaien is echt een nachtmerrie (qua onderhoud en performance)

Voor mij niet. Ik weet liever exact wat er gebeurt dan dat een BI tool voor me beslist.

GlowMouse schreef op maandag 15 januari 2018 @ 14:21:
[...]
Je haalt opslag en representatie door elkaar. Een timestamp is het aantal secondes sinds 1/1/1970, ook in MySQL.

Dat zei ik ook. In MySQL kan een timestamp dan ook geen andere datum bevatten dan dat. Dus geen datum vóór die tijd, terwijl dat in een datetime veld wel kan.

MerijnB schreef op maandag 15 januari 2018 @ 13:49:
[...]
Ik zie absoluut niet in waarom inhoudelijke informatie over de requests nodig is om zo'n conclusie te trekken? Volgens mij is er nu een heel goed beeld van de soort informatie en de relatie tot elkaar?

Welke type analyse draai je op de requests. Is dat een tekst analyse?

[ Voor 20% gewijzigd door CurlyMo op 15-01-2018 14:49 ]

[CurlyMo]

Nu doet ik het zelf ook

[ Voor 91% gewijzigd door CurlyMo op 15-01-2018 14:49 ]

[MerijnB]

CurlyMo schreef op maandag 15 januari 2018 @ 14:41:
[...]

Welke type analyse draai je op de requests. Is dat een tekst analyse?

zoiets als dit

code:

BEGIN
 select r.RequestInfo1, r.RequestInfo2, count(*) as times
 from requests r
 where r.`RequestInfo4` LIKE `whatever` and r.RequestInfo5 = 'blaat'
 group by r.RequestInfo1, r.RequestInfo2
 order by times desc;
END

waar wel nog een cap op timestamp omheen zal moeten.

[CurlyMo]

MerijnB schreef op maandag 15 januari 2018 @ 14:48:
[...]


zoiets als dit

code:

BEGIN
 select r.RequestInfo1, r.RequestInfo2, count(*) as times
 from requests r
 where r.`RequestInfo4` LIKE `whatever` and r.RequestInfo5 = 'blaat'
 group by r.RequestInfo1, r.RequestInfo2
 order by times desc;
END

waar wel nog een cap op timestamp omheen zal moeten.

En kan RequestInfo5 alles zijn als het een enkel woord is? En hoe groot is de tekst in RequestInfo4, 1, en 2?

[MerijnB]

[quote]CurlyMo schreef op maandag 15 januari 2018 @ 14:50:
[...]

En kan RequestInfo5 alles zijn als het een enkel woord is?

Deze vraag snap ik niet helemaal?

En hoe groot is de tekst in RequestInfo4, 1, en 2?

Zie MerijnB in "Opzet MySQL db OK en query aanpassen"

de Varchars zullen over het algemeen niet lang zijn (3 karakters oid), soms is het meer. 255 is gekozen omdat ik begrepen heb dat voor varchars tot 255 de overhead altijd 1 byte is (voor de lengte van de string).

[CurlyMo]

MerijnB schreef op maandag 15 januari 2018 @ 15:00:
[...]
Deze vraag snap ik niet helemaal?

Is dat altijd aap, noot of mies. Of kunnen het alle combinaties van 0 t/m 255 letters zijn?

de Varchars zullen over het algemeen niet lang zijn (3 karakters oid), soms is het meer. 255 is gekozen omdat ik begrepen heb dat voor varchars tot 255 de overhead altijd 1 byte is (voor de lengte van de string).

Hier kan het dus ook een willekeurige combinatie van karakters zijn van maximaal X, maar nooit meer dan 255?

[MerijnB]

CurlyMo schreef op maandag 15 januari 2018 @ 15:03:
[...]

Is dat altijd aap, noot of mies. Of kunnen het alle combinaties van 4 letters zijn? Of in jouw geval 5.


[...]

Hier kan het dus ook een willekeurige combinatie van karakters zijn van maximaal X, maar nooit meer dan 255?

Ah, duidelijk:

code:

  `RequestInfo1` char(3) NOT NULL,
  `RequestInfo2` char(5) NOT NULL,
  `RequestInfo3` varchar(255) NOT NULL,
  `RequestInfo4` varchar(255) NOT NULL,
  `RequestInfo5` varchar(255) NOT NULL,

RequestInfo1 ongeveer 30 verschillende waardes (bijna altijd 2 chars lang, soms 3)
RequestInfo2 ongeveer 100 verschilldende combinaties (bijna altijd 3 chars lang, soms 4 of 5)
RequestInfo3 kunnen er veel zijn, zeg 3000 oid (bijna altijd 1 .. 3 lang)
RequestInfo4 ongeveer 30 verschilldende waardes (lengte variable, zal in de praktijk niet snel langer dan 30 oid zijn).
RequestInfo5 ongeveer 200 verschilldende waardes (bijna altijd 2 lang)

[CurlyMo]

Zijn sommige van die requestinfo waardes niet te categoriseren in een aparte format tabellen?

[MerijnB]

CurlyMo schreef op maandag 15 januari 2018 @ 15:20:
Zijn sommige van die requestinfo waardes niet te categoriseren in een aparte format tabellen?

Als ik je goed begrijp (in een andere tabel alle mogelijke waardes noteren en daarnaar referenen?) zou dat opzich kunnen, alhoewel het goed mogelijk is dat er 'spontaan' een nieuwe waarde bij komt (dus dan moet ik vanuit het proces wat logt die format tabel aanvullen).

[CurlyMo]

MerijnB schreef op maandag 15 januari 2018 @ 15:46:
[...]


Als ik je goed begrijp (in een andere tabel alle mogelijke waardes noteren en daarnaar referenen?)

Dat zou zinvol kunnen zijn. Dat scheelt namelijk tekst vergelijkingen.

alhoewel het goed mogelijk is dat er 'spontaan' een nieuwe waarde bij komt (dus dan moet ik vanuit het proces wat logt die format tabel aanvullen).

Dat is niet erg.

[EvH]

offtopic:
[quote]
`IP` char(15) NOT NULL,
[/quote]

Houd je rekening met IPv6?

[MerijnB]

EvH schreef op maandag 15 januari 2018 @ 16:14:

offtopic:
[quote]
`IP` char(15) NOT NULL,
[/quote]

Houd je rekening met IPv6?

Thanks, en nee (nog) niet

[Gomez12]

MerijnB schreef op maandag 15 januari 2018 @ 15:46:
[...]
Als ik je goed begrijp (in een andere tabel alle mogelijke waardes noteren en daarnaar referenen?) zou dat opzich kunnen, alhoewel het goed mogelijk is dat er 'spontaan' een nieuwe waarde bij komt (dus dan moet ik vanuit het proces wat logt die format tabel aanvullen).

Waarom handmatig een index bouwen als je dbase dat ook kan?

Wat je hier beschrijft is gewoon een index

[CurlyMo]

Gomez12 schreef op dinsdag 16 januari 2018 @ 09:26:
[...]

Waarom handmatig een index bouwen als je dbase dat ook kan?

Wat je hier beschrijft is gewoon een index

Dat zie ik niet?

[Gomez12]

CurlyMo schreef op dinsdag 16 januari 2018 @ 10:16:
[...]

Dat zie ik niet?

Wat denk je dat een index is?

Dat is ook (gesimplificeerd) gewoon een b-tree met enkel de unieke waardes en referenties naar de daadwerkelijk records.
Waarom zou je dat handmatig gaan bouwen?

[CurlyMo]

Gomez12 schreef op dinsdag 16 januari 2018 @ 11:09:
[...]
Waarom zou je dat handmatig gaan bouwen?

Waarom zou je sowieso een database model maken als je redundante gegevens gewoon in kolommen kan stoppen? Oftewel, gewoon datasets in databases proppen.

Je kan inderdaad een karakter kolom maken met het volledig uitgeschreven land van geboorte per geboorterecord, of je kan een landen tabel maken waarna je refereert (Nederland = 6030). Volgens een index maken op je landcodes i.p.v. op je uitgeschreven landnamen. Dat laatste is toch echt een stukje efficiënter (qua index overhead)?

[CurlyMo]

CurlyMo schreef op zaterdag 13 januari 2018 @ 12:18:
Ik heb de query wel klaar liggen, maar hier op tweakers is het doel dat je zelf dingen probeert om er zo van te leren. Ik zal als cadeau je wel verleiden PostgreSQL te gebruiken.

Zonde om hem weg te gooien:

SQL:

SELECT
    r.requestinfo1,
    r.requestinfo2,
    count(*) AS times
FROM
    sessions s
INNER JOIN
    requests r
ON
    s.id = r.sessionid
WHERE
    r.failed = 'Y'
AND
    s.timestamp BETWEEN date_sub(curdate(), interval 7 day) AND date_add(curdate(), interval 1 day)
AND
    (SELECT
        b.failed
    FROM
        sessions a
    INNER JOIN
        requests b
    ON
        a.id = b.sessionid
    WHERE
        a.timestamp =
        (SELECT
            MAX(z.timestamp)
        FROM
            sessions z
        INNER JOIN
            requests y
        ON
            z.id = y.sessionid
        WHERE
            z.timestamp BETWEEN date_sub(curdate(), interval 7 day) AND date_add(curdate(), interval 1 day)
        AND
                y.requestinfo1 = b.requestinfo1
        AND
                y.requestinfo2 = b.requestinfo2
        )
    AND
            r.requestinfo1 = b.requestinfo1
    AND
            r.requestinfo2 = b.requestinfo2
    ) != 'Y'
GROUP BY
  r.requestinfo1,
  r.requestinfo2
ORDER BY
  times DESC;

[MerijnB]

Even een update van mijn kant (was beetje stil )

Helaas ben ik in werkzaamheden enorm side tracked geraakt, vandaar de vertraging.

Ik was in twee richtingen bezig: adhv wat je hier gepost hebt in MySQL wat queries te bouwen en doorgronden, en daarnaast een test met PostgreSQL de boel opnieuw opzetten om daarmee te spelen.

Op het moment dat ik hier weer lucht voor heb ga ik dit zeker oppakken, en beide kanten verder onderzoeken.

In ieder geval bedankt voor alle input tot nu toe