Mysql server optimalisatie

[d4v1d]

Hoi,

Ik ben voor een irc netwerk een ding aan het maken wat alles wat er gezegd en gedaan wordt opslaat in een database. Nu staat dat ding nog op mijn eigen server nu, maar ik heb ook een vps gekregen waar ik alles op moet gaan draaien, Apache (om de logs terug te lezen), Mysql (Om eh.. Ja waarvoor... ).
Nu is er op die vps 160GB aan opslagcapaciteit beschikbaar, er is nog +/- 3700MB ram vrij en het heeft een disk I/O van richting de 100 MB/s

Nu gaat er dus gebeuren dat er op sommige momenten meerdere query's per seconde komen (2 of 3) Nu denk ik dat Mysql dit al redelijk aan kan maar als ik toch ongeveer 3,7GB ram vrij hebt valt er vast wat te optimaliseren.

Nu ziet de tabel waar de logs inkomen er als volgt uit (Misschien is dit handig voor jullie?)

code:

CREATE TABLE `log` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `created` DATETIME NULL DEFAULT NULL,
    `channel` VARCHAR(255) NULL DEFAULT NULL,
    `nick` VARCHAR(20) NULL DEFAULT NULL,
    `uhost` VARCHAR(255) NULL DEFAULT NULL,
    `type` VARCHAR(20) NULL DEFAULT NULL,
    `arg` VARCHAR(255) NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=36816;

_{Niet op die auto increment letten.. Zo komt die regelrecht uit het programma waarmee ik de mysql database bekijk.}

id lijkt me duidelijk, created is de tijd dat hij in de tabel wordt gestopt (Gewoon met de mysql functie NOW()), channel is het betreffende chat kanaal, nick is de persoon die het zegt, uhost is de user host, type is bv. MESSAGE, JOIN, QUIT, NOTICE en arg is hetgeen wat er gezegd werd.

Er is ook nog een tabel die niet heel veel boeit, hier wordt af en toe wat informatie uitgehaald en ingezet ivm. veiligheid en opvragen van de logs.

code:

