- developers - lists.mariadb.org

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (monty:2829)
by Michael Widenius 10 Mar '10

10 Mar '10

#At lp:maria based on revid:knielsen@knielsen-hq.org-20100310103214-4ao08thvv174zwv6 2829 Michael Widenius 2010-03-10 [merge] Automatic merge modified: mysql-test/r/foreign_key.result mysql-test/t/foreign_key.test sql/sql_delete.cc === modified file 'mysql-test/r/foreign_key.result' --- a/mysql-test/r/foreign_key.result 2001-09-28 05:05:54 +0000 +++ b/mysql-test/r/foreign_key.result 2010-03-10 13:39:02 +0000 @@ -1,4 +1,4 @@ -drop table if exists t1; +drop table if exists t1,t2; create table t1 ( a int not null references t2, b int not null references t2 (c), @@ -13,3 +13,30 @@ foreign key (a,b) references t3 (c,d) on create index a on t1 (a); create unique index b on t1 (a,b); drop table t1; +create table t1 (id int primary key) engine = innodb; +create table t2 (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES t1(id)) engine=innodb; +insert into t1 values (1), (2), (3), (4), (5), (6); +insert into t2 values (3), (5); +delete from t1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)) +select * from t1; +id +1 +2 +3 +4 +5 +6 +delete ignore from t1; +Warnings: +Error 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)) +Error 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)) +select row_count(); +row_count() +-1 +select * from t1; +id +3 +5 +drop table t2; +drop table t1; === modified file 'mysql-test/t/foreign_key.test' --- a/mysql-test/t/foreign_key.test 2005-07-28 00:22:47 +0000 +++ b/mysql-test/t/foreign_key.test 2010-03-10 13:39:02 +0000 @@ -2,8 +2,10 @@ # Test syntax of foreign keys # +-- source include/have_innodb.inc + --disable_warnings -drop table if exists t1; +drop table if exists t1,t2; --enable_warnings create table t1 ( @@ -23,3 +25,23 @@ create unique index b on t1 (a,b); drop table t1; # End of 4.1 tests + +# +# Test DELETE IGNORE +# Bug#44987 DELETE IGNORE and FK constraint +# + +create table t1 (id int primary key) engine = innodb; +create table t2 (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES t1(id)) engine=innodb; +insert into t1 values (1), (2), (3), (4), (5), (6); +insert into t2 values (3), (5); + +--error 1451 +delete from t1; +select * from t1; + +delete ignore from t1; +select row_count(); +select * from t1; +drop table t2; +drop table t1; === modified file 'sql/sql_delete.cc' --- a/sql/sql_delete.cc 2010-03-04 08:03:07 +0000 +++ b/sql/sql_delete.cc 2010-03-10 13:55:40 +0000 @@ -335,8 +335,11 @@ bool mysql_delete(THD *thd, TABLE_LIST * InnoDB it can fail in a FOREIGN KEY error or an out-of-tablespace error. */ - error= 1; - break; + if (!select_lex->no_error) + { + error= 1; + break; + } } } else

1 0

[Maria-developers] bzr commit into MariaDB 5.1, with Maria 1.5:maria branch (monty:2826) Bug#44987
by Michael Widenius 10 Mar '10

10 Mar '10

