February 2018 - discuss - lists.mariadb.org

[Maria-discuss] [MARIADB 5.5] Profiling truncated
by Fabien CLERC 12 Feb '18

12 Feb '18

Hello, I'm trying to profile a very long query in production (MariaDB 5.5) using "set session profiling = 1" but when I run the command "show profile for query 1", there is only the only 100 last steps of the query. It's the same checking in "INFORMATION_SCHEMA.PROFILING" I understand the parameter "profiling_history_size" is the maximum number of queries and not the maximum steps stored fora query. => How can I change the number of steps available in the result of the profiling ? Thanks and Regards *Fabien CLERC* <https://www.facebook.com/Yieloo-184624595039051/?fref=ts> <https://twitter.com/intraknow> <https://plus.google.com/+Intraknow?hl=fr> fabien.clerc(a)yieloo.com ____________________________________________________________ *Editeur de la solution intra'know* Espace performance 3 - Bâtiment Z - 35769 Saint-Grégoire www.intraknow.com <https://www.intra-know.com/yieloo/admin/cciaux/modif/www.intraknow.com> 02 99 55 15 03

2 1

[Maria-discuss] Clarification about have_ssl and have_openssl variables
by Peter Laursen 11 Feb '18

11 Feb '18

MySQL documentation states https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar… simply states "This variable is an alias for have_ssl.". I checked with a few Oracle/MySQL installations that the two variables have identical values. But this is not the case on MariaDB (I checked my local Windows installs of 10.1 and 10.2). have_openssl is NO - have_ssl is YES|DISABLED. We believe that this is the reason for a newly reported problem to us: SSL-connections is not possibe to MariaDB on Amazon RDS. We verified the problem with a fresh RDS instance with MariaDB 10.2 and running with SSL enabled. The 3 different 'mysql' CLI's give results: *MariaDB 10.2.6 version:* - without ssl, gives 'Access denied error' - with ssl, it gives Error No. 2026: SSl Connection error: Certificate signature check failed *using MySQL 5.7.x version:* it connects to the server irrespective of the ssl parameter passed or not (but we don't know if SSL is actually used. It may silently connect without SSL if SSL is not possible) *MariaDB 10.1.22 version:* without ssl: it gives 'Access denied error' with ssl (Works as expected) Any thoughts on this? What can we do? We are affected as we use MariaDB Connector/C 2.33 currently -- Peter -- Webyog

2 1

[Maria-discuss] MariaDB server 10.1.31 a little question ?
by Ljr Yang 08 Feb '18

08 Feb '18

