- discuss - lists.mariadb.org

[Maria-discuss] The insert performance issue
by 林澤宇 11 Mar '16

11 Mar '16

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 ?

7 9

Re: [Maria-discuss] The insert performance issue
by Federico Razzoli 11 Mar '16

11 Mar '16

That could be a problem if DNS is not reliable, but I never heard of a 10 seconds DNS request. I'd strongly recommend to assign more memory to the buffer pool. If InnoDB is the main storage engine you use, 1.5G should be ok. Swap is evil for a database, so you should make sure that loading a dump does not cause swapping. Federico -------------------------------------------- Gio 10/3/16, Jerry Lin <jerry08291021(a)gmail.com> ha scritto: Oggetto: Re: [Maria-discuss] The insert performance issue A: "Sergei Golubchik" <serg(a)mariadb.org> Cc: maria-discuss(a)lists.launchpad.net Data: Giovedì 10 marzo 2016, 11:20 Hi ,My MariaDB original setting has include "skip-name-resolve" . 2016-03-10 16:48 GMT+08:00 Sergei Golubchik <serg(a)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(a)mariadb.org -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

3 2

[Maria-discuss] New Question: Table and Column
by AskMonty KB 11 Mar '16

11 Mar '16

Hello, A new question has been asked in "MariaDB Community" by Redman Companies. Please answer it at http://mariadb.com/kb/en/table-and-column/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I have created a new table called 'B' using a table called 'A'. My question is, how do I create three new columns in table B and populate them with data from table A? Your help would be greatly appreciated. Thank you in advance. (See example below) Ex.tables--- A ------------ B columns a, a1, a2... b , b1, b2... c, c1, c2... x, y, z, a b c a1 b1 c1 a2 b2 c2 . . . a8 b8 c8 Put all of the data from a...a8 into column x line by line Put all of the data from b..b8. into column y line by line Put all of the data from c...c8 into column z line by line -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/table-and-column/

1 0

[Maria-discuss] R: The insert performance issue
by Federico Razzoli 10 Mar '16

10 Mar '16

林澤宇, there is clearly some difference between your servers. Same version? Same storage engine (InnoDB)? Same indexes? Memory is full, maybe swapping? If all these things are ok, check your settings (SHOW VARIABLES). Federico -------------------------------------------- Gio 10/3/16, 林澤宇 <jerry08291021(a)gmail.com> ha scritto: Oggetto: [Maria-discuss] The insert performance issue A: maria-discuss(a)lists.launchpad.net Data: Giovedì 10 marzo 2016, 04:52 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 ? -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

2 1

[Maria-discuss] R: The insert performance issue
by Federico Razzoli 10 Mar '16

10 Mar '16

林澤宇, there is clearly some difference between your servers. Same version? Same storage engine (InnoDB)? Same indexes? Memory is full, maybe swapping? If all these things are ok, check your settings (SHOW VARIABLES). Federico -------------------------------------------- Gio 10/3/16, 林澤宇 <jerry08291021(a)gmail.com> ha scritto: Oggetto: [Maria-discuss] The insert performance issue A: maria-discuss(a)lists.launchpad.net Data: Giovedì 10 marzo 2016, 04:52 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 ? -----Segue allegato----- _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss(a)lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp

1 0

[Maria-discuss] Question about MariaDB global reference In high complexity app and big DB Size
by Seung Hoon Yoo 09 Mar '16

09 Mar '16