#At lp:maria based on revid:monty@askmonty.org-20100309192224-ycijqc2xxv8v9dai 2826 Michael Widenius 2010-03-10 Fix for: Bug#44987 DELETE IGNORE and FK constraint - Now DELETE IGNORE skips over rows with a foreign key constraints (as it was supposed to do) modified: mysql-test/r/foreign_key.result mysql-test/t/foreign_key.test sql/sql_delete.cc per-file messages: mysql-test/r/foreign_key.result Test case for Bug#44987 DELETE IGNORE and FK constraint mysql-test/t/foreign_key.test Test case for Bug#44987 DELETE IGNORE and FK constraint sql/sql_delete.cc Firx for Bug#44987 DELETE IGNORE and FK constraint Now DELETE IGNORE skips over rows with a foreign key constraints (as it was supposed to do) Bug fix inspired by: Moritz Mertinkat === modified file 'mysql-test/r/foreign_key.result' --- a/mysql-test/r/foreign_key.result 2001-09-28 05:05:54 +0000 +++ b/mysql-test/r/foreign_key.result 2010-03-10 13:39:02 +0000 @@ -1,4 +1,4 @@ -drop table if exists t1; +drop table if exists t1,t2; create table t1 ( a int not null references t2, b int not null references t2 (c), @@ -13,3 +13,30 @@ foreign key (a,b) references t3 (c,d) on create index a on t1 (a); create unique index b on t1 (a,b); drop table t1; +create table t1 (id int primary key) engine = innodb; +create table t2 (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES t1(id)) engine=innodb; +insert into t1 values (1), (2), (3), (4), (5), (6); +insert into t2 values (3), (5); +delete from t1; +ERROR 23000: Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)) +select * from t1; +id +1 +2 +3 +4 +5 +6 +delete ignore from t1; +Warnings: +Error 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)) +Error 1451 Cannot delete or update a parent row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`id`) REFERENCES `t1` (`id`)) +select row_count(); +row_count() +-1 +select * from t1; +id +3 +5 +drop table t2; +drop table t1; === modified file 'mysql-test/t/foreign_key.test' --- a/mysql-test/t/foreign_key.test 2005-07-28 00:22:47 +0000 +++ b/mysql-test/t/foreign_key.test 2010-03-10 13:39:02 +0000 @@ -2,8 +2,10 @@ # Test syntax of foreign keys # +-- source include/have_innodb.inc + --disable_warnings -drop table if exists t1; +drop table if exists t1,t2; --enable_warnings create table t1 ( @@ -23,3 +25,23 @@ create unique index b on t1 (a,b); drop table t1; # End of 4.1 tests + +# +# Test DELETE IGNORE +# Bug#44987 DELETE IGNORE and FK constraint +# + +create table t1 (id int primary key) engine = innodb; +create table t2 (id int PRIMARY KEY, FOREIGN KEY (id) REFERENCES t1(id)) engine=innodb; +insert into t1 values (1), (2), (3), (4), (5), (6); +insert into t2 values (3), (5); + +--error 1451 +delete from t1; +select * from t1; + +delete ignore from t1; +select row_count(); +select * from t1; +drop table t2; +drop table t1; === modified file 'sql/sql_delete.cc' --- a/sql/sql_delete.cc 2010-02-10 19:06:24 +0000 +++ b/sql/sql_delete.cc 2010-03-10 13:39:02 +0000 @@ -335,8 +335,11 @@ bool mysql_delete(THD *thd, TABLE_LIST * InnoDB it can fail in a FOREIGN KEY error or an out-of-tablespace error. */ - error= 1; - break; + if (!select_lex->no_error) + { + error= 1; + break; + } } } else

1 0

[Maria-developers] Updated (by Serg): MySQL/Maria Wait Interface (102)
by worklog-noreply＠askmonty.org 10 Mar '10

