MySQL memory gebruik

[RvdH]

Hello,

Op een server van ons met 1Gb geheugen (P4 2.6GHz) wordt mysql steeds en steeds langzamer, omdat de database steeds en steeds groter wordt. Op het moment is de database in totaal 5.5Gb groot, en de grootste table is 1.1Gb.

In 'top' is te zien dat mysqld 264M in gebruik heeft. De overige 740M gebruikt Linux dus voor I/O disk caching ofzo. Hoe krijg ik nu mysql, of Linux, zover dat er meer geheugen naar MySQL gaat?

En zal dit dan ook daadwerkelijk de performance beinvloeden, of heeft mysql op het moment gewoon niet meer als 264M nodig?

Heeft het uiteindelijk nut de server te upgraden met meer geheugen?

Dit is de my.cnf (of iig het relevante daaruit):

code:

port                    =       3306
socket                  =       /tmp/mysql.sock
set-variable            =       key_buffer_size         =       256M
set-variable            =       max_allowed_packet      =       1M
#set-variable           =       thread_stack            =       128K
set-variable            =       max_connections         =       300
#set-variable           =       query_buffer_size       =       4M
set-variable            =       record_buffer           =       32M
#set-variable           =       record_buffer           =       64M
set-variable            =       record_rnd_buffer       =       32M
#set-variable           =       sort_buffer             =       8M
set-variable            =       sort_buffer             =       4M
set-variable            =       join_buffer             =       16M
set-variable            =       table_cache             =       768
set-variable            =       wait_timeout            =       180

[ Voor 46% gewijzigd door RvdH op 05-03-2003 10:17 ]

[Zwerver]

als ik het helemaal goed begrepen heb, dan krijgen processen die het nodig hebben ook het geheugen waar ze omvragen... en als mysql het enige proces is (naast de standaard linux processen dan) dan moet dat wel goed komen dunkt mij, dus meer geheugen alloceren (als dit al kan, geen idee eigenlijk?!) zou niet hoeven eigenlijk.

edit:
btw, wat gebeurd er als je die buffersize, die nu op 256, staat hoger zet in top?

[ Voor 14% gewijzigd door Zwerver op 05-03-2003 10:20 . Reden: buffer size ]

[RvdH]

We hebben trouwens ook al "voor de gein" alle set-variable opties een keer verdubbeld, om te kijken of het dan ook 2 x zoveel geheugen zou gebruiken.. of dat uiteindelijk is gebeurd weet ik niet meer, maar hij werd er wel ontiegelijk traag van (?) !

Ben benieuwd hoe dit is ingesteld op de t.net servers... kees?

[ Voor 3% gewijzigd door RvdH op 05-03-2003 10:24 ]

[Verwijderd]

het geheugengebruik lijkt een beetje op key_buffer_size + 8 MB, dus misschien kan je deze verhogen (verdubbelen misschien)?

[edit]
Ik ben geen expert, maar alle opties behalve key_buffer_size lijken mij te zijn voor als er een query loopt, key_buffer_size geldt ook tussen queries.

[ Voor 40% gewijzigd door Verwijderd op 05-03-2003 10:26 ]

[RvdH]

Ik zal het eens bekijken ja. Het lastige is dat onze testserver precies dezelfde data e.d. bevat, maar lang niet zo druk is en dus ook niet evenveel geheugen als de productie-server alloceert .

[Wilke]

Bij database-servers geldt i.h.a.: hoe meer geheugen, hoe beter.

Er is op de pagina's van MySQL zelf al van alles te vinden over de instellingen, hier heb je bv. zo'n pagina. Waarschijnlijk is het wel zinvol om bv. de keybuffer groter te maken. Als je InnoDB tables gebruikt, zijn er nog wel meer opties.

Kortom, MySQL moet je wel meer geheugen kunnen geven, maar ik denk dat je eerst zelf eens aan het zoeken moet slaan, veel hiervan staat namelijk gewoon in de documentatie....maar wie weet kan kees er idd nog iets over zeggen

[RvdH]

Die documentatie hebben we dus al 30 keer doorgenomen, anders waren we nooit aan al die set-variable opties gekomen. In de documentatie staat ook dat je X % van je geheugen aan keybuffer moet geven, maar als we dat doen, is de server 2 keer zo langzaam als dat ie nu is.

[Verwijderd]

Om wat voor data gaat het percies? Je gebruikt Myisam?

En wat zijn de meeste queries?

[ Voor 24% gewijzigd door Verwijderd op 05-03-2003 10:38 ]

[Verwijderd]

Wat voor opslag systeem gebruik je voor mysql?

[RvdH]

MyISAM tables op een ext3 filesysteem op een RAID-5 disk.
Meeste queries zijn selects, zoals deze (eentje uit de slow query log):

SELECT B.RecordNumber FROM books AS B , yauthor AS YA1 USE INDEX (WordBook) , yauthor AS YA2 USE INDEX (WordBook) , ytitle AS YT1 USE INDEX (WordBook) , ytitle AS YT2 USE INDEX (WordBook) WHERE YA1.BookNumber = B.RecordNumber AND YA2.BookNumber = B.RecordNumber AND YA1.WordNumber = 9839 AND YA2.WordNumber = 1683 AND YT1.BookNumber = B.RecordNumber AND YT2.BookNumber = B.RecordNumber AND YT1.WordNumber = 452 AND YT2.WordNumber = 6202 AND (B.DollarPrice <= 2000) LIMIT 0,51;

WordNumber is een id wat bij een woord hoort, en daar wordt op gezocht op een website. Explain laat zien dat de goede indices enzo worden gebruikt. Books is 1 van de tabellen van 1.1Gb.

[Verwijderd]

Met grote tabellen heeft InnoDB zijn voordelen, mijn load van een Dual Pen III(1,5 GB) ging terug naar een derde.

In de test omgeving kan je deze makkelijk converteren naar InnoDB.

Ook zou die query iets anders kunnen maar dat is iets voor P&W

[Verwijderd]

Voer deze query eens uit in Mysql console en noteer de query tijd. Verander nu alle tabellen met meer dan 100.000 rows naar InnoDB en probeer het dan nog eens.

Ik ben benieuwd!

PS: Als al je geheugen wordt gebruikt dan is het altijd handig om er meer bij te plaatsen, tegenwoordig kost 2 GB extra niets voor een bedrijf(icm met de voordelen).

[RvdH]

mysql> SELECT B.RecordNumber FROM books AS B , yauthor AS YA1 USE INDEX (WordBook) , yauthor AS YA2 USE INDEX (WordBook) , ytitle AS YT1 USE INDEX (WordBook) , ytitle AS YT2 USE INDEX (WordBook) WHERE YA1.BookNumber = B.RecordNumber AND YA2.BookNumber = B.RecordNumber AND YA1.WordNumber = 9839 AND YA2.WordNumber = 1683 AND YT1.BookNumber = B.RecordNumber AND YT2.BookNumber = B.RecordNumber AND YT1.WordNumber = 452 AND YT2.WordNumber = 6202 AND (B.DollarPrice <= 2000) LIMIT 0,51;
Empty set (28.73 sec)

Ga nu de InnoDB files aanmaken en tables converten..

[RvdH]

Na een zooi tables omzetten:

Database changed
mysql> SELECT B.RecordNumber FROM books AS B , yauthor AS YA1 USE INDEX (WordBook) , yauthor AS YA2 USE INDEX (WordBook) , ytitle AS YT1 USE INDEX (WordBook) , ytitle AS YT2 USE INDEX (WordBook) WHERE YA1.BookNumber = B.RecordNumber AND YA2.BookNumber = B.RecordNumber AND YA1.WordNumber = 9839 AND YA2.WordNumber = 1683 AND YT1.BookNumber = B.RecordNumber AND YT2.BookNumber = B.RecordNumber AND YT1.WordNumber = 452 AND YT2.WordNumber = 6202 AND (B.DollarPrice <= 2000) LIMIT 0,51;
Empty set (17.23 sec)

Maargoed, InnoDB gebruiken is nog geen oplossing voor het feit dat mysql zo weinig geheugen gebruikt. We hebben eerder proberen over te stappen op InnoDB maar dat ging vanwege de indices e.d. zo traag allemaal dat we besloten hebben alles maar weer terug te zetten...

[Kees]

Voordeel van innodb tov myisam is dat innodb rowlevel locking doet, en myisam table locking waardor je met innodb meerdere selects naast elkaar kan draaien.

Verder is de diskcache erg belangrijk, hoe meer hij gecached heeft, des te sneller kan hij erbij. In dit geval zou ik zeker voor meer geheugen in de server gaan. Dan kan hij én complete tables in de diskcache zetten, en hij heeft meer over voor mysql. Want ik denk dat als je nu de set-vars verdubbelt hij te weinig over heeft voor zijn diskcache, en dus zwaar moet swappen / alles elke keer van de schijf af moet lezen.

Als ik op de tweakers.net servers kijk, zie ik dat meer dan 60% van het geheugen in de buffers/cache zit, als dit niet het geval zou zijn, dan zou mysql elke keer de tables van de schijf moeten lezen, en dat wil je niet

Overigens is een raid-5 voor een db ook niet de snelste oplossing, raid-1 of raid-10 presteren voor db's significant beter.