MySQL config aanpassen voor grote database

[nlrobert]

Ik heb een database (MySQL, MyIsam tabellen) die dagelijks gevuld wordt met data die via xml wordt opgehaald bij diverse andere websites. Dit wordt 's nachts gedraaid dmv een cronjob. Dit werkt op zich prima. Het uiteindelijke resultaat is een tabel X met ruim 1,2 miljoen records. Om dit geschikt te maken voor de zoekmachine op de website wordt er daarna een nieuwe tabel Z gemaakt, die gegevens uit tabel X combineert met gegevens uit tabel Y en daarna een groot aantal indexen toevoegd, zodat een zoekactie in de tabel Z zeer snel uitgevoerd kan worden (zonder indexen zou het al gauw 30 seconden duren en dat is voor een productiesite natuurlijk veel te lang).

Tabel Z wordt als volgt gemaakt:

$query = "DROP TABLE IF EXISTS tabelZ";
mysql_query($query);

$query = "
CREATE TABLE 
        tabelZ 
AS SELECT 
        tabelX.x_id,
        tabelX.x_bedrag,
        tabelX.x_extra,
        tabelX.x_startdatum,
        tabelX.x_einddatum,
        tabelX.x_dag,
        tabelX.x_bedrijf,
        tabelY.y_nummer,
        tabelY.y_nummer2,
        tabelY.y_beschrijving,
        tabelY.y_plaats,
        tabelY.y_regio,
        tabelY.y_land,
        tabelY.y_minimum,
        tabelY.y_maximum,
        tabelY.y_punten
FROM
        tabelX
INNER JOIN 
        tabelY 
ON 
        tabelX.x_nummer = tabelY.y_nummer AND
        tabelX.x_bedrijf = tabelY.y_bedrijf
GROUP BY 
        tabelX.x_id
";
mysql_query($query) or print $query.mysql_error();

$query = "ALTER TABLE tabelZ MODIFY y_beschrijving CHAR(255) NOT NULL";
mysql_query($query) or print $query.mysql_error();

$query = "ALTER TABLE tabelZ ADD PRIMARY KEY (x_id)";
mysql_query($query);

$query = "ALTER TABLE tabelZ ADD INDEX (y_land)";
mysql_query($query);

$query = "ALTER TABLE tabelZ ADD INDEX (y_maximum, y_land)";
mysql_query($query);

$query = "ALTER TABLE tabelZ ADD INDEX (y_land, x_startdatum, x_dag)";
mysql_query($query);

$query = "ALTER TABLE tabelZ ADD INDEX (x_dag, y_land)";
mysql_query($query);

$query = "ALTER TABLE tabelZ ADD INDEX (y_land, x_startdatum, y_maximum)";
mysql_query($query);

$query = "ALTER TABLE tabelZ ADD INDEX (y_punten, y_land, x_startdatum, y_maximum)";
mysql_query($query);

$query = "ALTER TABLE tabelZ ADD INDEX (y_punten, x_startdatum, y_maximum)";
mysql_query($query);
   
$query = "ALTER TABLE tabelZ ADD y_soundex VARCHAR(4) NOT NULL AFTER y_plaats";
mysql_query($query);

$query = "UPDATE tabelZ SET y_soundex = SOUNDEX(y_plaats)";
mysql_query($query);

$query = "ALTER TABLE tabelZ ADD INDEX (y_soundex)";
mysql_query($query);

$query = "ALTER TABLE tabelZ ADD INDEX (y_soundex, x_startdatum, x_dag)";
mysql_query($query);

$query = "ALTER TABLE tabelZ ADD z_order TINYINT NOT NULL";
mysql_query($query);

$query = "ALTER TABLE tabelZ ADD z_show TINYINT NOT NULL";
mysql_query($query);
 
$query = "ALTER TABLE tabelZ ADD INDEX (x_startdatum, x_dag, y_land, z_show, z_order, y_nummer, x_bedrag)";
mysql_query($query);

$query = "ALTER TABLE tabelZ ADD INDEX (y_nummer)";
mysql_query($query);

$query = "ALTER TABLE tabelZ ADD INDEX (x_bedrijf)";
mysql_query($query);

Het resultaat is een tabel van weer ruim 1,2 miljoen records, met een totale datagrootte van 800 MB, waarvan zo'n 250 MB aan indexen.

Het toevoegen van al die extra indexen (en een paar extra velden, "ALTER TABLE tabel Z ADD...") kost behoorlijk wat tijd (alles bij elkaar ongeveer 1 uur), maar uiteindelijk werkt het wel.

Nu dacht ik het wat efficiënter aan te pakken:
- Ik kopiëer de structuur van tabelZ, inclusief alle indexen en extra velden (naar tabelW).
- Ik pas de "CREATE TABEL ... AS SELECT ... " aan in "INSERT INTO tabelZ SELECT ... ", waarbij ik de SELECT iets uitbreidt, zodat de extra velden ook worden gevuld, om een error te voorkomen.