10 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: MySQL/Maria Wait Interface CREATION DATE..: Wed, 10 Mar 2010, 10:53 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: Sergei CATEGORY.......: Client-BackLog TASK ID........: 102 (http://askmonty.org/worklog/?tid=102) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Serg - Wed, 10 Mar 2010, 12:41)=-=- Observers changed: Sergei -=-=(Shinguz - Wed, 10 Mar 2010, 12:41)=-=- High Level Description modified. --- /tmp/wklog.102.old.2095 2010-03-10 12:41:15.000000000 +0000 +++ /tmp/wklog.102.new.2095 2010-03-10 12:41:15.000000000 +0000 @@ -10,6 +10,9 @@ http://www.xaprb.com/blog/2009/11/07/a-review-of-optimizing-oracle-performa… http://www.facebook.com/note.php?note_id=355839540932 +Possibly the MySQL performance schema can cover part/all of it yet? +http://dev.mysql.com/doc/performance-schema/en/index.html + Good sources to start with: http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/0072… http://books.google.ch/books?id=14OmJzfCfXMC&pg=PA6&lpg=PA6&dq=owi+is+a+per… -=-=(Shinguz - Wed, 10 Mar 2010, 10:55)=-=- Title modified. --- /tmp/wklog.102.old.29019 2010-03-10 10:55:18.000000000 +0000 +++ /tmp/wklog.102.new.29019 2010-03-10 10:55:18.000000000 +0000 @@ -1 +1 @@ -MySQL Wait Interface +MySQL/Maria Wait Interface -=-=(Shinguz - Wed, 10 Mar 2010, 10:55)=-=- Version updated. --- /tmp/wklog.102.old.29019 2010-03-10 10:55:18.000000000 +0000 +++ /tmp/wklog.102.new.29019 2010-03-10 10:55:18.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +Server-9.x DESCRIPTION: What lacks in MySQL is something similar like the Oracle Wait Interface. The basic idea is, that every connection/thread (and the whole system) shows (on request) where it spent how much time doing what. They implemented the wait interface (which is possibly a sub-set of Sun's dtrace) where you can see where you loose your time. Marc Callaghan and Baron Schwartz blogged in this direction recently: http://www.xaprb.com/blog/2009/11/07/a-review-of-optimizing-oracle-performa… http://www.facebook.com/note.php?note_id=355839540932 Possibly the MySQL performance schema can cover part/all of it yet? http://dev.mysql.com/doc/performance-schema/en/index.html Good sources to start with: http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/0072… http://books.google.ch/books?id=14OmJzfCfXMC&pg=PA6&lpg=PA6&dq=owi+is+a+per… http://www.oracle.com/technology/books/pdfs/sample%20chapter%202729x.pdf http://www.amazon.com/Optimizing-Oracle-Performance-Cary-Millsap/dp/0596005… ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Serg): MySQL/Maria Wait Interface (102)
by worklog-noreply＠askmonty.org 10 Mar '10

10 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: MySQL/Maria Wait Interface CREATION DATE..: Wed, 10 Mar 2010, 10:53 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: Sergei CATEGORY.......: Client-BackLog TASK ID........: 102 (http://askmonty.org/worklog/?tid=102) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Serg - Wed, 10 Mar 2010, 12:41)=-=- Observers changed: Sergei -=-=(Shinguz - Wed, 10 Mar 2010, 12:41)=-=- High Level Description modified. --- /tmp/wklog.102.old.2095 2010-03-10 12:41:15.000000000 +0000 +++ /tmp/wklog.102.new.2095 2010-03-10 12:41:15.000000000 +0000 @@ -10,6 +10,9 @@ http://www.xaprb.com/blog/2009/11/07/a-review-of-optimizing-oracle-performa… http://www.facebook.com/note.php?note_id=355839540932 +Possibly the MySQL performance schema can cover part/all of it yet? +http://dev.mysql.com/doc/performance-schema/en/index.html + Good sources to start with: http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/0072… http://books.google.ch/books?id=14OmJzfCfXMC&pg=PA6&lpg=PA6&dq=owi+is+a+per… -=-=(Shinguz - Wed, 10 Mar 2010, 10:55)=-=- Title modified. --- /tmp/wklog.102.old.29019 2010-03-10 10:55:18.000000000 +0000 +++ /tmp/wklog.102.new.29019 2010-03-10 10:55:18.000000000 +0000 @@ -1 +1 @@ -MySQL Wait Interface +MySQL/Maria Wait Interface -=-=(Shinguz - Wed, 10 Mar 2010, 10:55)=-=- Version updated. --- /tmp/wklog.102.old.29019 2010-03-10 10:55:18.000000000 +0000 +++ /tmp/wklog.102.new.29019 2010-03-10 10:55:18.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +Server-9.x DESCRIPTION: What lacks in MySQL is something similar like the Oracle Wait Interface. The basic idea is, that every connection/thread (and the whole system) shows (on request) where it spent how much time doing what. They implemented the wait interface (which is possibly a sub-set of Sun's dtrace) where you can see where you loose your time. Marc Callaghan and Baron Schwartz blogged in this direction recently: http://www.xaprb.com/blog/2009/11/07/a-review-of-optimizing-oracle-performa… http://www.facebook.com/note.php?note_id=355839540932 Possibly the MySQL performance schema can cover part/all of it yet? http://dev.mysql.com/doc/performance-schema/en/index.html Good sources to start with: http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/0072… http://books.google.ch/books?id=14OmJzfCfXMC&pg=PA6&lpg=PA6&dq=owi+is+a+per… http://www.oracle.com/technology/books/pdfs/sample%20chapter%202729x.pdf http://www.amazon.com/Optimizing-Oracle-Performance-Cary-Millsap/dp/0596005… ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Serg): MySQL/Maria Wait Interface (102)
by worklog-noreply＠askmonty.org 10 Mar '10

10 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: MySQL/Maria Wait Interface CREATION DATE..: Wed, 10 Mar 2010, 10:53 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: Sergei CATEGORY.......: Client-BackLog TASK ID........: 102 (http://askmonty.org/worklog/?tid=102) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Serg - Wed, 10 Mar 2010, 12:41)=-=- Observers changed: Sergei -=-=(Shinguz - Wed, 10 Mar 2010, 12:41)=-=- High Level Description modified. --- /tmp/wklog.102.old.2095 2010-03-10 12:41:15.000000000 +0000 +++ /tmp/wklog.102.new.2095 2010-03-10 12:41:15.000000000 +0000 @@ -10,6 +10,9 @@ http://www.xaprb.com/blog/2009/11/07/a-review-of-optimizing-oracle-performa… http://www.facebook.com/note.php?note_id=355839540932 +Possibly the MySQL performance schema can cover part/all of it yet? +http://dev.mysql.com/doc/performance-schema/en/index.html + Good sources to start with: http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/0072… http://books.google.ch/books?id=14OmJzfCfXMC&pg=PA6&lpg=PA6&dq=owi+is+a+per… -=-=(Shinguz - Wed, 10 Mar 2010, 10:55)=-=- Title modified. --- /tmp/wklog.102.old.29019 2010-03-10 10:55:18.000000000 +0000 +++ /tmp/wklog.102.new.29019 2010-03-10 10:55:18.000000000 +0000 @@ -1 +1 @@ -MySQL Wait Interface +MySQL/Maria Wait Interface -=-=(Shinguz - Wed, 10 Mar 2010, 10:55)=-=- Version updated. --- /tmp/wklog.102.old.29019 2010-03-10 10:55:18.000000000 +0000 +++ /tmp/wklog.102.new.29019 2010-03-10 10:55:18.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +Server-9.x DESCRIPTION: What lacks in MySQL is something similar like the Oracle Wait Interface. The basic idea is, that every connection/thread (and the whole system) shows (on request) where it spent how much time doing what. They implemented the wait interface (which is possibly a sub-set of Sun's dtrace) where you can see where you loose your time. Marc Callaghan and Baron Schwartz blogged in this direction recently: http://www.xaprb.com/blog/2009/11/07/a-review-of-optimizing-oracle-performa… http://www.facebook.com/note.php?note_id=355839540932 Possibly the MySQL performance schema can cover part/all of it yet? http://dev.mysql.com/doc/performance-schema/en/index.html Good sources to start with: http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/0072… http://books.google.ch/books?id=14OmJzfCfXMC&pg=PA6&lpg=PA6&dq=owi+is+a+per… http://www.oracle.com/technology/books/pdfs/sample%20chapter%202729x.pdf http://www.amazon.com/Optimizing-Oracle-Performance-Cary-Millsap/dp/0596005… ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Shinguz): MySQL/Maria Wait Interface (102)
by worklog-noreply＠askmonty.org 10 Mar '10

10 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: MySQL/Maria Wait Interface CREATION DATE..: Wed, 10 Mar 2010, 10:53 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 102 (http://askmonty.org/worklog/?tid=102) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Shinguz - Wed, 10 Mar 2010, 12:41)=-=- High Level Description modified. --- /tmp/wklog.102.old.2095 2010-03-10 12:41:15.000000000 +0000 +++ /tmp/wklog.102.new.2095 2010-03-10 12:41:15.000000000 +0000 @@ -10,6 +10,9 @@ http://www.xaprb.com/blog/2009/11/07/a-review-of-optimizing-oracle-performa… http://www.facebook.com/note.php?note_id=355839540932 +Possibly the MySQL performance schema can cover part/all of it yet? +http://dev.mysql.com/doc/performance-schema/en/index.html + Good sources to start with: http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/0072… http://books.google.ch/books?id=14OmJzfCfXMC&pg=PA6&lpg=PA6&dq=owi+is+a+per… -=-=(Shinguz - Wed, 10 Mar 2010, 10:55)=-=- Title modified. --- /tmp/wklog.102.old.29019 2010-03-10 10:55:18.000000000 +0000 +++ /tmp/wklog.102.new.29019 2010-03-10 10:55:18.000000000 +0000 @@ -1 +1 @@ -MySQL Wait Interface +MySQL/Maria Wait Interface -=-=(Shinguz - Wed, 10 Mar 2010, 10:55)=-=- Version updated. --- /tmp/wklog.102.old.29019 2010-03-10 10:55:18.000000000 +0000 +++ /tmp/wklog.102.new.29019 2010-03-10 10:55:18.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +Server-9.x DESCRIPTION: What lacks in MySQL is something similar like the Oracle Wait Interface. The basic idea is, that every connection/thread (and the whole system) shows (on request) where it spent how much time doing what. They implemented the wait interface (which is possibly a sub-set of Sun's dtrace) where you can see where you loose your time. Marc Callaghan and Baron Schwartz blogged in this direction recently: http://www.xaprb.com/blog/2009/11/07/a-review-of-optimizing-oracle-performa… http://www.facebook.com/note.php?note_id=355839540932 Possibly the MySQL performance schema can cover part/all of it yet? http://dev.mysql.com/doc/performance-schema/en/index.html Good sources to start with: http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/0072… http://books.google.ch/books?id=14OmJzfCfXMC&pg=PA6&lpg=PA6&dq=owi+is+a+per… http://www.oracle.com/technology/books/pdfs/sample%20chapter%202729x.pdf http://www.amazon.com/Optimizing-Oracle-Performance-Cary-Millsap/dp/0596005… ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Shinguz): MySQL/Maria Wait Interface (102)
by worklog-noreply＠askmonty.org 10 Mar '10

10 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: MySQL/Maria Wait Interface CREATION DATE..: Wed, 10 Mar 2010, 10:53 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 102 (http://askmonty.org/worklog/?tid=102) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Shinguz - Wed, 10 Mar 2010, 12:41)=-=- High Level Description modified. --- /tmp/wklog.102.old.2095 2010-03-10 12:41:15.000000000 +0000 +++ /tmp/wklog.102.new.2095 2010-03-10 12:41:15.000000000 +0000 @@ -10,6 +10,9 @@ http://www.xaprb.com/blog/2009/11/07/a-review-of-optimizing-oracle-performa… http://www.facebook.com/note.php?note_id=355839540932 +Possibly the MySQL performance schema can cover part/all of it yet? +http://dev.mysql.com/doc/performance-schema/en/index.html + Good sources to start with: http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/0072… http://books.google.ch/books?id=14OmJzfCfXMC&pg=PA6&lpg=PA6&dq=owi+is+a+per… -=-=(Shinguz - Wed, 10 Mar 2010, 10:55)=-=- Title modified. --- /tmp/wklog.102.old.29019 2010-03-10 10:55:18.000000000 +0000 +++ /tmp/wklog.102.new.29019 2010-03-10 10:55:18.000000000 +0000 @@ -1 +1 @@ -MySQL Wait Interface +MySQL/Maria Wait Interface -=-=(Shinguz - Wed, 10 Mar 2010, 10:55)=-=- Version updated. --- /tmp/wklog.102.old.29019 2010-03-10 10:55:18.000000000 +0000 +++ /tmp/wklog.102.new.29019 2010-03-10 10:55:18.000000000 +0000 @@ -1 +1 @@ -Benchmarks-3.0 +Server-9.x DESCRIPTION: What lacks in MySQL is something similar like the Oracle Wait Interface. The basic idea is, that every connection/thread (and the whole system) shows (on request) where it spent how much time doing what. They implemented the wait interface (which is possibly a sub-set of Sun's dtrace) where you can see where you loose your time. Marc Callaghan and Baron Schwartz blogged in this direction recently: http://www.xaprb.com/blog/2009/11/07/a-review-of-optimizing-oracle-performa… http://www.facebook.com/note.php?note_id=355839540932 Possibly the MySQL performance schema can cover part/all of it yet? http://dev.mysql.com/doc/performance-schema/en/index.html Good sources to start with: http://www.amazon.com/Oracle-Wait-Interface-Performance-Diagnostics/dp/0072… http://books.google.ch/books?id=14OmJzfCfXMC&pg=PA6&lpg=PA6&dq=owi+is+a+per… http://www.oracle.com/technology/books/pdfs/sample%20chapter%202729x.pdf http://www.amazon.com/Optimizing-Oracle-Performance-Cary-Millsap/dp/0596005… ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Shinguz): Index usage tracker (103)
by worklog-noreply＠askmonty.org 10 Mar '10

10 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Index usage tracker CREATION DATE..: Wed, 10 Mar 2010, 11:29 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 103 (http://askmonty.org/worklog/?tid=103) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Shinguz - Wed, 10 Mar 2010, 11:30)=-=- High Level Description modified. --- /tmp/wklog.103.old.30875 2010-03-10 11:30:45.000000000 +0000 +++ /tmp/wklog.103.new.30875 2010-03-10 11:30:45.000000000 +0000 @@ -35,3 +35,6 @@ Possibly this could/should be implemented on the handler interface level because there we know what we are touching? But I am not familiar with the code. + +If some more specification is need for implementation let me know and I will +collect the stuff from the sources mentioned above. DESCRIPTION: What indexes are needed is often easy to find. What is more difficult is to find which indexes are not used at all. Thus some statistics about the index usage would be nice. I think Percona has already something like this done some time ago. And big O has similar functionality. I could imagine something like this: +------------+------------+--------------+---------------------+---------------------+---------------------+------------+----------+-----------+-----------+------------+ | table_name | index_name | index_length | create_time | update_time | use_time | read_first | read_key | read_next | full_scan | range_scan | +------------+------------+--------------+---------------------+---------------------+---------------------+------------+----------+-----------+-----------+------------+ | test_table | idx1 | 1234567890 | 2010-01-01 00:00:00 | 2010-01-01 00:00:00 | 2010-03-10 11:34:56 | 1234 | 42560 | 2468 | 234 | 321 | | test_table | idx2 | 123456 | 2010-01-01 00:00:00 | NULL | NULL | 0 | 0 | 0 | 0 | 0 | | test_table | idx3 | 234561 | 2010-01-01 00:00:00 | 2010-03-10 11:12:34 | 2010-03-10 11:34:56 | 7890 | 89890 | 15780 | 678 | 321 | | test_table | idx4 | 345612 | 2010-01-01 00:00:00 | 2010-03-10 11:34:56 | NULL | 0 | 0 | 0 | 0 | 0 | | test_table | idx5 | 456123 | 2010-01-01 00:00:00 | 2010-03-10 06:56:12 | NULL | 0 | 0 | 0 | 0 | 0 | | test_table | idx6 | 561234 | 2010-01-01 00:00:00 | 2010-03-10 01:12:34 | 2010-03-10 11:34:42 | 3456 | 12356 | 6912 | 123 | 12 | +------------+------------+--------------+---------------------+---------------------+---------------------+------------+----------+-----------+-----------+------------+ Possibly this could/should be implemented on the handler interface level because there we know what we are touching? But I am not familiar with the code. If some more specification is need for implementation let me know and I will collect the stuff from the sources mentioned above. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] Updated (by Shinguz): Index usage tracker (103)
by worklog-noreply＠askmonty.org 10 Mar '10

10 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Index usage tracker CREATION DATE..: Wed, 10 Mar 2010, 11:29 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 103 (http://askmonty.org/worklog/?tid=103) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: -=-=(Shinguz - Wed, 10 Mar 2010, 11:30)=-=- High Level Description modified. --- /tmp/wklog.103.old.30875 2010-03-10 11:30:45.000000000 +0000 +++ /tmp/wklog.103.new.30875 2010-03-10 11:30:45.000000000 +0000 @@ -35,3 +35,6 @@ Possibly this could/should be implemented on the handler interface level because there we know what we are touching? But I am not familiar with the code. + +If some more specification is need for implementation let me know and I will +collect the stuff from the sources mentioned above. DESCRIPTION: What indexes are needed is often easy to find. What is more difficult is to find which indexes are not used at all. Thus some statistics about the index usage would be nice. I think Percona has already something like this done some time ago. And big O has similar functionality. I could imagine something like this: +------------+------------+--------------+---------------------+---------------------+---------------------+------------+----------+-----------+-----------+------------+ | table_name | index_name | index_length | create_time | update_time | use_time | read_first | read_key | read_next | full_scan | range_scan | +------------+------------+--------------+---------------------+---------------------+---------------------+------------+----------+-----------+-----------+------------+ | test_table | idx1 | 1234567890 | 2010-01-01 00:00:00 | 2010-01-01 00:00:00 | 2010-03-10 11:34:56 | 1234 | 42560 | 2468 | 234 | 321 | | test_table | idx2 | 123456 | 2010-01-01 00:00:00 | NULL | NULL | 0 | 0 | 0 | 0 | 0 | | test_table | idx3 | 234561 | 2010-01-01 00:00:00 | 2010-03-10 11:12:34 | 2010-03-10 11:34:56 | 7890 | 89890 | 15780 | 678 | 321 | | test_table | idx4 | 345612 | 2010-01-01 00:00:00 | 2010-03-10 11:34:56 | NULL | 0 | 0 | 0 | 0 | 0 | | test_table | idx5 | 456123 | 2010-01-01 00:00:00 | 2010-03-10 06:56:12 | NULL | 0 | 0 | 0 | 0 | 0 | | test_table | idx6 | 561234 | 2010-01-01 00:00:00 | 2010-03-10 01:12:34 | 2010-03-10 11:34:42 | 3456 | 12356 | 6912 | 123 | 12 | +------------+------------+--------------+---------------------+---------------------+---------------------+------------+----------+-----------+-----------+------------+ Possibly this could/should be implemented on the handler interface level because there we know what we are touching? But I am not familiar with the code. If some more specification is need for implementation let me know and I will collect the stuff from the sources mentioned above. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0

[Maria-developers] New (by Shinguz): Index usage tracker (103)
by worklog-noreply＠askmonty.org 10 Mar '10

10 Mar '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Index usage tracker CREATION DATE..: Wed, 10 Mar 2010, 11:29 SUPERVISOR.....: Bothorsen IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Client-BackLog TASK ID........: 103 (http://askmonty.org/worklog/?tid=103) VERSION........: Benchmarks-3.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: What indexes are needed is often easy to find. What is more difficult is to find which indexes are not used at all. Thus some statistics about the index usage would be nice. I think Percona has already something like this done some time ago. And big O has similar functionality. I could imagine something like this: +------------+------------+--------------+---------------------+---------------------+---------------------+------------+----------+-----------+-----------+------------+ | table_name | index_name | index_length | create_time | update_time | use_time | read_first | read_key | read_next | full_scan | range_scan | +------------+------------+--------------+---------------------+---------------------+---------------------+------------+----------+-----------+-----------+------------+ | test_table | idx1 | 1234567890 | 2010-01-01 00:00:00 | 2010-01-01 00:00:00 | 2010-03-10 11:34:56 | 1234 | 42560 | 2468 | 234 | 321 | | test_table | idx2 | 123456 | 2010-01-01 00:00:00 | NULL | NULL | 0 | 0 | 0 | 0 | 0 | | test_table | idx3 | 234561 | 2010-01-01 00:00:00 | 2010-03-10 11:12:34 | 2010-03-10 11:34:56 | 7890 | 89890 | 15780 | 678 | 321 | | test_table | idx4 | 345612 | 2010-01-01 00:00:00 | 2010-03-10 11:34:56 | NULL | 0 | 0 | 0 | 0 | 0 | | test_table | idx5 | 456123 | 2010-01-01 00:00:00 | 2010-03-10 06:56:12 | NULL | 0 | 0 | 0 | 0 | 0 | | test_table | idx6 | 561234 | 2010-01-01 00:00:00 | 2010-03-10 01:12:34 | 2010-03-10 11:34:42 | 3456 | 12356 | 6912 | 123 | 12 | +------------+------------+--------------+---------------------+---------------------+---------------------+------------+----------+-----------+-----------+------------+ Possibly this could/should be implemented on the handler interface level because there we know what we are touching? But I am not familiar with the code. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v3.5.9)

1 0