Pomalé inserty v MySQL

Zdavim, chtěl bych vás moc poprosit o radu. Mám v tabulce cca 40tis. záznamů a jako engine jsem používal myisam. Při vložení dalšího záznamu nebo updatu trvala operace přes minutu a ještě to zahltilo celou db. Nyní jsem zkusil předělat tabulku na innodb, ale problém s insertem a updatem je podobný (navíc trochu víc narůstaly pomalé selecty). Tak se chci zeptat co mám udělat jiného, případně co jak nastavit, aby to šlapalo pořádně. Už jsem i tabulku rozdělil - místo 30 sloupců na 12 a 18. Jediné co se zlepšílo je hodnota Table_locks_waited - nezamyká se tabulka Proměnné pro innodb mám: innodb_buffer_pool_size = 3G innodb_additional_mem_pool_size = 30M innodb_data_file_path = ibdata1:20M:autoextend innodb_log_file_size = 300M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 Předem moc děkuji...

Záznamů tam máš málo, v tom problém nebude. Jaké jsou následné kroky? Zkoušel jsi pustit samotný INSERT přes phpMyAdmin nebo nějak podobně? Trvalo to stejně dlouho? (To abys vyloučil špatně napsaný script co na DB sahá).Jak jsou na tom indexy nad touhle tabulkou? Kolik jich máš? Nejsou na tom pověšené nějaké triggery?

Pouze první INSERT po dlouhé době způsobí zahlcení db. Pak je jistě tabulka v cache a nasledující INSERTy jsou v pořádku. Přes phpmyadmina ještě zkusim, až nebude dlouho nic vloženo. Tiggery nejsou a tabulka má 5 klíčů: 1x primary(id), 1xunique, 3xindex. Spojuje se (JOIN LEFT) s další innodb tabulkou (ta má jen 1xPRIMARY) a s myisam tabulkou pro fulltext - tabulka jen s PRIMARY (ID) + FULLTEXT(varchar 250) - ta tabulka s fulltextem se JOINUJE jen při vyhledávání a zapisuje se do ni při INSERTu, takže podezřívám že to bude touto tabulkou, ale logu pomalých dotazů insert do ni neni..Foreign key zatim nepoužívam.

Jak říká Standa, ukažte výsledky SHOW CREATE TABLE `tablename`; SHOW INDEXES FROM `tablename`; a SHOW TRIGGERS FROM `tablename`; Edit: a znění toho INSERTu. InnoDB hodnotu Table_locks_waited nikdy nezvyšuje.