$query = "
INSERT INTO
        tabelW 
SELECT 
        tabelX.x_id,
        tabelX.x_bedrag,
        tabelX.x_extra,
        tabelX.x_startdatum,
        tabelX.x_einddatum,
        tabelX.x_dag,
        tabelX.x_bedrijf,
        tabelY.y_nummer,
        tabelY.y_nummer2,
        tabelY.y_beschrijving,
        tabelY.y_plaats,
        SOUNDEX(tabelY.y_plaats),
        tabelY.y_regio,
        tabelY.y_land,
        tabelY.y_minimum,
        tabelY.y_maximum,
        tabelY.y_punten,
        0,
        0
FROM
        tabelX
INNER JOIN 
        tabelY 
ON 
        tabelX.x_nummer = tabelY.y_nummer AND
        tabelX.x_bedrijf = tabelY.y_bedrijf
GROUP BY 
        tabelX.x_id
";
mysql_query($query) or print $query.mysql_error();

Met de query is opzichzelf is niks mis (qua structuur klopt hij). Als ik hem probeer uit te voeren via de cronjob dan is hij rustig 8 uur bezig terwijl ik dan nog niets zie staan in tabelZ (via phpMyAdmin). De query staat wel in de processlist (Status "Copying to tmp table"). Uiteindelijk kill ik hem dan maar handmatig omdat anders de hele website onbereikbaar wordt.

Ik denk dat er toch iets mis is met de MySQL configuratie. Er is op de server 2 GB ram beschikbaar, waarvan MySQL zeker 1 GB zou mogen gebruiken.

Welke variabelen zou ik het beste aan kunnen passen in de configuratie? Ik zat zelf te denken aan key_buffer en misschien ook query_cache_size of query_cache_limit.

(Het gaat er dus om dat er 1x per dag een cronjob wordt uitgevoerd waarbij een grote hoeveelheid data gekopiëerd moet worden. De rest van de dag wordt de database doorzocht door bezoekers van de website en wordt er aan de database zelf niets meer veranderd).

Momenteel is de configuratie als volgt:

#:~)- cat /home/var/db/mysql/my.cnf
[mysqld]
query_cache_limit=8M
query_cache_size=96M
query_cache_type=1
thread_concurrency=2
max_connections=900
interactive_timeout=90
wait_timeout=100
connect_timeout=30
thread_cache_size=256
key_buffer = 256M
join_buffer=2M
max_allowed_packet=32M
table_cache=4096
record_buffer=2M
sort_buffer_size=2M
read_buffer_size=2M
max_connect_errors=90
myisam_sort_buffer_size=32M
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
#safe-updates
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /home/var/db/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /home/var/db/mysql/innodblogs/
innodb_log_arch_dir = /home/var/db/mysql/innodblogsarchive/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 32M
innodb_additional_mem_pool_size = 10M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 24M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50


Alvast bedankt voor het doorlezen van deze lange post en mogelijk een nuttig antwoord!

[Deimos]

Waarom heb je allereerst zo belachelijk veel indexen? Of gebruik je al die index velden voor een WHERE lookup??

[nlrobert]

Waarom heb je allereerst zo belachelijk veel indexen? Of gebruik je al die index velden voor een WHERE lookup??

Die indexen worden inderdaad gebruikt voor een WHERE lookup. De website bestaat eigenlijk uit een grote zoekmachine, waarbij bezoekers op allerlei manieren kunnen zoeken. Denk aan een bepaald maximum bedrag, zelf gekozen startdatum, einddatum, regio, land, minimum aantal punten en ook zoeken op plaatsnaam (dit wordt gerealiseerd m.b.v. het SOUNDEX() veld).

Meestal wordt gezocht op een combinatie van de zojuist genoemde velden, maaar het kan ook zijn dat er alleen maar gezocht moet worden op een bepaald land of een ingevoerd nummer (nummer of nummer2).

Zodra die indexen er eenmaal zijn gaat het zoeken razendsnel (gaat al snel 100x zo snel als zonder indexen).

[V. Kleijnendorst]

Interessant

Ik heb zelf een vergelijkbare situatie, met dit verschil dat de cronjob bij mij elke 15 minuten loopt.

Ik heb de indices op twee tabellen in plaats van een extra tabel. Bij een zoekopdracht left join ik de twee tabellen op elkaar en ik heb geen last van slechte performance.

Als je toch gebruik maakt van een aparte zoektabel (wat niet eens zo gek is), dan is het nogal een dure operatie om deze elke keer weer van de grond of op te bouwen. Waarom niet in de tabellen een datetime kolom opnemen en vervolgens alleen gewijzigde records / nieuwe records in de zoektabel wijzigen / verwijderen?

Ik zou er sowieso voor kiezen om de indexen aan te maken vóórdat je de tabel vult.

Eventueel kun je nog kijken naar INSERT DELAYED

[nlrobert]