CREATE TABLE `hashes` (
    `id` INT(10) NOT NULL AUTO_INCREMENT,
    `channel` VARCHAR(32) NOT NULL,
    `hash` VARCHAR(5) NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=13;

Wat kan ik allemaal doen om hem zo snel mogelijk te laten lopen? Ik ga helemaal compilen (geen apt-get dus.) tot en met de config zelf aanpassen maar ik weet nu nog niet precies waar ik op moet letten.

Alvast bedankt,

[Rainmaker]

Gaat het je om read performance, of om schrijf performance?

Voor read performance kun je indexes gebruiken. Meer RAM gebruik, meer disk gebruik, maar wel een stuk snellere performance bij gebruik van SELECT queries.

Voor write performance (INSERT queries), moet je zorgen dat je transactielog (sinds MySQL 5 "binary log" genoemd) zo snel mogelijk ge-update kunnen worden. Dus liefst op een SSD, of desnoods een RAM disk. Een RAM disk heeft natuurlijk wel als risico dat je dataverlies hebt bij een plotselinge crash.
Zie onder andere http://dev.mysql.com/doc/refman/5.5/en/binary-log.html
Verder; zou je ook eens kunnen kijken naar de "sync_binlog" variabele. Zet deze op 1000 transacties om eens in de 1000 transacties een daadwerkelijke sync naar disk te doen.

Voor UPDATE queries zul je een beetje van beide moeten gebruiken (mits je een WHERE gebruikt ).

Je kunt natuurlijk ook wat dingen aan het OS doen; disk async mounten, met data=writeback, met noatime etc. Zie bijvoorbeeld: http://erikugel.wordpress...fastest-linux-filesystem/

Aangezien het een VM is, kun je ook nog denken aan switchen naar de NOOP I/O scheduler, en ervoor zorgen dat je de juiste modules voor je disk gebruikt (paravirt indien mogelijk, of VMWare tools up2date houden...)

Profile je reads / writes naar je filesystem. Pas de chunksize van je RAID en / of filesystem hierop aan. Let erop dat partities goed uitgelijnd zijn met de disk. Zie http://clariionblogs.blogspot.nl/2008/02/disk-alignment.html

Over het algemeen; focus je op I/O, daar is meestal de meeste winst mee te halen.

[ Voor 7% gewijzigd door Rainmaker op 08-03-2013 22:35 ]

[CAPSLOCK2000]

Weet je iets over hoeveel verkeer je gaat verwerken?
Je hebt het over 2 a 3 queries per seconde. Gaat het dan om 2 a 3 regels text die per seconde gesproken worden en moeten worden opgeslagen of heb je het over bevragingen van de database?

Ik weet niet of MySQL de handigste manier is om enorme hoeveelheden tekst op te slaan. Daar zijn gespecialiseerde databases voor. De naam Lucene komt bij me boven (al heb ik daar zelf geen ervaring mee).

[Rainmaker]

Lucene is toch meer voor content indexing? Full search queries e.d.?

Ik heb wel begrepen dat ondertussen een aantal developers een fork van mysql zijn begonnen; MariaDB. Dit vanwege de aquisitie door Sun / Oracle

Onder andere Wikipedia schijnt hier (gefaseerd) op over te gaan.

That said; je zou inderdaad naar een "makkelijkere" database kunnen kijken. Bijvoorbeeld een eenvoudige key -> value database. Bijvoorbeeld Tokyocabinet, HDB of HamsterDB.

Maar; als MySQL je voorkeur heeft, 2 - 3 ("normale", niet 3 pagina's aan joins en subqueries gebruiken) queries per seconde zou echt niet zo'n probleem moeten zijn. En er valt zeker wat aan te tunen.

[d4v1d]

Rainmaker, Bedankt voor je ongelooflijk uitgebreid antwoord, daar ga ik me even in verdiepen.
Er kunnen momenten zijn dat er 2 á 3 zinnen per seconde in de database moeten komen. Maar het kan ook zijn dat er om de 5 tot 10 minuten eentje is (om 2 uur 's nachts bijvoorbeeld)

Ik houd het het liefst op mysql, omdat ik dat al redelijk goed ken.

Het gaat eigenlijk om read EN write performace.. Want er kunnen dus 2 tot 3 zinnen per seconde komen. Maar als er uiteindelijk een webinterface (met PHP) is dan kunnen kanaal eigenaren de voledige logs van hun kanaal opvragen.. En dat kunnen toch een behoorlijk aantal rows zijn.

Momenteel gooi ik zo regels in de database (gaat trouwens via een zelf geschreven tcl script voor Eggdrop)

code:

INSERT INTO log (created,channel,nick,uhost,type,arg) VALUES (NOW(),'$channel','$nick','$uhost','$type','$arg')

En ik wil ze er uiteindelijk zo uithalen:

code:

SELECT * FROM log WHERE channel='#kanaal' ORDER BY created

En dat geeft het volgende terug..
/* 0 rows affected, 13.868 rows found. Duration for 1 query: 3,588 sec. (+ 1,700 sec. network) */
En dat wil ik eigenlijk sneller.. Het zou ook leuk zijn als INSERT sneller kan maar het gaat me vooral om dat ik sneller kan uitlezen.

Als het niet sneller kan ga ik limit doen op de laatste XXX results.

[Wolfboy]

Voor die laatste query heb je simpelweg een index op channel en created nodig. Als je queries altijd in deze vorm zijn (filter op channel en order by op created) dan zou je ervoor kunnen kiezen om de index op deze kolommen tegelijk te zetten.

Afhankelijk van hoe je de database verder gaat querien kan het eventueel ook zinnig zijn te partitioneren op channel zodat elk kanaal effectief zijn eigen tabel krijgt, dat maakt het een stuk makkelijker om uit te lezen en ook het wegschrijven kan beter verdeeld worden (i.e. dat zou op andere schijven kunnen).

Maar... als je maar 2-3 rijen per seconde insert hoef je je eigenlijk geen zorgen te maken. Als je continu op meer dan 10 per seconden zit dan kan je misschien eens wat problemen gaan krijgen, maar daar zou ik me voorlopig geen zorgen over maken. Zelfs al komt er een flinke burst dan zal het even een seconde langer duren voor het weggeschreven is maar daarna is alles weer up to date.

[danslo]

Hoe groot is je ibdata? Het liefst wil je dat je innodb_buffer_pool_size altijd groter is dan dat getal, zodat MySQL direct uit geheugen kan lezen.

Daarnaast kan je op zich wel tweaken aan dingen als query caching etc, maar in eerste instantie wil je eerder kijken naar het plaatsen van juiste indexes, geen onnodiges joins maken, etc.

[Wolfboy]

cls schreef op zaterdag 09 maart 2013 @ 16:27:
Daarnaast kan je op zich wel tweaken aan dingen als query caching etc, maar in eerste instantie wil je eerder kijken naar het plaatsen van juiste indexes, geen onnodiges joins maken, etc.

NOFI maar query caching is in dit geval een _heel_ slecht idee om aan te hebben.

Aangezien er continu inserts gebeuren zal MySQL continu z'n query cache flushen en heb je er dus helemaal niets aan
Beter om de query cache gewoon helemaal uit te zetten aangezien het toch niet gebruikt gaat worden.

[_JGC_]

Klopt, de optimale query cache size is nog altijd 0. Query caches kan je beter in je applicatie oplossen.

Nadeel van query cache bij veel inserts is dat MySQL continu de cache moet invalidaten met de nodige locks. Is brak voor performance, dus liever uitschakelen. Afhankelijk van je MySQL versie betekent uitschakelen ook echt uitschakelen, veel oude versies gaan nog steeds proberen de lock te krijgen bij elke insert.

[d4v1d]

Oke, bedankt. ik ga mysql nu even compilen en dan de tips opvolgen.

@cls ik denk dat ik dit bestand nu nog niet heb? Moet nog compilen

[JaQ]

Dmbekker schreef op vrijdag 08 maart 2013 @ 14:03:
Nu denk ik dat Mysql dit al redelijk aan kan maar als ik toch ongeveer 3,7GB ram vrij hebt valt er vast wat te optimaliseren.

Als klanten mij vragen om een omgeving te tunen zonder helder doel, dan is de eerste missie het doel duidelijk krijgen. Waar zit je performance probleem / wat moet sneller. In het wilde weg aan settings gaan draaien om "het" sneller te maken is dermate ongestructureerd dat je netto resultaat altijd minder is dan bij een gerichte actie.

In algemene termen is er uiteraard altijd wat te noemen. Ik kom uit de Oracle wereld, dus je moet mijn uitspraken wel even valideren. (moet je altijd doen, het is immers advies van een internet forum )

Om insert snelheid te maximaliseren is van belang om:
- Je IO te verspreiden over meerdere fysieke disken. IO is zowel je binary logs (redo logs heten die in Oracle) + eventuele archives daar van en je data zelf (hash partitionering van je logs tabel werkt waanzinnig goed). Nu heb je daar niet direct invloed op in jouw usecase, want je gebruikt een VPS waar je ongetwijfeld niet zomaar aan de hardware configuratie (of disk configuratie) kan knutselen
- Zo min mogelijk te inserten. Dat klinkt triviaal, maar dat is het niet. Des te enger je kolomen, des te minder I/O's om te verwerken. Het lijkt er op dat daar ook al een keuze is gemaakt, die varchar(255) voor de arg kolom is een keuze (want je kan RFC 2812 maximaal 512 karakters in een message stoppen, inclusief headers en zo).
- indien mogelijk je inserts batchmatig uitvoeren (insert as append), real time is niet altijd beter, zeker als je selecties helemaal niet geïnteresseerd zijn in real-time data.

Om selecteer snelheid te maximaliseren is van belang om:
- indexen te gebruiken wanneer dat beter is (vuistregel in Oracle: minder dan 20% van de totale data selecteren dan is een index interessant, maar er is betere wiskunde voor die ook kijkt naar number of distinct values in een kolom en andere statistieken. Daar zal ik je niet mee vervelen Ik verwacht echter niet dat mysql daar significant in verschilt)
- indien mogelijk te partitioneren zodat je enkel in een partitie gaat zoeken, bijvoorbeeld partition by range.
- enkel de kolommen op te halen die je nodig hebt in je applicatie, hiermee kan je de lookup van het record voorkomen (althans, in Oracle) Gecombineerde index over meer dan 1 kolom.
- dan nog een heel lang verhaal over casting, gebruik van functies in queries, geen dummy values, passende data typen en meer van dat soort dingen. Maar ik verwacht dat mysql ook wel een sql tuning reference heeft?
- Als je zowel snel wilt inserten als selecteren en je kiest voor partitionering, dan zou je naar subpartitionering kunnen kijken. Range-hash partities is iets wat ik veel tegen komen binnen Oracle.

Anyway, zoals je ziet kan iedere gek (en hiermee doel ik even op mezelf ) je zo tien dingen vertellen waar je iets mee zou kunnen doen. Of het ook echt noodzakelijk is (of zelfs wenselijk) is een tweede. Vergeet niet dat partitioning ook extra complexiteit is. Complexiteit die je wel moet beheren. Hetzelfde geldt voor veel micro-management trucken. Wat voor jou passend is, is echt sterk afhankelijk van de use case (zie de opmerking over query cache, als je batchmatig gaat laden kan het query cache wel relevant worden. Ik gok zomaar dat Wolfman zich dan ook minder sterk had uitgedrukt, maar het is van belang om de nuance te kunnen zien).

Dmbekker schreef op zaterdag 09 maart 2013 @ 21:26:
Oke, bedankt. ik ga mysql nu even compilen en dan de tips opvolgen.

Win je daadwerkelijk iets met zelf compilen? (meetbaar?)

[ Voor 3% gewijzigd door JaQ op 09-03-2013 22:32 ]

[Wolfboy]

JaQ schreef op zaterdag 09 maart 2013 @ 22:31:
...als je batchmatig gaat laden kan het query cache wel relevant worden. Ik gok zomaar dat Wolfman zich dan ook minder sterk had uitgedrukt, maar het is van belang om de nuance te kunnen zien).

Uiteraard
Helaas is de query cache implementatie van MySQL zo simpel dat het in maar een heel beperkt aantal use-cases echt zinnig is.

Als de data read-only is dan kan je er wel voordeel aan hebben, maar als er ook maar een enkele mutatie is dan zal MySQL direct de cache invalideren en heb je er dus niets meer aan.

Gezien de query (en het gebrek aan indexes volgens de tabeldefinitie) lijkt me dat het zinniger is om daar eerst te beginnen. En het ziet er voorlopig nog niet naar uit dat er een enorme hoeveelheid data in de tabel terecht komt.

offtopic:
Goede post overigens

[danslo]

Overigens had ik het over 'tweaken' van 'dingen als query cache'. Dat kan ook betekenen dat je het uit moet zetten Het punt van heel m'n post was dat je in eerste instantie beter je focus op indexes en queries (zware joins, subqueries, etc) kan leggen.

Daarna pas zou ik naar de MySQL settings zelf gaan kijken.

[ Voor 6% gewijzigd door danslo op 10-03-2013 00:04 ]

[JaQ]

Wolfboy schreef op zaterdag 09 maart 2013 @ 23:27:
Gezien de query (en het gebrek aan indexes volgens de tabeldefinitie) lijkt me dat het zinniger is om daar eerst te beginnen.

Krijg je bij MySQL meteen de indexen terug als je een tabel-definitie opvraagt? (in Oracle namelijk niet)

Focussen op queries (access paden en filters) is m.i. ook de kortste klap. (een mening die cls ook deelt )

[ Voor 14% gewijzigd door JaQ op 10-03-2013 20:06 ]

[deadinspace]

Dmbekker schreef op vrijdag 08 maart 2013 @ 14:03:
Wat kan ik allemaal doen om hem zo snel mogelijk te laten lopen? Ik ga helemaal compilen (geen apt-get dus.)

Waarom? Je schiet er over het algemeen echt helemaal niks mee op, maar je zorgt er wel voor dat je geen automatische security updates krijgt. Ga je je abbonneren op security mailinglists, die mails lezen en MySQL opnieuw downloaden en compilen zodra er een probleem bekend is? Nee, dat ga je niet en dus draai je vroeg of laat een MySQL met bekende lekken. Zelfde geldt voor bv Apache en (zeker) PHP.

Doe jezelf en de rest van het internet een plezier en gebruik gewoon de distributiepackages

[Wolfboy]

cls schreef op zondag 10 maart 2013 @ 00:03:
Overigens had ik het over 'tweaken' van 'dingen als query cache'. Dat kan ook betekenen dat je het uit moet zetten Het punt van heel m'n post was dat je in eerste instantie beter je focus op indexes en queries (zware joins, subqueries, etc) kan leggen.

Ah, dat was me niet duidelijk. Maar daarom ook de NOFI

JaQ schreef op zondag 10 maart 2013 @ 20:05:
[...]

Krijg je bij MySQL meteen de indexen terug als je een tabel-definitie opvraagt? (in Oracle namelijk niet)

Focussen op queries (access paden en filters) is m.i. ook de kortste klap. (een mening die cls ook deelt )

Dit ziet eruit als een export van de hele tabeldefinitie, in dat geval verwacht ik ook de indexes ja. Anders is er inderdaad geen zinnig antwoord te geven op de vraag

[d4v1d]

JaQ schreef op zaterdag 09 maart 2013 @ 22:31:

[...]

Win je daadwerkelijk iets met zelf compilen? (meetbaar?)

Blijkt uiteindelijk ubuntu op die vps te staan. Ik dacht dat er debian op stond, dan ging ik liever compilen... versie 5.1 staat nog in de repo's daar

[_JGC_]

Is niks aan MySQL 5.1 voor Debian stable op zich. Persoonlijk vervang ik tegenwoordig alles door Percona Server 5.5, die hebben ook gewoon pakketjes voor Debian Stable.
Maargoed, als je zelf al niet weet hoe je my.cnf opties moet tunen en welke indexes je op je tabellen moet zetten, is het upgraden of zelf compileren van MySQL het laatste waar je aan moet denken in je optimalisatietraject.

[deadinspace]

Dmbekker schreef op maandag 11 maart 2013 @ 14:29:
Ik dacht dat er debian op stond, dan ging ik liever compilen... versie 5.1 staat nog in de repo's daar

Dus? Zit er daadwerkelijk iets in nieuwere versies dat nuttig voor je is? Dat is meestal namelijk niet het geval. En de exacte versie in Debian is tenminste uitgebreid getest (talloze Debian machines draaien die versie) en je hebt fatsoenlijke security support (zie mijn vorige post).

[d4v1d]

deadinspace schreef op maandag 11 maart 2013 @ 14:44:
[...]

Dus? Zit er daadwerkelijk iets in nieuwere versies dat nuttig voor je is? Dat is meestal namelijk niet het geval. En de exacte versie in Debian is tenminste uitgebreid getest (talloze Debian machines draaien die versie) en je hebt fatsoenlijke security support (zie mijn vorige post).

Geen idee, ik wil gewoon het nieuwste hebben Maar als er niks mis is met 5.1 dan zou ik die ook kunnen nemen..

[deadinspace]

Dmbekker schreef op dinsdag 12 maart 2013 @ 11:23:
Geen idee, ik wil gewoon het nieuwste hebben

Maar het nieuwste is niet per se het beste

Zeker als het op serversoftware aankomt kan het vaak geen kwaad de kat een beetje uit de boom te kijken. Aangenomen dat veiligheid en stabiliteit je belangrijkste eisen zijn natuurlijk.

Maar als er niks mis is met 5.1 dan zou ik die ook kunnen nemen..

Nouja, mijn punt was niet zozeer dat je 5.1 moet nemen, maar meer dat je vaak het beste uit bent met de standaardversie van je distributie. Afhankelijk van welke versie van Ubuntu je hebt kan dat best een andere versie dan 5.1 zijn.

De voordelen van de distributiepackages gebruiken zijn:

• Security updates + testen vanuit je distributie
• Die versie wordt door veel mensen gebruikt, dus blijkbaar werkt die versie ook voor veel mensen zonder problemen
• Software en documentatie voor jouw distributie zal uitgaan van die versie, dus beste compatibility