hi I am some question, i want some opinion below question We planning ERP solution convert oracle to mariaDB so erp application pattern and BIG DB SIZING IS ISSUE. Our IT background & goal 1) Apppliciation Complexity & Pattern: ERP Level complexity (high , OLTP & many batch) 2) DB SIZE : 10 TB Over (Single DB , NO sharding) 3) replication Gap issue ** My Question ** 1) Any Global reference is in MariaDB (on ERP level complexity and application pattern) 2) Any some reference in BIG DATA SIZING ? (EX: 5TB DB Exists in XX COMPARY e-commerce soluntion..) IS Only solution is DB Sharding? no big data global reference ?? 3) Replication performance : Our test result 2MB/SEC binlog written in master , so slave node get replication lag glorwing. (in row binlog format) ( 1master - 1 slave ) Any Body have experience some reference max transaction in no replication gap ? ( ex : 20MB / SEC but no replication gap our application experience..) : parellel slave thread is good performance in single domain-id and ordered method replicaiton ? ( how much better performance in parallel replication? (ex single repliation 2MB/SEC BINLOG => Replication GAP GROW but, parellel replicaiton is good in 10MB/Sec binlog write Environment) thanks a lot. regards, seung hoon yoo.

3 6

[Maria-discuss] New Question: SST smethod: rsync_wan
by AskMonty KB 09 Mar '16

09 Mar '16

Hello, A new question has been asked in "MariaDB Galera Cluster" by gijs. Please answer it at http://mariadb.com/kb/en/sst-smethod-rsync_wan/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I'm looking for an option to make my SST faster. I tried using xtrabackup-v2 but this currently doesn't support GTID clusters. I read that there is a rsync SST method that is customized for WAN usage. It makes use of delta transfers to reduce the data that has to be send over the WAN. I've tried: wsrep_sst_method=rsync-wan wsrep_sst_method=rsync_wan But neither of them work, nor can I find any official documentation about this feature for MariaDB. I did find: http://galeracluster.com/documentation-webpages/statetransfer.html http://galeracluster.com/documentation-webpages/sst.html http://galeracluster.com/documentation-webpages/mysqlwsrepoptions.html#wsre… https://blog.adminfactory.net/mariadb-and-galera-on-aws-amazon-linux.html My question is how can I enable the delta transfer for rsync with MariaDB 10.1.12? If this is not possible with MariaDB I'd like to know if there are any plans to implement it and if there are any plans to make xtrabackup-v2 support GTID clusters. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/sst-smethod-rsync_wan/

1 0

[Maria-discuss] New Question: Is Aria a good choice in environments where power will often fail?
by AskMonty KB 08 Mar '16

08 Mar '16

Hello, A new question has been asked in "Aria" by thomas_gawehn. Please answer it at http://mariadb.com/kb/en/is-aria-a-good-choice-in-environments-where-power-… as the person asking the question may not be subscribed to the mailing list. -------------------------------- Hi all, the Aria storage engine is described to be "crash safe". Would this mean that Aria is a good choice in production environments where the system power is expected to fail very often? Think about small embedded systems which are run without UPS? What will happen when power fails right whilst a record being updated or inserted and the corresponding I/O is right written to the disk at that moment? Would the database then come up corrupt after the next boot, with manuel assistance being neccessary -or- will the database be fine in general, and only the last record / the last change / last operation vanishes? Always presumed, the LOCK TABLES command is sent directly after each data manipulation command. What about possible operating system or hard disk "write behand" caches? Thanks and best regards Thomas -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/is-aria-a-good-choice-in-environments-where-power-…

1 0

[Maria-discuss] New Question: MariaDB SSL connection with Connector/J Setup help
by AskMonty KB 08 Mar '16

08 Mar '16

Hello, A new question has been asked in "SSL Connections" by og_116262525349832924122. Please answer it at http://mariadb.com/kb/en/mariadb-ssl-connection-with-connectorj-setup-help/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- I just installed MariaDB 10.1.11 on Centos 7 and enabled SSL connections. I created the root CA, Server Cert/Key, & Client Cert/Key as spelled out in the instructions from this link http://xmodulo.com/enable-ssl-mysql-server-client.html (used -sha256 instead of -sha1) I added the variables in the [mysqld] section of the server.cnf file and restarted MariaDB I created a user and granted them privilages with a "require x509" suffix. I have 2 users created (nossluser, ssluser) grant all on *.* to nossluser; grant all on *.* to ssluser require X509; I can login from the command line using MySQL without any problems MySQL -u nossluser -p <--- works fine with the user that has no ssl requirement MySQL -u ssluser -p --ssl-cert client-cert.pem --ssl-key client-key.pem --ssl-ca ca-cert.pem <-- this works fine I can also connect from a remote client using MySQL workbench using the same ca-cert.pem, client-cert.pem, client-key.pem as I used on the command line. My problem is when I try to create a java client connection using the Mariadb connector/j I have tried issuing the connection using the useSSL=true, requireSSL=true, serverSslCert=server-cert.pem but it does not work. I get the: "Could not connect: Access denied for user 'ssluser'@192.168.1.9' (using password: YES)" error message I also enabled java.net.debug and I get a message in the log that says: "Warning:no suitable certificate found - continuing without client authentication" Now if I change the user to "require ssl" instead of "require x509" The connection works, but I still see the java net debug warning no suitable certificate found which I assume means that the SSL connection did not work but it allowed the connection anyway. Any Ideas on the correct connection settings for the connector/j Thanks, Willie -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/mariadb-ssl-connection-with-connectorj-setup-help/

1 0

[Maria-discuss] New Question: CentOS v6 glibc 2.12 default but Tokudb need 2.14
by AskMonty KB 08 Mar '16

08 Mar '16

Hello, A new question has been asked in "TokuDB" by yngve.eriksson_39408. Please answer it at http://mariadb.com/kb/en/centos-v6-glibc-212-default-but-tokudb-need-214/ as the person asking the question may not be subscribed to the mailing list. -------------------------------- What is the best way to install glibc 2.14 ? so many tell me not to mess with glibc version or CentosOS is unstable ..... is it included in CentOS 7 ? is that the way to go ? or how do I install glibc 2.14 ? Where do I get it from .... yum -UHv xxxxx_glibc-2.14.rpm ??? -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/centos-v6-glibc-212-default-but-tokudb-need-214/

1 0