Struktura tabulek a provedení INSERTu:CREATE TABLE `user_info` (`id` int(6) unsigned NOT NULL auto_increment,`name` varchar(250) character set cp1250 NOT NULL,`category` smallint(5) unsigned NOT NULL,`post` varchar(250) collate cp1250_czech_cs NOT NULL,`p1` varchar(250) collate cp1250_czech_cs NOT NULL,`p2` varchar(250) collate cp1250_czech_cs NOT NULL,`p3` varchar(250) collate cp1250_czech_cs NOT NULL,`p4` varchar(250) collate cp1250_czech_cs NOT NULL,`p5` varchar(250) collate cp1250_czech_cs NOT NULL,`p6` varchar(250) collate cp1250_czech_cs NOT NULL,`p7` varchar(250) collate cp1250_czech_cs NOT NULL,`p8` varchar(250) collate cp1250_czech_cs NOT NULL,`p9` varchar(250) collate cp1250_czech_cs NOT NULL,`p10` varchar(250) collate cp1250_czech_cs NOT NULL,`num` int(15) unsigned NOT NULL,`work` tinyint(5) unsigned NOT NULL,`rating_count` int(10) NOT NULL default '0',`rating_value` int(10) NOT NULL default '0',`active` tinyint(1) NOT NULL default '0',`typ` bigint(14) NOT NULL,PRIMARY KEY (`id`),UNIQUE KEY `post` (`post`),KEY `work` (`work`),KEY `num` (`num`),KEY `index_categ` (`category`)) ENGINE=InnoDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_csCREATE TABLE `user_spec` (`id` int(6) unsigned NOT NULL auto_increment,`p11` varchar(250) collate cp1250_czech_cs NOT NULL,`p12` varchar(250) collate cp1250_czech_cs NOT NULL,`p13` varchar(250) collate cp1250_czech_cs NOT NULL,`p14` varchar(250) collate cp1250_czech_cs NOT NULL,`p15` varchar(250) collate cp1250_czech_cs NOT NULL,`p16` varchar(250) collate cp1250_czech_cs NOT NULL,`p17` varchar(250) collate cp1250_czech_cs NOT NULL,`p18` varchar(250) collate cp1250_czech_cs NOT NULL,`p19` varchar(250) collate cp1250_czech_cs NOT NULL,`p20` varchar(250) collate cp1250_czech_cs NOT NULL,`text` text collate cp1250_czech_cs NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_csCREATE TABLE `user_name` (`id` int(6) unsigned NOT NULL auto_increment,`name` varchar(250) character set cp1250 NOT NULL,PRIMARY KEY (`id`),FULLTEXT KEY `name` (`name`)) ENGINE=MyISAM AUTO_INCREMENT=69683 DEFAULT CHARSET=cp1250 COLLATE=cp1250_czech_csINSERT:$set=mysql_query("INSERT INTO user_info VALUES('',trim('$name'),'$categ',trim('$post'),trim('$p1'),trim('$p2'),trim('$p3'),trim('$p4'),trim('$p5'),trim('$p6'),trim('$p7'),trim('$p8'),trim('$p9'),trim('$p10'),'','','','',2,'$typ')");if ($set) {$idSpec=mysql_insert_id();$set1=mysql_query("INSERT INTO user_spec VALUES('".$idSpec."',trim('$p11a'),trim('$p12a'),trim('$p13a'),trim('$p14a'),trim('$p15a'),trim('$p16a'),trim('$p17a'),trim('$p18a'),trim('$p19a'),trim('$p20a'),trim('$text'))");$set1=mysql_query("INSERT INTO user_name VALUES('".$idSpec."',trim('$name'))");}

Ty tabulky jsem si nasimuloval a vložil do nich několik desítek tisíc náhodných řádků, ale nic se nezpomalilo. Ani okem v té struktuře nevidím nic problematického. Zkusíte přímo v kódu změřit, co konkrétně trvá tak dlouho? Jaký konkrétní dotaz, nebo jaká část kódu? Stačí nějaká jednoduchá funkce jako function stopwatch($flag = 'NA') { static $starttime; if(empty($starttime)) { $starttime = microtime(true); echo "

Stopwatch started: $starttime

"; return; } $endtime = microtime(true); $measuredtime = $endtime - $starttime; echo "

$flag: $measuredtime

"; flush(); $starttime = microtime(true); }

Pánové tipnul bych si, že střílíte vedle, tohle spíš vypadá na problém se samotným serverem. Málo paměti (swap, ucpané io) nebo jinak poddimenzovaný hw...

..jestli mas pristup do shellu tak pouzij program "atop" a ten ti okamzite ukaze kde to vazne.

Jak jsem psal, pokud se provádí INSERT chvíli po sobě (třeba do hodiny), tak INSERT proběhne bleskově. V případě, že se provede další INSERT po několika hodinách od posledního INSERTu či UPDATU, zatíží mi celou db (a nasledný INSERT opět bude v pořádku). Je možný, že se při prvnim INSERTu tabulky nahrávají do cache a po čase se z paměti vymazají?V databázi je dalších 30 tabulek a jedna z nich má 5mil. záznamů.Stále jsem to tipoval na špatné nastavení proměnných mysql, ale zkusil jsem toho tolik, že už jsem začal chybu hledat jinde.Martine S., zítra vyzkoušim vaši funkci pro změření, dnes už na monitor moc nevidim... Díky.Toshi, v tomto samozřejmě také vidím příčinu problému, ale rád bych železo pořešil už opravdu jako poslední možnost. Běží to na serveru s 8GB RAM, AMD Opteron 146. Swap je v pořádku. Vytížení procesoru nějaké je, ne max., ale od doby co jsem změnil tabulky na innodb tak je vytížení CPU vyšší.---------- Příspěvek doplněn 05.01.2011 v 01:27 ----------to NextClick: zítra se po atopu porozhlédnu, dosud jsem o tomto programu neslyšel. Díky.

Napsal lukal;611294

Toshi, v tomto samozřejmě také vidím příčinu problému, ale rád bych železo pořešil už opravdu jako poslední možnost. Běží to na serveru s 8GB RAM, AMD Opteron 146. Swap je v pořádku. Vytížení procesoru nějaké je, ne max., ale od doby co jsem změnil tabulky na innodb tak je vytížení CPU vyšší.

Aha tak to jsem čekal spíš nějaké VPS. Paměti bude imho dost, spíš jaké jsou tam disky? A takto to funguje už od začátku nebo se to začalo zpožďovat za provozu?Innodb je obecně náročnější protože kontroluje i relace mezi záznamy.

toshi: disky tam jsou 2x 500 GB SATA-II v RAID1. Od začátku s tím problém nebyl. Pomalejší insert byl, ale nezahltilo to tolik db. Tragicky pomalý to začalo asi před 2 měs., řádky tam narůstají nepravidelně, ale tak cca 100 záznamů denně.NextClick: vystačil bych si s příkazem top? Pokud ano, tak myslím, že bych tam viděl jen vytížení u mysqld a víc nic. Nebo se mýlim?Martin Schlemmer: pokusím se někdy vystihnout pro změření první insert. Jen ten to zahlcuje. Dnes už se INSERT provedl několikrát a tak mi to vypsalo:Stopwatch started: 1294241286.81: 0.05215096473691: 0.0118880271912

Pokud ti to zahlcuje první insert (jak předpokládáš), tak je možné, že ve skutečnosti ti to zahlcuje něco úplně jiného a pomalý insert s kterým máš problém je až důsledek. I pokud v MySQL měříš pomalé Query stane se že se změří nějaké SQL které samo o sobě jde rychle, ale následkem přetížení db / uzamčení tabulky / jiného problematického SQL se právě až to následné ukazuje jako dlouho trvající. Ideální bys měl zjistit všechny query co běžely nějakou dobu před tím problematickým.

na 100% to způsobuje první insert. V logu pomalých dotazů se za den objeví pár dotazů v odlišném čase, které trvají do 5s, avšak pouze ve chvili, kdy se provede první insert, je před tímto insertem v logu vypsaných i několik desítek dotazů (trvající 1 - 2 min, u dalších dotazů se v této době nenaváže spojení s db) v témže čase.Vypadá to, že mi nezbyde nic jiného než investice do výkonnějšího serveru, ale pořád doufam, že se to dá vyřešit i jinak. Vy tedy při desítkách tisíc a více řádků + indexy v mysql s inserty problém nemáte, ikdyž se insert provede jednou za čas?

Napsal lukal;611524

Vypadá to, že mi nezbyde nic jiného než investice do výkonnějšího serveru, ale pořád doufam, že se to dá vyřešit i jinak. Vy tedy při desítkách tisíc a více řádků + indexy v mysql s inserty problém nemáte, ikdyž se insert provede jednou za čas?

No jestli chceš srovnání - tak já mám dnes už podprůměrné dvoujádro, 4G ram s 200 různě využívanými a různě prasácky navrženými databázemi. V nejaktivnější databázi tabulka o 25ti sloupcích s 500 tis. zázn., 7 indexů., a pořád přibývá. Takže to co popisuješ ty má k normalitě opravdu hodně daleko, byť i na slabším stroji. "i několik desítek dotazů (trvající 1 - 2 min", neběhají ti tam na pozadí nějaké mohutné selecty, které listují celou tabulku nebo tak něco (sql SHOW PROCESSLIST, nebo v phpmyadminovi záložka procesy)? Mimochodem všechny errorlogy a syslogy si prolezl?

Hm, na jednu stranu mi to potěšilo (nejspíš nemusim kupovat nový server), na druhou ne (problém stále nevyřešen). Jaký engine prosím tě máš u té tabulky? Používáš ten stroj jen na mysql nebo tam běží i apache, mail atd.? Sledoval jsem ted chvíli výsledky po příkazu "top" (obdoba co mi poradil NextClick) a vypisuje se tam hromada apache2, kde některý z nich chvilkově zabírá i 25% CPU. Mysqld se drží jako první s 50-60% CPU.V processlistu se mi zobrazují dotazy skoro pokaždý, někdy jich tam vidim i 8 a často u nich bývá Copying to tmp table. Dotazy jsem se snažil optimalizovat jako první, víc zoptimalizovat se mi je nepodařilo.errorlogy jsou prázdný. Do syslogu jsem se ted podíval, ale popravdě, ani nevim co tam přesně hledat.Co nějaká chyba v my.cnf?:key_buffer = 2560Mmax_allowed_packet = 32Mthread_stack = 128Kthread_cache_size = 8table_cache = 2200tmp_table_size = 3800Mmax_heap_table_size = 3600Mread_rnd_buffer_size = 4Msort_buffer_size = 120Mft_min_word_len = 2query_cache_limit = 1048576query_cache_limit = 3145728query_cache_size = 1936Mquery_cache_type = 1innodb_buffer_pool_size = 4Ginnodb_additional_mem_pool_size = 30Minnodb_data_file_path = ibdata1:20M:autoextendinnodb_log_file_size = 300Minnodb_log_buffer_size = 8Minnodb_flush_log_at_trx_commit = 1innodb_lock_wait_timeout = 50