February 2014 - discuss - lists.mariadb.org

[Maria-discuss] Doubt - replication and ssh/ssl
by Roberto Spadim 19 Feb '14

19 Feb '14

Hi guys, i'm with a doubt... What is better (better = more secure, and with good compression), a ssh tunnel, or a native mariadb ssl connection between master/slave replication mariadb servers? -- Roberto Spadim SPAEmpresarial Eng. Automação e Controle

2 4

[Maria-discuss] Implementing APPROXIMATE_COUNT(*)
by Tomek R 19 Feb '14

19 Feb '14

Hi, I have realised that whenever I am doing any query and want to count number of rows:SELECT COUNT(*) FROM atable WHERE <complicated condition>; On large tables (>10e6 rows), when the count is >100e3, it takes over a second... even when all the indexes are set up, etc... and with large counts, I have not been able to go below ~<1 s. So I would like to implement APPROXIMATE_COUNT(*) function, which checks, if, for example every 10th, 100th or 1000th row is present in my final output... and based on this statistics, return the approximate number of rows that given query produces. I am using MyISAM engine and would like to do the implementation on it, where the total number of rows is automatically stored. Obviously, it would be useful for anybody dealing with these huge aggregate websites, where users do not really need to know if their keyword search returned 1234567890 results or just 1.2e9 results... Please, can you advise me how to start this project? I.e. whether it's easy to do in SQL or whether I would have to start looking somewhere in the source code of MariaDB? I have plenty of time, programming/SQL experience, but no experience with maria/mysql source code. Tomek

3 2

[Maria-discuss] New Question: Data limit on joins with connect tables?
by AskMonty KB 18 Feb '14

18 Feb '14

Hello, A new question has been asked in "Connect" by benkendrick: -------------------------------- I am attempting run a query against two tables in a remote database with an inner join. These tables reside in a Progress Database. I have used the CONNECT engine via the ODBC connection that I have set up. The first query had 24 columns returned by the query, I got 930 rows as a result. If I reduce the number of columns that I select, the number of rows returned by the database goes up. Even with only two rows select, I only get about 12,000 rows. The total result should be 14,149 rows. I have used the same ODBC connection with LibreOffice Base and received the full number of rows. If I query each table individually, I get the full number of rows (The tables have a 1 to 1 relationship with the current data). I am using MariaDB 10.0.8 32-bit on Windows 7 querying a remote Progress Database. Is there some sort of data limit? I have noticed that the total number of data points (rows multiplied by columns) is between 20,000 and 24,000. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/data-limit-on-joins-with-connect-tables/

1 0

[Maria-discuss] MySQL 5.6 optimizer trace functionality in MariaDB 10?
by Tim Callaghan 17 Feb '14

17 Feb '14

Is this feature or something similar already in MariaDB 10? If not, is it on the roadmap? http://dev.mysql.com/doc/internals/en/optimizer-tracing.html -Tim Callaghan

5 9

[Maria-discuss] Potential participation in Google Summer of Code 2014
by Colin Charles 15 Feb '14

15 Feb '14

Hi! We have applied to be part of the Google Summer of Code 2014 project, and hope to gain some new contributors. If you are on this list and think you can be a mentor for a project, please consider writing a note at: https://mariadb.com/kb/en/google-summer-of-code-2014/ I think the aim for the GSoC students code should be for 10.1 inclusion If you have any queries, don't hesitate to ask me about them, I'm happy to help. Typically mentors need about 4 hours or so per week to assist the student in seeing their project become a reality I encourage many to participate as its a rewarding experience. In 2013, we did participate, and got 3 features out of it: SQL ROLES, PCRE regular expressions, and a Kerberos authentication plugin Looking forward to seeing more happen in 2014! (here's hoping we get accepted into GSoC 2014 of course!) cheers, -colin -- Colin Charles, Chief Evangelist MariaDB | t: +6-012-204-3201 | Skype: colincharles

1 0

[Maria-discuss] MariaDB Galera Cluster 5.5.35 now available
by Daniel Bartholomew 14 Feb '14

14 Feb '14

The MariaDB project is pleased to announce the immediate availability of MariaDB Galera Cluster 5.5.35. This is a Stable (GA) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB Galera Cluster 5.5.35 - Release Notes: https://mariadb.com/kb/en/mariadb-galera-cluster-5535-release-notes - Changelog: https://mariadb.com/kb/en/mariadb-galera-cluster-5535-changelog - Downloads: https://downloads.mariadb.org/mariadb-galera/5.5.35 About MariaDB Galera Cluster - https://mariadb.com/kb/en/galera/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - SkySQL has held several MariaDB-focused Webinars recently which are available on an on-demand basis at: - http://www.skysql.com/why-skysql/webinars/ They are also planning several more MariaDB-focused webinars that will be held over the comming months. Details available at the above link. - - New MariaDB Book - - - - - - - - - - - - - - - - - - - - - - - - - A new MariaDB book has been released! Getting Started with MariaDB is a how-to guide for beginners to help them get up to speed quickly with MariaDB, no previous MariaDB or MySQL experience required. It's available from several booksellers in both paperback and DRM-free epub, mobi and pdf formats. See the PacktPub website for details: - http://www.packtpub.com/getting-started-with-mariadb/book - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/en/donate/ - http://www.cafepress.com/mariadb We hope you enjoy MariaDB! -- MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