Hi, There two little qustion in MariaDB 10.1.31, please confirm, thanks. 1) After install MariaDB 10.1.31 , in error log show xtradb version 5.6.36-83.0 ? it should 5.6.38-83.0 2018-02-07 19:07:47 140196300970016 [Note] InnoDB: Percona XtraDB ( http://www.percona.com) 5.6.36-83.0 started; log seque nce number 6162730 2) in the web page , show " Download MariaDB Server 10.2.10" , it should "10.0.34 Galera cluster", https://mariadb.com/resources/blog/mariadb-server-10131-and-mariadb-galera-…

2 1

[Maria-discuss] MariaDB threads optimalization
by Brano Gege 07 Feb '18

07 Feb '18

Hi All, I got problem with mariadb performance, NEW (pcie) server: Intel(R) Xeon(R) CPU E5-2637 v4 @ 3.50GHz, 1TB NVMe disks, 128 GB RAM, installed Debian 4.9.65-3+deb9u1, Ver 15.1 Distrib 10.1.26-MariaDB OLD server: Intel(R) Xeon(R) CPU E5-1630 v3 @ 3.70GHz, SSD disk, 64 GB RAM, FreeBSD 11.0-STABLE, 10.1.21-MariaDB On servers is running just mysql, I copy my.ini file, config files are same. mysql slap results for example employees db: #less /root/slap/select_query.sql SELECT emp_no, first_name, last_name, gender FROM employees LIMIT 10; SELECT emp_no, first_name, last_name, gender FROM employees ORDER BY last_name ASC LIMIT 10; SELECT COUNT(emp_no) FROM employees WHERE last_name = 'Aamodt'; SELECT last_name, COUNT(emp_no) AS num_emp FROM employees GROUP BY last_name ORDER BY num_emp DESC LIMIT 10; SELECT employees.* FROM employees LEFT JOIN dept_emp ON ( dept_emp.emp_no = employees.emp_no ) LEFT JOIN salaries ON ( salaries.emp_no = salaries.emp_no ) WHERE employees.first_name LIKE '%Jo%' AND salaries.from_date > '1993-01-21' AND salaries.to_date < '1998-01-01' LIMIT 0, 100; root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=1 Average number of seconds to run all queries: 0.459 seconds DB1: Average number of seconds to run all queries: 0.627 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=2 Benchmark Average number of seconds to run all queries: 0.473 seconds DB1: Average number of seconds to run all queries: 0.626 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=4 Average number of seconds to run all queries: 0.486 seconds DB1: Average number of seconds to run all queries: 0.656 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=8 Average number of seconds to run all queries: 0.569 seconds DB1: Average number of seconds to run all queries: 1.136 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=16 Benchmark Average number of seconds to run all queries: 0.948 seconds DB1: Average number of seconds to run all queries: 1.750 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=32 Average number of seconds to run all queries: 1.650 seconds DB1: Average number of seconds to run all queries: 2.455 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=64 Average number of seconds to run all queries: 3.306 seconds DB1: Average number of seconds to run all queries: 3.176 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=128 Average number of seconds to run all queries: 6.744 seconds DB1: Average number of seconds to run all queries: 5.737 seconds root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=256 Average number of seconds to run all queries: 13.474 seconds (verified 2nd run: 12.883 seconds) DB1: Average number of seconds to run all queries: 3.451 seconds (verified 2nd run: 4.935 seconds) root@pcie:~/slap# mysqlslap --pre-query="RESET QUERY CACHE;" --create-schema=employees --query="/root/slap/select_query.sql" --iterations=10 --concurrency=512 Average number of seconds to run all queries: 26.085 seconds (verified 2nd run: 26.307 seconds) DB1: Average number of seconds to run all queries: 15.862 seconds (verified 2nd run: 11.280 seconds) as you can see with raising threads PCIE performance is lowering rapidly. Any idea what to check? Thank you. I post this question couple of days with more information also here: https://serverfault.com/questions/895506/mariadb-threads-optimalization

4 7

[Maria-discuss] Triangular FKs - Cascade delete leaves DB in state with broken referential integrity
by Oliver Schonrock 05 Feb '18

05 Feb '18

I have come across a very strange symptom with triangular FKs (see below) where a cascade delete of the central (member) record leaves the DB in a state with broken referential integrity. The FK structure is a little bit demanding and somewhat "triangular/circular", but has been in operation in our application for many years without problems. After recent upgrade from 10.1 to 10.2.12, we promptly got broken FKs. (I have not yet retested with 10.1). How to reproduce: (example reduced to the bare essentials with 3 tables with 1 record each). SET FOREIGN_KEY_CHECKS=0; CREATE TABLE member ( id int(11) NOT NULL AUTO_INCREMENT, default_address_id int(11) DEFAULT NULL, default_card_id int(11) DEFAULT NULL, PRIMARY KEY (id), KEY member_FI_2 (default_address_id), KEY member_FI_3 (default_card_id), CONSTRAINT member_FK_2 FOREIGN KEY (default_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT member_FK_3 FOREIGN KEY (default_card_id) REFERENCES payment_method (id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO member VALUES (1,2,3); CREATE TABLE address ( id int(11) NOT NULL AUTO_INCREMENT, member_id int(11) NOT NULL, PRIMARY KEY (id), KEY address_FI_1 (member_id), CONSTRAINT address_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO address VALUES (2,1); CREATE TABLE payment_method ( id int(11) NOT NULL AUTO_INCREMENT, member_id int(11) NOT NULL, cardholder_address_id int(11) DEFAULT NULL, PRIMARY KEY (id), KEY payment_method_FI_1 (member_id), KEY payment_method_FI_2 (cardholder_address_id), CONSTRAINT payment_method_FK_1 FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT payment_method_FK_2 FOREIGN KEY (cardholder_address_id) REFERENCES address (id) ON DELETE SET NULL ON UPDATE CASCADE ) ENGINE=InnoDB; INSERT INTO payment_method VALUES (3,1,2); SET FOREIGN_KEY_CHECKS=1; SELECT * FROM member; +----+--------------------+-----------------+ | id | default_address_id | default_card_id | +----+--------------------+-----------------+ | 1 | 2 | 3 | +----+--------------------+-----------------+ SELECT * FROM address; +----+-----------+ | id | member_id | +----+-----------+ | 2 | 1 | +----+-----------+ SELECT * FROM payment_method; +----+-----------+-----------------------+ | id | member_id | cardholder_address_id | +----+-----------+-----------------------+ | 3 | 1 | 2 | +----+-----------+-----------------------+ DELETE FROM member WHERE id = 1; SELECT * FROM member; [ empty ] SELECT * FROM address; [ empty ] SELECT * FROM payment_method; +----+-----------+-----------------------+ | id | member_id | cardholder_address_id | +----+-----------+-----------------------+ | 3 | 1 | NULL | +----+-----------+-----------------------+ ------------ DB is now in an inconsistent state: payment_method.member_id=1 references a non-existent member record. Am I missing something, or should this never happen? Thanks -- Oliver Schönrock

1 2