Hi, sorry to bother you with this, but one thing one might look into is:
When table was dumped with --extended-insert option, that the server importing the table does NOT perform well. In the output from `vmstat 1` each second there's a lot of I/O, and at least on linux 2.4.19-pre1 it runs more easily out of memory. On the other hand, when omitting --extended-insert, I get: 1 0 0 19520 9552 19216 922272 0 0 0 4692 1852 3323 23 10 67 1 0 0 19520 9460 19220 922300 4 0 4 0 1850 3385 27 6 67 1 0 0 19520 9472 19220 922260 0 0 0 0 1789 3206 21 8 71 1 0 0 19520 9440 19220 922240 0 0 0 0 2040 3879 33 7 60 1 0 0 19520 9440 19220 922216 0 0 0 0 1994 3747 26 7 67 1 0 0 19520 9504 19264 922064 0 0 0 3668 1784 3268 22 7 70 0 0 0 19520 9520 19264 922004 0 0 0 0 1810 3323 28 10 62 1 0 0 19520 9516 19268 921956 0 0 0 0 2125 3105 25 6 68 1 0 0 19520 9536 19268 921892 0 0 0 0 2096 3136 23 6 71 1 0 0 19520 9440 19268 921964 0 0 0 0 2076 3066 25 6 69 1 0 0 19520 9552 19312 921756 0 0 0 3796 2081 3105 24 8 68 procs memory swap io system cpu r b w swpd free buff cache si so bi bo in cs us sy id 1 0 0 19520 9564 19316 921700 0 0 0 0 2012 3097 25 8 67 1 0 0 19520 9456 19316 921760 0 0 0 0 1992 3140 24 7 69 1 0 0 19520 9468 19316 921712 0 0 0 0 1835 3171 26 12 61 1 0 0 19520 9472 19316 921680 0 0 0 0 2032 3287 18 5 76 0 0 0 19520 9508 19364 921544 0 0 0 3796 2375 3221 23 9 68 0 0 0 19520 9448 19364 921564 0 0 0 0 2424 3187 23 9 68 0 0 0 19520 9508 19364 921460 0 0 0 0 2467 3199 22 9 68 0 0 0 19520 9516 19364 921416 0 0 0 0 2207 3301 23 7 69 0 0 0 19520 9484 19364 921404 0 0 0 0 1879 3327 30 7 63 1 0 0 19520 9524 19412 921276 0 0 0 4316 1874 3294 24 8 68 1 0 0 19520 9692 19408 921068 0 0 0 0 1822 3389 26 7 67 0 0 0 19520 9608 19408 921228 0 0 4 0 2002 3623 29 9 62 1 0 0 19516 9832 19240 921256 0 0 0 0 1826 3385 26 8 66 1 0 0 19516 9764 18904 921636 0 0 0 0 1794 3348 26 8 66 1 0 0 19516 9976 18232 922060 0 0 0 4316 2358 3489 21 9 70 0 0 0 19736 10544 17592 922160 0 0 0 0 2253 3470 26 4 70 I do not want to stop my import script to send you the output what happens in case 1 above. Sorry, that's just a note. So just time to time we read data(and/or) write them. I guess read is via network. Checking iostat output tells me, that every about 4 seconds a lot of data is written to the disk and zero activity at all in between. ----------- Second point would be, that there's a lot of table locks issued when importing table. It looks every row equals to one lock ( I use mysqldump | mysql ) to import data. The shell command is executed on remote host, I'm talking about mysqld on target, not source. And, once I have seen: +----+----------+----------------+-------------------------------+---------+------+--------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | |State | Info | | +----+----------+----------------+-------------------------------+---------+------+--------+------------------------------------------------------------------------------------------------------+ | 4 | pedant | xxxxxxxxxxxxxx | Bordetella_pertussis_Tohama_I | Query | 0 | |Locked | INSERT INTO `blast` VALUES (42171,5097,0,'BP','orf188','PIR:A49936',319,'9e-86',39,54,56,595,540,550 | | 7 | bioadmin | 127.0.0.1 | | Query | 0 | | | show processlist | | +----+----------+----------------+-------------------------------+---------+------+--------+------------------------------------------------------------------------------------------------------+ I can exclude the possibility that someone else had locked this table temporarily as the traffic on server who query usually this box is redirected to another db server. My question is initiated because the number of Table_locks_immediate is growing very fast while importing data. Maybe both my question are poor misunderstanding. Please Cc: me in replies. TIA. (tested on Linux 2.4.19-pre1 and 3.23.49a) -- Martin Mokrejs - PGP5.0i key is at http://www.natur.cuni.cz/~mmokrejs MIPS / Institute for Bioinformatics <http://mips.gsf.de> GSF - National Research Center for Environment and Health Ingolstaedter Landstrasse 1, D-85764 Neuherberg, Germany tel.: +49-89-3187 3616 , fax: +49-89-3187 3585 --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