1 0

[Maria-discuss] Very slow insert with load data infile
by Pierre 12 Feb '14

12 Feb '14

Hello, Mariadb is getting slower and slower when I'm inserting a massive amout of data. I'm trying to insert 166 507 066 rows (12go) using load data infile '' into an empty table. I splitted my file in 13 parts of the same size because it was too long to insert in one shot. When I inserted more than 100M rows, it starts to be extremely slow. Here are the time for each load data infile : LOAD DATA INFILE '/tmp/hash/xaa' : 2min 49sec LOAD DATA INFILE '/tmp/hash/xab' : 2min 49sec LOAD DATA INFILE '/tmp/hash/xac' : 3min 48sec LOAD DATA INFILE '/tmp/hash/xad' : 3min 48sec LOAD DATA INFILE '/tmp/hash/xae' : 3min 49sec LOAD DATA INFILE '/tmp/hash/xaf' : 5min 59sec LOAD DATA INFILE '/tmp/hash/xag' : 10min 50sec LOAD DATA INFILE '/tmp/hash/xah' : 20min 7sec LOAD DATA INFILE '/tmp/hash/xai' : 47min 7sec LOAD DATA INFILE '/tmp/hash/xaj' : 4 hours 1 min 9.34 sec ( ouch !) LOAD DATA INFILE '/tmp/hash/xak' : still running... Now the task is running but mariadb is only using 2% cpu and read to the disk at 2500 Kb/sec (which is slow regarding the disk performance). There is a lot of disk space. If insertion speed continue to slow down at this rate, it will take weeks to insert the 3 remainings files ! It's only 166M rows so I think it's not normal. If I disable the "unique" constraint insertion speed is great but I need to be sure there is no duplicate and I also need an index. Can someone explain me what is happening internaly and why it is so slow ? Do you think if I just create an index (not a unique index) it will be faster ? The problem is I specially delegated this task of unique checking to mysql. If no solution, do you know a database or a key/value store with better for performance for this use case ? (create an index and remove duplicate ?) I can rearrange my table structure (at a cost of more disk space usage) to match a key/value structure. Here is my current table structure : CREATE TABLE `wallets` ( `p1` varbinary(20) DEFAULT NULL, `p2` varbinary(20) DEFAULT NULL, `data` varbinary(32) DEFAULT NULL, UNIQUE KEY `p1` (`p1`), UNIQUE KEY `p2` (`p2`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED; Computer specs: 16go RAM and 4x3.1 ghz, 2x1To (with 90% unused disk space) The configuration variables (I tweaked specially for this insertion job) : key_buffer_size = 6G # MyISAM: used with insert to an empty table bulk_insert_buffer_size = 6G # MyISAM/Aria: insert to a non empty table; default 8M myisam_sort_buffer_size = 6G # MyISAM: used for sort but only when : "CREATE INDEX", "ALTER TABLE" or "REPAIR TABLE" read_buffer_size = 6G # MyISAM/Aria/MERGE: allocated for each table scan, for order by, nested query caching, bulk insert into partitions sort_buffer_size = 4M # ALL: allocated each time a session need to do a sort myisam_max_sort_file_size = 128G # MyISAM: tmp file max size I'm using mariadb 5.5.31. Best regards,

6 16

[Maria-discuss] MariaDB 5.5.35 now available
by Daniel Bartholomew 11 Feb '14

11 Feb '14

The MariaDB project is pleased to announce the immediate availability of MariaDB 5.5.35. This is a Stable (GA) maintenance (bug fix) release. See the Release Notes and Changelog for details. - - Links - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - MariaDB 5.5.35 - Release Notes: https://mariadb.com/kb/en/mariadb-5535-release-notes - Changelog: https://mariadb.com/kb/en/mariadb-5535-changelog - Downloads: https://downloads.mariadb.org/mariadb/5.5.35 About MariaDB 5.5: - https://mariadb.com/kb/en/what-is-mariadb-55/ APT and YUM Repository Configuration Generator: - https://downloads.mariadb.org/mariadb/repositories/ - - MariaDB Webinars - - - - - - - - - - - - - - - - - - - - - - - - - SkySQL has held several MariaDB-focused Webinars recently which are available on an on-demand basis at: - http://www.skysql.com/why-skysql/webinars/ They are also planning several more MariaDB-focused webinars that will be held over the comming months. Details available at the above link. - - New MariaDB Book - - - - - - - - - - - - - - - - - - - - - - - - - A new MariaDB book has been released! Getting Started with MariaDB is a how-to guide for beginners to help them get up to speed quickly with MariaDB, no previous MariaDB or MySQL experience required. It's available from several booksellers in both paperback and DRM-free epub, mobi and pdf formats. See the PacktPub website for details: - http://www.packtpub.com/getting-started-with-mariadb/book - - User Feedback plugin - - - - - - - - - - - - - - - - - - - - - - - MariaDB includes a User Feedback plugin. This plugin is disabled by default. If enabled, it submits basic, completely anonymous MariaDB usage information. This information is used by the developers to track trends in MariaDB usage to better guide development efforts. If you would like to help make MariaDB better, please add "feedback=ON" to your my.cnf or my.ini file! See http://mariadb.com/kb/en/user-feedback-plugin for more information. - - Quality - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - The project always strives for quality, but in reality, nothing is perfect. Please take time to report any issues you encounter at: - http://mariadb.org/jira - - Support MariaDB - - - - - - - - - - - - - - - - - - - - - - - - - - If you would like to contribute to the MariaDB Foundation, please see the "contributing" and "donations" pages. We also have merchandise available in a cafepress store. All proceeds go to support the MariaDB Foundation. - https://mariadb.com/kb/en/contributing - https://mariadb.org/en/donate/ - http://www.cafepress.com/mariadb We hope you enjoy MariaDB! -- MariaDB: An Enhanced Drop-in Replacement for MySQL Website - http://mariadb.org Twitter - http://twitter.com/mariadb Google+ - http://google.com/+mariadb Facebook - http://fb.com/MariaDB.dbms Knowledge Base - http://mariadb.com/kb

5 7

[Maria-discuss] New Question: ARCHIVE equivalent
by AskMonty KB 11 Feb '14

11 Feb '14

Hello, A new question has been asked in "MariaDB FAQ" by dumdummare: -------------------------------- What options are there to have a log table that is difficult to change but quick to search using criterias even during inserts? Something like an ARCHIVE with indicies or an InnoDB without UPDATE or DELETE. -------------------------------- To view or answer this question please visit: http://mariadb.com/kb/en/archive-equivalent/

4 4

[Maria-discuss] why are ipv6 tests not simply skipped?
by Reindl Harald 11 Feb '14

11 Feb '14

these tests should be skipped in case the machine has no ipv6 config instead fired up and failing __________________________________ CURRENT_TEST: rpl.rpl_ip_mix mysqltest: In included file "./include/rpl_ip_mix.inc": included from /usr/share/mysql-test/suite/rpl/t/rpl_ip_mix.test at line 11: At line 2: query 'connect master,$IPv6,root,,test,$MASTER_MYPORT' failed: 2003: Can't connect to MySQL server on '::1' (101) The result from queries just before the failure was: #################### IP: ::1 ########################### connect (master,::1,root,,test,MASTER_MYPORT); - saving '/usr/share/mysql-test/var/log/rpl.rpl_ip_mix/' to '/usr/share/mysql-test/var/log/rpl.rpl_ip_mix/' rpl.rpl_ip_mix2 [ fail ] Test ended at 2014-02-11 13:11:55 CURRENT_TEST: rpl.rpl_ip_mix2 mysqltest: In included file "./include/rpl_ip_mix2.inc": included from /usr/share/mysql-test/suite/rpl/t/rpl_ip_mix2.test at line 11: At line 4: query 'connect slave,$IPv6,root,,test,$SLAVE_MYPORT' failed: 2003: Can't connect to MySQL server on '::1' (101) The result from queries just before the failure was: #################### IP: ::1 ########################### connect (master,127.0.0.1,root,,test,MASTER_MYPORT); connect (slave,::1,root,,test,SLAVE_MYPORT); - saving '/usr/share/mysql-test/var/log/rpl.rpl_ip_mix2/' to '/usr/share/mysql-test/var/log/rpl.rpl_ip_mix2/' rpl.rpl_ipv4_as_ipv6 [ pass ] 24 rpl.rpl_ipv6 [ fail ] Test ended at 2014-02-11 13:12:00 CURRENT_TEST: rpl.rpl_ipv6 mysqltest: In included file "./include/rpl_ipv6.inc": included from /usr/share/mysql-test/suite/rpl/t/rpl_ipv6.test at line 13: At line 2: query 'connect master,$IPv6,root,,test,$MASTER_MYPORT' failed: 2003: Can't connect to MySQL server on '::1' (101) The result from queries just before the failure was: #################### IP: ::1 ########################### connect (master,::1,root,,test,MASTER_MYPORT);

2 2