[Maria-discuss] The insert performance issue
Hi ALL , I test the MariaDB performance of insert command . I use a file include 100000 insert command statements to test the insert performanc . On local server ,the Mariadb spent about 20 second to insert data;but on remote server ,the MariaDB spent about 30 second to insert data . I execute the command " mysql -h db1 -utest -ptest -D IST < insert_file.sql" on local and remote servers . Why the MariaDB has about 10 second gap ? Maybe the network should to cause some latancy ,but the time should not have so long . What issue to cause the gap ?
Am 10.03.2016 04:52, schrieb 林澤宇:
Hi ALL ,
I test the MariaDB performance of insert command . I use a file include 100000 insert command statements to test the insert performanc . On local server ,the Mariadb spent about 20 second to insert data;but on remote server ,the MariaDB spent about 30 second to insert data . I execute the command " mysql -h db1 -utest -ptest -D IST < insert_file.sql" on local and remote servers .
Why the MariaDB has about 10 second gap ? Maybe the network should to cause some latancy ,but the time should not have so long .
What issue to cause the gap ?
I have notice that mariadb (and mysql) need a lot of time for login. A noteable speedup is to use ssh+keyexchange. re, wh
Hi, Walter! On Mar 10, walter harms wrote:
Am 10.03.2016 04:52, schrieb 林澤宇:
Hi ALL ,
I test the MariaDB performance of insert command . I use a file include 100000 insert command statements to test the insert performanc . On local server ,the Mariadb spent about 20 second to insert data;but on remote server ,the MariaDB spent about 30 second to insert data . I execute the command " mysql -h db1 -utest -ptest -D IST < insert_file.sql" on local and remote servers .
Why the MariaDB has about 10 second gap ? Maybe the network should to cause some latancy ,but the time should not have so long .
What issue to cause the gap ?
I have notice that mariadb (and mysql) need a lot of time for login. A noteable speedup is to use ssh+keyexchange.
This is simply not true. MariaDB (and MySQL) authetication protocol is very light-weight, the server sends the welcome packet, the client replies with the username/password, the server sends "OK". It is certainly much cheaper than SSH. Either way, in the original question there is only one connection, and authentication - even as slow as SSH - cannot take 10 seconds. (unless he used a custom authentication plugin that does "sleep(10)") Regards, Sergei Chief Architect MariaDB and security@mariadb.org
Am 10.03.2016 09:42, schrieb Sergei Golubchik:
Hi, Walter!
On Mar 10, walter harms wrote:
Am 10.03.2016 04:52, schrieb 林澤宇:
Hi ALL ,
I test the MariaDB performance of insert command . I use a file include 100000 insert command statements to test the insert performanc . On local server ,the Mariadb spent about 20 second to insert data;but on remote server ,the MariaDB spent about 30 second to insert data . I execute the command " mysql -h db1 -utest -ptest -D IST < insert_file.sql" on local and remote servers .
Why the MariaDB has about 10 second gap ? Maybe the network should to cause some latancy ,but the time should not have so long .
What issue to cause the gap ?
I have notice that mariadb (and mysql) need a lot of time for login. A noteable speedup is to use ssh+keyexchange.
This is simply not true. MariaDB (and MySQL) authetication protocol is very light-weight, the server sends the welcome packet, the client replies with the username/password, the server sends "OK". It is certainly much cheaper than SSH.
Either way, in the original question there is only one connection, and authentication - even as slow as SSH - cannot take 10 seconds.
(unless he used a custom authentication plugin that does "sleep(10)")
I have no clue what the problem is i can only say in my context it is faster to use ssh re, wj
Le 10 Mar 2016 à 19:05, walter harms <wharms@bfs.de> a écrit :
Am 10.03.2016 09:42, schrieb Sergei Golubchik:
Hi, Walter!
On Mar 10, walter harms wrote:
Am 10.03.2016 04:52, schrieb 林澤宇:
Hi ALL ,
I test the MariaDB performance of insert command . I use a file include 100000 insert command statements to test the insert performanc . On local server ,the Mariadb spent about 20 second to insert data;but on remote server ,the MariaDB spent about 30 second to insert data . I execute the command " mysql -h db1 -utest -ptest -D IST < insert_file.sql" on local and remote servers .
Why the MariaDB has about 10 second gap ? Maybe the network should to cause some latancy ,but the time should not have so long .
What issue to cause the gap ?
I have notice that mariadb (and mysql) need a lot of time for login. A noteable speedup is to use ssh+keyexchange.
This is simply not true. MariaDB (and MySQL) authetication protocol is very light-weight, the server sends the welcome packet, the client replies with the username/password, the server sends "OK". It is certainly much cheaper than SSH.
Either way, in the original question there is only one connection, and authentication - even as slow as SSH - cannot take 10 seconds.
(unless he used a custom authentication plugin that does "sleep(10)")
I have no clue what the problem is i can only say in my context it is faster to use ssh
Hi Walter, I guess this is expected mysql client run queries one by one and will take a total amount of time just because each query add a small communication time You can fixe this with 2 options : 1 - Use batch insert to get bigger packet on network . 2 - Use more concurrency by splitting your file in peaces and run each peace in concurrency ETL like talent can make this in graphical designer. Please experiment dumping you 100K result table with mysqldump and reload the resulting file from network let us know how long it take on your hardware If it is a lot more faster, have look on how it’s done ! Keep in mind insert perf is really a matter of configuration , data safety and data size you would like to get. There is always a hardware limit and be sure that MariaDB can reach it when well tuned. Those having storage that cost more than 40K euros, we monitor internal limit at 60K IOp/s for writes:) Subliminal PS for Kristian DROP DOMAIN ! DROP DOMAIN ! Thanks Stéphane
re, wj
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
Hi, 林澤宇! On Mar 10, 林澤宇 wrote:
Hi ALL ,
I test the MariaDB performance of insert command . I use a file include 100000 insert command statements to test the insert performanc . On local server ,the Mariadb spent about 20 second to insert data;but on remote server ,the MariaDB spent about 30 second to insert data . I execute the command " mysql -h db1 -utest -ptest -D IST < insert_file.sql" on local and remote servers .
Why the MariaDB has about 10 second gap ? Maybe the network should to cause some latancy ,but the time should not have so long .
What issue to cause the gap ?
May be hostname resolution? As far as I understand the default resolver timeout is 5 seconds (man resolv.conf) - two failed name lookups gives you exactly 10 seconds. Try --skip-name-resolve on the remote mariadb server and make sure you only use IP adresses when granting privileges and when connecting to the server. Regards, Sergei Chief Architect MariaDB and security@mariadb.org
Hi , My MariaDB original setting has include "skip-name-resolve" . 2016-03-10 16:48 GMT+08:00 Sergei Golubchik <serg@mariadb.org>:
Hi, 林澤宇!
On Mar 10, 林澤宇 wrote:
Hi ALL ,
I test the MariaDB performance of insert command . I use a file include 100000 insert command statements to test the insert performanc . On local server ,the Mariadb spent about 20 second to insert data;but on remote server ,the MariaDB spent about 30 second to insert data . I execute the command " mysql -h db1 -utest -ptest -D IST < insert_file.sql" on local and remote servers .
Why the MariaDB has about 10 second gap ? Maybe the network should to cause some latancy ,but the time should not have so long .
What issue to cause the gap ?
May be hostname resolution? As far as I understand the default resolver timeout is 5 seconds (man resolv.conf) - two failed name lookups gives you exactly 10 seconds.
Try --skip-name-resolve on the remote mariadb server and make sure you only use IP adresses when granting privileges and when connecting to the server.
Regards, Sergei Chief Architect MariaDB and security@mariadb.org
林澤宇 <jerry08291021@gmail.com> writes:
I use a file include 100000 insert command statements to test the insert performanc . On local server ,the Mariadb spent about 20 second to insert data;but on remote server ,the MariaDB spent about 30 second to insert data .
Why the MariaDB has about 10 second gap ? Maybe the network should to cause some latancy ,but the time should not have so long .
Why do you think 10 seconds is unexpected? If you are sending 100000 individual queries from a single client thread, that is 100000 network roundtrips. Extra 10 seconds for that seems reasonable, in fact it sounds quite fast to me (0.1 ms network roundtrip). If you want better performance on remote server, try sending many statements in one roundtrip using multi-statement protocol, or running many queries in parallel from the client to overlap the roundtrips. - Kristian.
Hi Kristian, In local server ,I also use -h <ip> option to login my mariadb not localhost . If this ip is on local server ,does the mariadb can transfer the ip to local soket ? If not ,I think the command " mysql -h db1 -utest -ptest -D IST < insert_file.sql" should not has gap ? - Jerry. 2016-03-10 22:46 GMT+08:00 Kristian Nielsen <knielsen@knielsen-hq.org>:
林澤宇 <jerry08291021@gmail.com> writes:
I use a file include 100000 insert command statements to test the insert performanc . On local server ,the Mariadb spent about 20 second to insert data;but on remote server ,the MariaDB spent about 30 second to insert data .
Why the MariaDB has about 10 second gap ? Maybe the network should to cause some latancy ,but the time should not have so long .
Why do you think 10 seconds is unexpected?
If you are sending 100000 individual queries from a single client thread, that is 100000 network roundtrips. Extra 10 seconds for that seems reasonable, in fact it sounds quite fast to me (0.1 ms network roundtrip).
If you want better performance on remote server, try sending many statements in one roundtrip using multi-statement protocol, or running many queries in parallel from the client to overlap the roundtrips.
- Kristian.
Am 11.03.2016 um 05:10 schrieb Jerry Lin:
Hi Kristian, In local server ,I also use -h <ip> option to login my mariadb not localhost . If this ip is on local server ,does the mariadb can transfer the ip to local soket ?
no, only when you use "localhost" and not "127.0.0.1"
If not ,I think the command " mysql -h db1 -utest -ptest -D IST < insert_file.sql" should not has gap ?
and how do the data of "insert_file.sql" come to the server? besides that a local interface is always faster then network try it out - your own IP on a linux machine is handeled by the loopback device - proven by iptables rules for "-i lo" hit also when call the 192.168.2.2 address
2016-03-10 22:46 GMT+08:00 Kristian Nielsen <knielsen@knielsen-hq.org <mailto:knielsen@knielsen-hq.org>>:
林澤宇 <jerry08291021@gmail.com <mailto:jerry08291021@gmail.com>> writes:
> I use a file include 100000 insert command statements to test the insert > performanc . > On local server ,the Mariadb spent about 20 second to insert data;but on > remote server ,the MariaDB spent about 30 second to insert data .
> Why the MariaDB has about 10 second gap ? > Maybe the network should to cause some latancy ,but the time should not > have so long .
Why do you think 10 seconds is unexpected?
If you are sending 100000 individual queries from a single client thread, that is 100000 network roundtrips. Extra 10 seconds for that seems reasonable, in fact it sounds quite fast to me (0.1 ms network roundtrip).
If you want better performance on remote server, try sending many statements in one roundtrip using multi-statement protocol, or running many queries in parallel from the client to overlap the roundtrips.
participants (7)
-
Jerry Lin -
Kristian Nielsen -
Reindl Harald -
Sergei Golubchik -
Stephane VAROQUI -
walter harms -
林澤宇