Als je toch gebruik maakt van een aparte zoektabel (wat niet eens zo gek is), dan is het nogal een dure operatie om deze elke keer weer van de grond of op te bouwen. Waarom niet in de tabellen een datetime kolom opnemen en vervolgens alleen gewijzigde records / nieuwe records in de zoektabel wijzigen / verwijderen?

Ik zou er sowieso voor kiezen om de indexen aan te maken vóórdat je de tabel vult.

Eventueel kun je nog kijken naar INSERT DELAYED

Om de tabel compleet opnieuw op te bouwen is inderdaad een redelijk 'dure' operatie. Ik probeer het nu dus ook met een tabel waarin de indexen al staan.
Probleem is dat die query zo ontzettend lang duurt, dat er wel iets gewijzigd zal moeten worden in de config, om die query soepel te laten verlopen.

De mogelijkheid met een extra datetime veld vergt ook weer aanpassingen aan andere tabellen. Het cronjob script is geschreven door een andere programmeur, maar het moet nog veel efficiënter kunnen. Probleem is dat er momenteel andere zaken een hogere prioriteit hebben, en ik dus geen tijd heb om het hele cronjob script te herschrijven en verbeteren.

INSERT DELAYED is geen optie, omdat de zoektabel gewoon zo snel mogelijk up2date moet zijn. Als alles door elkaar heen gaat lopen ben ik nog verder van huis en kan ik het beter zo laten als het nu is.

[maxnet]

Een aantal indexen zijn redudant.
Bijvoorbeeld:

ALTER TABLE tabelZ ADD INDEX (y_soundex)
ALTER TABLE tabelZ ADD INDEX (y_soundex, x_startdatum, x_dag)

Een kenmerk van een BTREE index over meerdere velden is dat deze ook gebruikt kan worden als alleen op het meest linker veld gezocht wordt.

Voor bijv. de query "SELECT * FROM tabelZ WHERE y_soundex='H123'" kan ook de index (y_soundex, x_startdatum, x_dag) gebruikt worden, en heb je de index (y_soundex) dus niet nodig.

De query cache wordt alleen gebruikt om de resultaten van hele SELECT queries te onthouden, en niet voor onderdelen van queries.
Zie: http://dev.mysql.com/doc/refman/4.1/...cache-how.html
Zou dan ook eerder de sort_buffer + key_buffer ophogen dan de query_cache ruimte.


Verder zou je eens kunnen kijken of het nog snelheidsverschil uitmaakt als je wel alle extra velden in de tabel meteen aanmaakt, maar de indexen pas later.

[nlrobert]

Een aantal indexen zijn redudant.
Bijvoorbeeld:

ALTER TABLE tabelZ ADD INDEX (y_soundex)
ALTER TABLE tabelZ ADD INDEX (y_soundex, x_startdatum, x_dag)

Een kenmerk van een BTREE index over meerdere velden is dat deze ook gebruikt kan worden als alleen op het meest linker veld gezocht wordt.

Voor bijv. de query "SELECT * FROM tabelZ WHERE y_soundex='H123'" kan ook de index (y_soundex, x_startdatum, x_dag) gebruikt worden, en heb je de index (y_soundex) dus niet nodig.

De query cache wordt alleen gebruikt om de resultaten van hele SELECT queries te onthouden, en niet voor onderdelen van queries.
Zie: http://dev.mysql.com/doc/refman/4.1/...cache-how.html
Zou dan ook eerder de sort_buffer + key_buffer_size ophogen dan de query_cache ruimte.


Verder zou je eens kunnen kijken of het nog snelheidsverschil uitmaakt als je wel alle extra velden in de tabel meteen aanmaakt, maar de indexen pas later.

Bedankt voor de info over de BTREE indexen, dan kan ik in ieder geval een (paar) index(en) verwijderen.

Wat betreft de query cache: ik zit nu te kijken naar tmp_table_size en join_buffer_size. Deze staan nu ingesteld op 33M respectievelijk 2M. Ik denk dat het wel behoorlijk uit maakt als ik die aan laat passen.

[nlrobert]

Eventueel kun je nog kijken naar INSERT DELAYED

Ik zie zojuist in de MySQL manual staan:
"DELAYED is ignored with INSERT ... SELECT."
http://dev.mysql.com/doc/refman/5.0/...rt-select.html

Die vlieger gaat dus zowiezo niet op

[V. Kleijnendorst]

Ik zie zojuist in de MySQL manual staan:
"DELAYED is ignored with INSERT ... SELECT."
http://dev.mysql.com/doc/refman/5.0/...rt-select.html

Niet in de huidige vorm nee. Als je een zoektabel hebt die behouden blijft en alleen voorzien wordt van nieuwe wijzigingen, dan kun je dat via een script doen. Op dat moment heb je vaste waarden voor de insert en kun je gebruik maken van INSERT DELAYED. Dat kán voordelen opleveren omdat een select (een bezoeker) voorrang krijgt op de insert, waardoord de website niet merkbaar vertraagd.