Dotaz: záťaž > nice --19 > INSERT INTO maintab2 SELECT * FROM tab1 až tab21

[dusan456]

Zdravím,

mám hlavnú maintab2, ktorá ma 2GB a 3,5 milióna záznamov, nemá index, nepoužíva sa.

Raz mesačne ju potrebujem aktualizovať z tab1 až tab21, tiež nemajú indexy.

TRUNCATE TABLE maintab2;

INSERT INTO maintab2 SELECT * FROM tab1
--až
INSERT INTO maintab2 SELECT * FROM tab21

po skopírovaní približne prvej tab1 sa to strašne spomalí, všetkým týmto procesom som dal pritom nice --19 a aj napriek tomu je veľká záťaž, úplne sa spomalí apache, web skoro vôbec nefunguje, až kým tieto údaje sa neskopírujú. Potom sa web rozbehne a všetko beží zase rýchlo. Cele to trvá niekoľko minút.

Neviete prosím poradiť, ako by sa to dalo urobiť bez toho, aby sa takto spomalil web server?

---

---

Odpovědi

[d.c.]

Linux? BSD? UNIX?

Je dost pravdepodobne, ze nejde o prilisne zatizeni procesoru (takze nice nepomuze), ale o spatnou reakci i/o subsystemu. Muze pomoci vylepsit i/o (disky/radic...), lepsi nastaveni prace s pameti v SQL serveru, prip. pridani pameti. Muze pomoci ionice(1). Samozrejme zbyva spousta otazek (treba jestli web server neceka na SQL). Mozna by bodnul UN*Xovy spravce

[dusan456]

Debian Squezee

Záťaž procesoru je v poriadku. RAM je 4x1GB.

# cat my.cnf 
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0

[mysqld]
#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql
tmpdir		= /tmp
language	= /usr/share/mysql/english
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 127.0.0.1
#
# * Fine Tuning
#
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit	= 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error logging goes to syslog due to /etc/mysql/conf.d/mysqld_safe_syslog.cnf.
#
# Here you can see queries with especially long duration
#log_slow_queries	= /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id		= 1
#log_bin			= /var/log/mysql/mysql-bin.log
expire_logs_days	= 10
max_binlog_size         = 100M
#binlog_do_db		= include_database_name
#binlog_ignore_db	= include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

[mysql]
#no-auto-rehash	# faster start of mysql but no tab completition

[isamchk]
key_buffer		= 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

[Marek]

S nejvetsi pravdepodobnosti je problem s diskem, ktery nestiha cist/zapisovat. U podobneho typu dotazu nezalezi na CPU. Zkuste to overit treba jak je navrhovano zde.

Jak zrychlit server? Zkuste to cele davkovat - treba rozdelit podle poctu zaznamu v tabulkach jednotlive dotazy tak, se kopirovaly po mensim poctu zaznamu s prestavkami. Sice tim uplne nevyresite problem s IO, ale alespon muze v mezicasech server zpracovavat dalsi veci. Nevyhoda je pak zpomaleni celeho procesu.

[AraxoN]

nice, ani ionice Ti nepomôže. V procese, ktorý je pod nice sa len prepošle SQL príkaz databázovému serveru. To znamená, že pôvodný proces vlastne nič nerobí, len čaká na odpoveď databázového serveru. To svoje čakanie robí s nízkou prioritou, ale to je tak všetko. Až databázový server vykonáva na procesore a diskoch prácu s daným SQL príkazom. Databázový server beží so svojou nezmenenou prioritou. A aj keby si mu prioritu znížil, nepomôžeš si, lebo sa to bude týkať aj všetkých ostatných SQL príkazov, ktoré súbežne s tým databáza vykonáva, takže to pôjde ostatným rovnako pomaly ako predtým.

Obísť by sa to mohlo dať tak, že dané INSERT-y sa rozbijú na menšie časti a medzi spustením jednotlivých príkazov sa chvíľku počká, aby mali šancu dobehnúť príkazy ostatných používateľov:

INSERT INTO maintab2 SELECT * FROM tab1 ORDER BY ... LIMIT 1000 OFFSET 0;
sleep(1);
INSERT INTO maintab2 SELECT * FROM tab1 ORDER BY ... LIMIT 1000 OFFSET 1000;
sleep(1);
INSERT INTO maintab2 SELECT * FROM tab1 ORDER BY ... LIMIT 1000 OFFSET 2000;
sleep(1);
...

[rADOn]

Pokud je to MySQL, misto sleepu staci pouzit INSERT LOW_PRIORITY …

[jekub]

nešlo by pro maintab2 místo tabulky použít pohled?

[jano]

skusil by som to uzavriet do transakcie ak sa tam vytvaraaju indexi...

[mess]

... bez indexů ...

Problém bude v I/O operacích disku. S tím se moc nedá dělat, pokud do toho nechceš investovat prachy.

[VM]

Když to nemá index a nepoužívá se, proč se to musí aktualizovat? Pokud se jedná o zálohu, nebylo by lepší vykopírovat data na jiný fyzický disk, čímž by se vyřešilo to zahlcení I/O operacemi?

[dusan456]

Ďakujem všetkým za info.

Rozhodli sme sa zobrať novú vykonnejšiu mašinu, dúfam, že to pomôže celkovo, nielen v tomto prípade.

Tiež mám ináč tušenie, že to je do veľkej miery v HDD, lebo atop mi už dlhšiu dobu ukazuje 99% vyťaženie a to aj keď tieto nárazové operácie vôbec nerobím.

To, že sa tieto tabulky nepoužívajú, som mal na mysli, že v tom danom momente insertu nie sú používané, ináč potom samozrejme áno.

[Tomáš]

DB výkonnost je obvykle limitována především rychlostí a množsvím IO operací (s diskem), nikoliv CPU výkonností. Snaha je, aby IO operace (zápisy na disk) byly co nejvíce seskupené, aby hlavička disku se nemusela zbytečně hýbat a mohli se projevit optimalizace pro minimalizaci pohybu hlaviček na disku (sekvenční zápis, NCQ, ...).

Při zápisu se obvykle musí najít volné místo (blok) na disku, do kterého se data zapíší. Fungující cache pro hledání volného místa k zápisu a bufferování zápisu je základem pro efektivní hromadný zápis.

Hledání google: innodb insert hned druhý odkaz Something awesome in InnoDB, dává info o tomto problému.

Zkusil bych zvětšit innodb_change_buffer_max_size, buffery a cache, tak aby se celá cílová tabulka vešla do paměti (2GB není zase tak moc). Bude to útok na alokovanou paměť. Ale přitom systém (OS) nesmí swapovat, jinak bude Vaše snaha swapováním OS pohřbena.

Moc MySQL neznám, ale PK typu auto increment by možná taky mohl pomoci. Bohužel netuším jak se MySQL chová k tabulkám typu multiset(bag).

Jdu si nalít další sklenku. Je pozdě večer. Berte moje rady s rezervou.

[Aleš Janda]

Technická: nice --19 nastavuje prioritu -19, což je druhá nejvyšší. Kdyby to tedy fungovalo tak jak jsi očekával, server by přestal reagovat úplně. Zřejmě jsi měl na mysli prioritu 19, což se nastavuje přes nice -19 (pouze jedno minusko).

[Kit]

Priorita -19 se mu povede pouze za předpokladu, že je šílenec a spouští uvedené příkazy jako root.

[dusan456]

mal som na mysli 19, čiže low priority, pomylil som sa.

[cronin]

Zmena prebieha v transakcii? Ak áno, nie je možné robiť commit po každej tabuľke? Ak to ide rýchlo a potom to spomalí, pravdepodobne to prerástlo pamäť a db to musí začať písať do temporary tabuliek na disku.

[Michal Buchtík]

Takže len na vysvetlenie, celé tie problémy boli spôsobené starým hardware.

Máme nový stroj, problémy zmizli, rýchlosť normálna, ako má byť.

Ponaučenie, že z h.... koláč neupečieš.

[l0gik]

Neslo by zmenit tabulku maintab2 na typ MERGE a data proste takto nekopirovat?

môj blog

[Kit]

Vzhledem k tomu, že se tabulka maintab2 nepoužívá, možná by stačil dump - třeba tabulky typu MERGE, ať to má pohromadě.