maria-discuss team mailing list archive

[Stephane VAROQUI <stephane@xxxxxxxxxxx>]

> Le 10 Mar 2016 à 19:05, walter harms <wharms@xxxxxx> 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@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp