- developers - lists.mariadb.org

[Maria-developers] packaging
by Leif Walsh 22 Jan '14

22 Jan '14

Hi all, How do you make debian and rpm packages for mariadb? Which are the "blessed" config files and how is the process automated (and how might I run it manually)? -- Cheers, Leif

2 2

Re: [Maria-developers] MDEV-5419 no audit events for warnings converted to errors in the strict mode
by Sergei Golubchik 22 Jan '14

22 Jan '14

Hi, Holyfoot! On Dec 15, holyfoot(a)askmonty.org wrote: > revno: 4005 > revision-id: holyfoot(a)askmonty.org-20131215112825-1a5p7fa6mwdoin26 > parent: sergii(a)pisem.net-20131213120038-hjea4r5zp7sjuq8l > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev-5419 > timestamp: Sun 2013-12-15 15:28:25 +0400 > message: > MDEV-5419 no audit events for warnings converted to errors in the strict mode. > Plugins get error notifications only when my_message_sql() is called. > But errors are launched with THD::raise_condition() calls in other > places. These are push_warning(), implementations of SIGNAL and > RESIGNAL commands. > So it makes sence to notify plugins there in THD::raise_condition(). Looks ok. Please add test cases for MDEV-5419 and for MDEV-4846. Then ok to push. Regards, Sergei

1 0

Re: [Maria-developers] MDEV-5364 mysql_upgrade should be more verbose
by Sergei Golubchik 21 Jan '14

21 Jan '14

Hi, Holyfoot! On Dec 15, holyfoot(a)askmonty.org wrote: > At file:///home/hf/wmar/mdev-5364/ > > ------------------------------------------------------------ > revno: 4005 > revision-id: holyfoot(a)askmonty.org-20131215104419-ks2enrs2tds0z4ti > parent: sergii(a)pisem.net-20131213120038-hjea4r5zp7sjuq8l > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev-5364 > timestamp: Sun 2013-12-15 14:44:19 +0400 > message: > MDEV-5364 mysql_upgrade should be more verbose. > If the check_version_match() fails during the mysql_upgrade > execution, it doesn't print the 'mysql' output. > So the printing of the output added to the check_version_match() > and also to the get_upgrade_info_file_name() functions. Looks ok, but I'd prefer to see a test case too. Can you please add it? Ok to push with a test case. Regards, Sergei

1 0

[Maria-developers] MariaDB plugin interface and embedded library compatibility
by Honza Horak 21 Jan '14

21 Jan '14

Hi guys, I'm not able to find any information about compatibility assurance of server side -- i.e plugin interface and libmysqld.so library. I expect both should have stable API at least for one minor version across bugfix releases, but I don't believe that libmysqld.so is able to preserve ABI compatibility, since the set of exported symbols there is really huge. Can you, please, provide some simple statement (ideally as an article) what is your best afford and if we can expect API/ABI of plugin/libmysqld.so interfaces? Thanks and regards, Honza

3 3

Re: [Maria-developers] [Commits] Rev 3963: MDEV-5426: Assertion `toku_ft_needed_unlocked(src_h)' failed (errno=11) ... in file:///home/psergey/chroot/saucy-x64/home/psergey/dev2/10.0/
by Sergei Petrunia 21 Jan '14

21 Jan '14

Hi Jan, On Tue, Jan 21, 2014 at 12:18:13PM +0200, Jan Lindström wrote: > Hi, > > While the original problem is tokudb related and below fix does > nicely address it and I'm sure that you did run regression testing, > my question is does below change have any affect on other storage > engines ? Why not add test case that is run all/most of the > supported storage engines ? The problem is that I am not aware of any location where I could add a test and then it would be run for every available storage engine. The effect of the change is that now EXPLAIN INSERT ... SELECT does not make a handler->start_bulk_insert() call that's not paired with a end_bulk_insert() call. > > R: Jan > > >At file:///home/psergey/chroot/saucy-x64/home/psergey/dev2/10.0/ > > > >------------------------------------------------------------ > >revno: 3963 > >revision-id: psergey(a)askmonty.org-20140121100256-pwiacbt684azw6kj > >parent: elenst(a)opensuse.home-20131228163657-kk32bmtqogy61mwm > >committer: Sergey Petrunya <psergey(a)askmonty.org> > >branch nick: 10.0 > >timestamp: Tue 2014-01-21 14:02:56 +0400 > >message: > > MDEV-5426: Assertion `toku_ft_needed_unlocked(src_h)' failed (errno=11) ... > > - the problem was caused by EXPLAIN INSERT SELECT. For that statement, > > the code would call select_insert::prepare2(), which would call > > handler->ha_start_bulk_insert(). The corresponding handler->end_bulk_insert() > > call is made from select_insert::send_eof or select_insert::abort_result_set > > which are never called for EXPLAIN INSERT SELECT. > > - Fixed by re-using approach of mysql-5.6: don't call ha_start_bulk_insert() if > > we are in EXPLAIN. > >=== modified file 'sql/sql_insert.cc' > >--- a/sql/sql_insert.cc 2013-12-16 12:02:21 +0000 > >+++ b/sql/sql_insert.cc 2014-01-21 10:02:56 +0000 > >@@ -3555,7 +3555,8 @@ int select_insert::prepare2(void) > > { > > DBUG_ENTER("select_insert::prepare2"); > > if (thd->lex->current_select->options & OPTION_BUFFER_RESULT && > >- thd->locked_tables_mode <= LTM_LOCK_TABLES) > >+ thd->locked_tables_mode <= LTM_LOCK_TABLES && > >+ !thd->lex->describe) > > table->file->ha_start_bulk_insert((ha_rows) 0); > > DBUG_RETURN(0); > > } > > > >=== added file 'storage/tokudb/mysql-test/tokudb_mariadb/r/mdev5426.result' > >--- a/storage/tokudb/mysql-test/tokudb_mariadb/r/mdev5426.result 1970-01-01 00:00:00 +0000 > >+++ b/storage/tokudb/mysql-test/tokudb_mariadb/r/mdev5426.result 2014-01-21 10:02:56 +0000 > >@@ -0,0 +1,6 @@ > >+CREATE TABLE t1 (i INT) ENGINE=TokuDB; > >+EXPLAIN INSERT INTO t1 SELECT * FROM t1; > >+id select_type table type possible_keys key key_len ref rows Extra > >+1 SIMPLE t1 ALL NULL NULL NULL NULL 1 Using temporary > >+INSERT INTO t1 SELECT * FROM t1; > >+DROP TABLE t1; > > > >=== added file 'storage/tokudb/mysql-test/tokudb_mariadb/t/mdev5426.test' > >--- a/storage/tokudb/mysql-test/tokudb_mariadb/t/mdev5426.test 1970-01-01 00:00:00 +0000 > >+++ b/storage/tokudb/mysql-test/tokudb_mariadb/t/mdev5426.test 2014-01-21 10:02:56 +0000 > >@@ -0,0 +1,10 @@ > >+ > >+CREATE TABLE t1 (i INT) ENGINE=TokuDB; > >+EXPLAIN INSERT INTO t1 SELECT * FROM t1; > >+ > >+--connect con1,localhost,root,,test > >+INSERT INTO t1 SELECT * FROM t1; > >+ > >+--connection default > >+--disconnect con1 > >+DROP TABLE t1; > > -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] New message from Yoshinori Matsunobu
by Kristian Nielsen 21 Jan '14

21 Jan '14

"Facebook" <notification+xxx(a)facebookmail.com> writes: > I read your latest blog post about profile-guided optisation, which is really interesting. > > I have one question.. What perf options/arguments did you use to get INST_RETIRED.ANY and ICACHE.MISSES ? perf stat -e cache-misses? I'm not sure what options give these numbers.. perf record -e r03c -e r1c2 -e rc0 -e r280 -c 250000 sql/mysqld ... INST_RETIRED.ANY is -e rc0. ICACHE.MISSES is r280. Note that except for a few, the performance counters are processer specific. The full list of available counters is found in "Intel® 64 and IA-32 Architectures Software Developer’s Manual Volume 3B: System Programming Guide, Part 2". Eg. the ones for Sandy Bridge are found in section 19.4: http://www.intel.com/content/www/us/en/processors/architectures-software-de… (For example, ICACHE.MISSES is umask=0x02 and eventnum=0x80, hence r280.) - Kristian.

1 0

Re: [Maria-developers] [Commits] Rev 3964: MDEV-4816: rpl.rpl_trunc_temp fails in 10.0-serg in file:///home/psergey/dev2/10.0-fix1/
by Kristian Nielsen 21 Jan '14

21 Jan '14

Sergey Petrunya <psergey(a)askmonty.org> writes: > ------------------------------------------------------------ > revno: 3964 > revision-id: psergey(a)askmonty.org-20140120123657-g1z4g11xmsdiw2dc > committer: Sergey Petrunya <psergey(a)askmonty.org> > message: > MDEV-4816: rpl.rpl_trunc_temp fails in 10.0-serg > Undo the previous band-aid fix in psergey(a)askmonty.org-20130802141209-4dqfvx2db8acxwbl. > Kristian has made a proper fix, which uses a different approach. Thanks! - Kristian.

1 0

[Maria-developers] Top voted features for MariaDB 10.1
by Rasmus Johansson 19 Jan '14

19 Jan '14

Hi, Yesterday Daniel Bartholomew published a blog post about voting for features in MariaDB 10.1, https://blog.mariadb.org/what-do-you-want-to-see-in-mariadb-10-1/ . To make it easier to follow which the most wanted (most votes) features are I pulled together a list in JIRA for anyone interested. It can be found here https://mariadb.atlassian.net/secure/Dashboard.jspa?selectPageId=11500 . The page doesn't require login, but if you want to vote you need to log in. BR, Rasmus -- Rasmus Johansson, VP Engineering MariaDB | t: +358 50 499 9589 | Skype: ratzpo

2 1

[Maria-developers] problem with the linux thread pool in mariadb 5.5
by Rich Prohaska 17 Jan '14

17 Jan '14

we have a customer that has observed problems related to the interaction of the thread pools in mariadb 5.5.30 and tokudb 7.1.0. the problem is that connections that stuck for 100's of seconds in the Killed state when a big tokudb transaction commit is in progress. the customer claims that this problem was resolved by turning the thread pool OFF. have there been any fixes to the thread pool implementation post the 5.5.30 release? here is some information that may be useful. the processlist showed 2838 client connections of which: 851 Killed 483 Sleep 1467 Connect 1 show processlist 1 processing a commit on a big delete from a tokudb table 35 blocked on a row lock held by the big delete the Killed connections look like: 84847752 sfi_mysql 10.0.0.60:1814 sfi Killed 93 NULL 0.000 with the connection address and time slightly different a gdb snapshot of the system at the time of the failure: 291 total threads 130 threads waiting on fil_aio_wait 52 threads waiting on tokudb's work_on_kibbutz 16 thread tokudb deserialization thread waiting 36 mysql update thread blocked on the tokudb lock tree 1thread executing a big tokudb delete transaction 40 threads in the thread pool get_event function 1 tokudb checkpoint thread blocked waiting for the LP MO lock, which is held by the big txn commit thread that leaves 15 misc worker threads that are not doing anything interesting the gdb stacks for the thread pool threads are: Thread 78 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 71 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 70 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 69 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 68 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 62 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 57 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 52 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 51 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 49 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 45 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 44 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 43 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 42 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 41 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 39 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 37 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 36 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 35 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 33 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 32 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 31 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 26 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 24 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 22 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 21 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 20 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 19 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 18 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 17 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 16 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 13 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 12 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 11 : epoll_wait , io_poll_wait , listener , get_event , worker_main , start_thread , clone Thread 8 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 7 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 5 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 4 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 3 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone Thread 2 : pthread_cond_timedwait@@GLIBC_2.3.2 , inline_mysql_cond_timedwait , get_event , worker_main , start_thread , clone thanks rich prohaska

1 0

[Maria-developers] libmysqlclient.so and Fedora-specific symbol versioning
by Honza Horak 15 Jan '14

15 Jan '14

Hi guys, I'm writing this as a maintainer of MariaDB package in Fedora and RHEL. I've got some complaints about libmysqlclient symbols versioning in Fedora and future RHEL-7: https://bugzilla.redhat.com/show_bug.cgi?id=1045013 Simply put, the issue there is that RPMs (both made by MariaDB upstream or Fedora community) uses different symbol versioning than it is used in other distributions (deb packages, binary tar balls) or by other MySQL providers (Oracle, Percona). Fedora does it because it used to be done that way before and it was not changed at a time upstream changed that, since Fedora started to handle symbols list on its own. MariaDB upstream adopted Fedora's behavior as a resolution of the bug: https://mariadb.atlassian.net/browse/MDEV-3923 As a result, we have different versioning only based on packaging format now, which seems to be a problem. So, I'd like to propose to sync this gap between "RPM-based packages" and "rest of the world" in the future Fedora/RHEL releases, which basically means I'd like to ask: In case Fedora 21 and RHEL-7 adopt non-versioned symbols in libmysqlclient, would MariaDB upstream be able to do the same in RPMs for these releases? There is also second difference between RPM and non-RPM builds, which is that only limited subset of symbols (those that are documented as API) is exported in the client library in Fedora RPMs. This is because we believe exporting all symbols is wrong thing. Regarding this issue, we'd like to keep the limited set of symbols in RPMs, so, the only change I'm proposing to change versioning of these symbols (as described above). Primarily, I'd like to hear what MariaDB upstream's position on that is, if the sync can happen, but any ideas will be welcome. Honza

2 6

[Maria-developers] TokuDB INSERT NOAR / UPDATE NOAR
by Laurynas Biveinis 09 Jan '14

09 Jan '14

Hello - https://mariadb.com/kb/en/tokudb-differences/ says, for version 10: "No INSERT NOAR or UPDATE NOAR commands. We are working with Tokutek to improve this feature before adding it to MariaDB. " What improvements you are looking into? Are there any JIRA tasks to tracks? Thanks, -- Laurynas

1 0

[Maria-developers] Need Review: MDEV-5509: Seconds_behind_master incorrect in parallel replication in http://bazaar.launchpad.net/~maria-captains/maria/10.0
by Kristian Nielsen 08 Jan '14

08 Jan '14

Could someone review this patch for MDEV-5509? I am in particular worried that this might change some subtle behaviour of Seconds_Behind_Master in unexpected ways. Of course I tried my best to make sure such could not occur, but the semantics is really tricky and poorly documented, and I do not think we have good coverage in the test suite. So I would like to have one other developer check it carefully as well and see if I've missed something. - Kristian. knielsen(a)knielsen-hq.org writes: > At http://bazaar.launchpad.net/~maria-captains/maria/10.0 > > ------------------------------------------------------------ > revno: 3959 > revision-id: knielsen(a)knielsen-hq.org-20140108100044-iszrbhddvxkfne71 > parent: knielsen(a)knielsen-hq.org-20140107105703-sn31yvf80batk1yx > committer: knielsen(a)knielsen-hq.org > branch nick: mariadb-10.0-base > timestamp: Wed 2014-01-08 11:00:44 +0100 > message: > MDEV-5509: Seconds_behind_master incorrect in parallel replication > > The problem was a race between the SQL driver thread and the worker threads. > The SQL driver thread would set rli->last_master_timestamp to zero to > mark that it has caught up with the master, while the worker threads would > set it to the timestamp of the executed event. This can happen out-of-order > in parallel replication, causing the "caught up" status to be overwritten > and Seconds_Behind_Master to wrongly grow when the slave is idle. > > To fix, introduce a separate flag rli->sql_thread_caught_up to mark that the > SQL driver thread is caught up. This avoids issues with worker threads > overwriting the SQL driver thread status. In parallel replication, we then > make SHOW SLAVE STATUS check in addition that all worker threads are idle > before showing Seconds_Behind_Master as 0 due to slave idle. > === added file 'mysql-test/suite/rpl/r/rpl_parallel2.result' > --- a/mysql-test/suite/rpl/r/rpl_parallel2.result 1970-01-01 00:00:00 +0000 > +++ b/mysql-test/suite/rpl/r/rpl_parallel2.result 2014-01-08 10:00:44 +0000 > @@ -0,0 +1,18 @@ > +include/rpl_init.inc [topology=1->2] > +*** MDEV-5509: Incorrect value for Seconds_Behind_Master if parallel replication *** > +SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; > +include/stop_slave.inc > +SET GLOBAL slave_parallel_threads=5; > +include/start_slave.inc > +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); > +CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave"); > +INSERT INTO t1 VALUES (1,sleep(2)); > +Warnings: > +Note 1592 Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. > +Seconds_Behind_Master should be zero here because the slave is fully caught up and idle. > +Seconds_Behind_Master = '0' > +include/stop_slave.inc > +SET GLOBAL slave_parallel_threads=@old_parallel_threads; > +include/start_slave.inc > +DROP TABLE t1; > +include/rpl_end.inc > > === added file 'mysql-test/suite/rpl/t/rpl_parallel2.test' > --- a/mysql-test/suite/rpl/t/rpl_parallel2.test 1970-01-01 00:00:00 +0000 > +++ b/mysql-test/suite/rpl/t/rpl_parallel2.test 2014-01-08 10:00:44 +0000 > @@ -0,0 +1,41 @@ > +--source include/have_binlog_format_statement.inc > +--let $rpl_topology=1->2 > +--source include/rpl_init.inc > + > +--echo *** MDEV-5509: Incorrect value for Seconds_Behind_Master if parallel replication *** > + > +--connection server_2 > +SET @old_parallel_threads=@@GLOBAL.slave_parallel_threads; > +--source include/stop_slave.inc > +SET GLOBAL slave_parallel_threads=5; > +--source include/start_slave.inc > + > +--connection server_1 > +CREATE TABLE t1 (a INT PRIMARY KEY, b INT); > +CALL mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave"); > +--save_master_pos > + > +--connection server_2 > +--sync_with_master > + > +--connection server_1 > +INSERT INTO t1 VALUES (1,sleep(2)); > +--save_master_pos > + > +--connection server_2 > +--sync_with_master > + > +--echo Seconds_Behind_Master should be zero here because the slave is fully caught up and idle. > +--let $status_items= Seconds_Behind_Master > +--source include/show_slave_status.inc > + > + > +--connection server_2 > +--source include/stop_slave.inc > +SET GLOBAL slave_parallel_threads=@old_parallel_threads; > +--source include/start_slave.inc > + > +--connection server_1 > +DROP TABLE t1; > + > +--source include/rpl_end.inc > > === modified file 'sql/rpl_parallel.cc' > --- a/sql/rpl_parallel.cc 2014-01-03 11:20:53 +0000 > +++ b/sql/rpl_parallel.cc 2014-01-08 10:00:44 +0000 > @@ -766,6 +766,27 @@ rpl_parallel::wait_for_done() > } > > > +bool > +rpl_parallel::workers_idle() > +{ > + struct rpl_parallel_entry *e; > + uint32 i, max_i; > + > + max_i= domain_hash.records; > + for (i= 0; i < max_i; ++i) > + { > + bool active; > + e= (struct rpl_parallel_entry *)my_hash_element(&domain_hash, i); > + mysql_mutex_lock(&e->LOCK_parallel_entry); > + active= e->current_sub_id > e->last_committed_sub_id; > + mysql_mutex_unlock(&e->LOCK_parallel_entry); > + if (active) > + break; > + } > + return (i == max_i); > +} > + > + > /* > do_event() is executed by the sql_driver_thd thread. > It's main purpose is to find a thread that can execute the query. > > === modified file 'sql/rpl_parallel.h' > --- a/sql/rpl_parallel.h 2013-11-05 11:01:26 +0000 > +++ b/sql/rpl_parallel.h 2014-01-08 10:00:44 +0000 > @@ -117,6 +117,7 @@ struct rpl_parallel { > void reset(); > rpl_parallel_entry *find(uint32 domain_id); > void wait_for_done(); > + bool workers_idle(); > bool do_event(rpl_group_info *serial_rgi, Log_event *ev, > ulonglong event_size); > }; > > === modified file 'sql/rpl_rli.cc' > --- a/sql/rpl_rli.cc 2013-12-17 09:50:34 +0000 > +++ b/sql/rpl_rli.cc 2014-01-08 10:00:44 +0000 > @@ -56,7 +56,7 @@ Relay_log_info::Relay_log_info(bool is_s > is_fake(FALSE), > #endif > group_master_log_pos(0), log_space_total(0), ignore_log_space_limit(0), > - last_master_timestamp(0), slave_skip_counter(0), > + last_master_timestamp(0), sql_thread_caught_up(true), slave_skip_counter(0), > abort_pos_wait(0), slave_run_id(0), sql_driver_thd(), > inited(0), abort_slave(0), slave_running(0), until_condition(UNTIL_NONE), > until_log_pos(0), retried_trans(0), executed_entries(0), > @@ -1287,9 +1287,14 @@ void Relay_log_info::stmt_done(my_off_t > (probably ok - except in some very rare cases, only consequence > is that value may take some time to display in > Seconds_Behind_Master - not critical). > + > + In parallel replication, we take care to not set last_master_timestamp > + backwards, in case of out-of-order calls here. > */ > if (!(event_creation_time == 0 && > - IF_DBUG(debug_not_change_ts_if_art_event > 0, 1))) > + IF_DBUG(debug_not_change_ts_if_art_event > 0, 1)) && > + !(rgi->is_parallel_exec && event_creation_time <= last_master_timestamp) > + ) > last_master_timestamp= event_creation_time; > } > DBUG_VOID_RETURN; > > === modified file 'sql/rpl_rli.h' > --- a/sql/rpl_rli.h 2013-11-08 14:14:18 +0000 > +++ b/sql/rpl_rli.h 2014-01-08 10:00:44 +0000 > @@ -221,6 +221,12 @@ class Relay_log_info : public Slave_repo > bool sql_force_rotate_relay; > > time_t last_master_timestamp; > + /* > + The SQL driver thread sets this true while it is waiting at the end of the > + relay log for more events to arrive. SHOW SLAVE STATUS uses this to report > + Seconds_Behind_Master as zero while the SQL thread is so waiting. > + */ > + bool sql_thread_caught_up; > > void clear_until_condition(); > > > === modified file 'sql/slave.cc' > --- a/sql/slave.cc 2013-12-16 12:48:32 +0000 > +++ b/sql/slave.cc 2014-01-08 10:00:44 +0000 > @@ -2615,8 +2615,24 @@ static bool send_show_master_info_data(T > if ((mi->slave_running == MYSQL_SLAVE_RUN_CONNECT) && > mi->rli.slave_running) > { > - long time_diff= ((long)(time(0) - mi->rli.last_master_timestamp) > - - mi->clock_diff_with_master); > + long time_diff; > + bool idle; > + time_t stamp= mi->rli.last_master_timestamp; > + > + if (!stamp) > + idle= true; > + else > + { > + idle= mi->rli.sql_thread_caught_up; > + if (opt_slave_parallel_threads > 0 && idle && > + !mi->rli.parallel.workers_idle()) > + idle= false; > + } > + if (idle) > + time_diff= 0; > + else > + { > + time_diff= ((long)(time(0) - stamp) - mi->clock_diff_with_master); > /* > Apparently on some systems time_diff can be <0. Here are possible > reasons related to MySQL: > @@ -2632,13 +2648,15 @@ static bool send_show_master_info_data(T > slave is 2. At SHOW SLAVE STATUS time, assume that the difference > between timestamp of slave and rli->last_master_timestamp is 0 > (i.e. they are in the same second), then we get 0-(2-1)=-1 as a result. > - This confuses users, so we don't go below 0: hence the max(). > + This confuses users, so we don't go below 0. > > last_master_timestamp == 0 (an "impossible" timestamp 1970) is a > special marker to say "consider we have caught up". > */ > - protocol->store((longlong)(mi->rli.last_master_timestamp ? > - max(0, time_diff) : 0)); > + if (time_diff < 0) > + time_diff= 0; > + } > + protocol->store((longlong)time_diff); > } > else > { > @@ -6096,6 +6114,7 @@ static Log_event* next_event(rpl_group_i > > if (hot_log) > mysql_mutex_unlock(log_lock); > + rli->sql_thread_caught_up= false; > DBUG_RETURN(ev); > } > if (opt_reckless_slave) // For mysql-test > @@ -6133,12 +6152,10 @@ static Log_event* next_event(rpl_group_i > Seconds_Behind_Master would be zero only when master has no > more updates in binlog for slave. The heartbeat can be sent > in a (small) fraction of slave_net_timeout. Until it's done > - rli->last_master_timestamp is temporarely (for time of > - waiting for the following event) reset whenever EOF is > - reached. > + rli->sql_thread_caught_up is temporarely (for time of waiting for > + the following event) set whenever EOF is reached. > */ > - time_t save_timestamp= rli->last_master_timestamp; > - rli->last_master_timestamp= 0; > + rli->sql_thread_caught_up= true; > > DBUG_ASSERT(rli->relay_log.get_open_count() == > rli->cur_log_old_open_count); > @@ -6264,7 +6281,7 @@ static Log_event* next_event(rpl_group_i > rli->relay_log.wait_for_update_relay_log(rli->sql_driver_thd); > // re-acquire data lock since we released it earlier > mysql_mutex_lock(&rli->data_lock); > - rli->last_master_timestamp= save_timestamp; > + rli->sql_thread_caught_up= false; > continue; > } > /* > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

[Maria-developers] Correct way to determine the ThreadPool usage
by Jean Weisbuch 08 Jan '14

08 Jan '14

Hello, I am counter-checking the mysqlreport "fork" i am maintening (https://mariadb.atlassian.net/browse/MDEV-573) so it could be included in Maria 10 before GA, i am actually unsure that i am checking correctly the Threadpool usage. For these stats (with thread_pool_max_threads=1200 and thread_handling=pool-of-threads) : +------------------------------------------+---------+ | Variable_name | Value | +------------------------------------------+---------+ | Threadpool_idle_threads | 47 | | Threadpool_threads | 52 | | Threads_cached | 0 | | Threads_connected | 19 | | Threads_created | 599771 | | Threads_running | 6 | +------------------------------------------+---------+ The actual output is : __ Threads ___________________________________________ Running 6 of 24 Created 599.77k 0.2/s Slow 0 0/s Threadpool 98 of 1200 %Used: 8.17 Running 52 of 1200 %Running: 4.33 Idle 46 of 1200 %Idle: 3.83 The formulae used are : Threadpool $stats{'Threadpool_threads'} + $stats{'Threadpool_idle_threads'}) of $vars{'thread_pool_max_threads'} %Used: percentage( ($stats{'Threadpool_threads'} + $stats{'Threadpool_idle_threads'}) / $vars{'thread_pool_max_threads'} Running $stats{'Threadpool_threads'} of $vars{'thread_pool_max_threads'} %Running: percentage($stats{'Threadpool_threads'} / $vars{'thread_pool_max_threads'}) Idle $stats{'Threadpool_idle_threads'} of $vars{'thread_pool_max_threads'} %Idle: percentage($stats{'Threadpool_idle_threads'} / $vars{'thread_pool_max_threads'}) Is the Threadpool usage calculation right or should only the Threadpool_threads status be used? What is the meaning of the Threads_connected value when the threadpool is used? Regards.

1 0

[Maria-developers] Elena please test: (MDEV-4974) memory leak in 5.5.32-MariaDB-1~wheezy-log
by Sergei Petrunia 04 Jan '14

04 Jan '14

Hi Elena, Could you please test the below? The changed code may affect - queries that use GROUP BY/ORDER BY/DISTINCT, or a combination of those. - correlated/uncorrelated subqueries. The subquery should remain a separate SELECT, i.e. it should not be converted into semi-join or optimized away. - combination of the above. The code adds a cleanup, so I expect a possible problem to be where we cleaned up something we shouldn't have. Most likely effects are either crash or wrong result. I don't think this patch has a chance of introducing a memory leak. ----- Forwarded message from "Sergei Petrunia (JIRA)" <jira(a)mariadb.atlassian.net> ----- Date: Fri, 3 Jan 2014 21:26:24 +0200 (EET) From: "Sergei Petrunia (JIRA)" <jira(a)mariadb.atlassian.net> To: psergey(a)montyprogram.com Subject: [JIRA] (MDEV-4974) memory leak in 5.5.32-MariaDB-1~wheezy-log [ https://mariadb.atlassian.net/browse/MDEV-4974?page=com.atlassian.jira.plug… ] Sergei Petrunia commented on MDEV-4974: ---------------------------------------- Elena, could you please test 5.5 tree, patched with psergey-fix-mdev4954.diff ? > memory leak in 5.5.32-MariaDB-1~wheezy-log > ------------------------------------------ > > Key: MDEV-4974 > URL: https://mariadb.atlassian.net/browse/MDEV-4974 > Project: MariaDB Development > Issue Type: Bug > Affects Versions: 5.5.32 > Environment: Debian Wheezy x86_64 > Linux greeneggs.lentz.com.au 3.9.3-x86_64-linode33 #1 SMP Mon May 20 10:22:57 EDT 2013 x86_64 GNU/Linux > Reporter: Daniel Black > Assignee: Sergei Petrunia > Priority: Critical > Fix For: 5.5.35 > > Attachments: allqueries.sql, catinthehat_memory-day_no_indexmerge.png, drupal.sql, green-eggs-mysql_connections-day.png, greeneggs-memory-day.png, greeneggs-memory-week.png, greeneggs-mysql_bin_relay_log-day.png, greeneggs-mysql_commands-day.png, greeneggs-mysql_files_tables-day.png, greeneggs-mysql_innodb_bpool-day.png, greeneggs-mysql_innodb_semaphores-day.png, greeneggs-mysql_innodb_tnx-day.png, greeneggs-mysql_myisam_indexes-day.png, greeneggs-mysql_qcache-day.png, greeneggs-mysql_select_types-day.png, greeneggs-mysql_sorts-day.png, greeneggs-mysql_table_locks-day.png, greeneggs-mysql_tmp_tables-day.png, leaks-track-allqueries.sql, leaks-track.sql, psergey-fix-mdev4954.diff, psergey-mdev4974-xpl1.diff, valgrind.mysqld.27336 > > > After running mariadb-5.5.32 in a multimaster for a few days its almost out of memory on the active master (the one getting all the reads). > The replication slave (same version) doesn't suffer the memory leak (with or without the replication filters defined). > Disabling the query cache on the active master may (was slightly off peak) have slowed the memory leak however it wasn't stopped. In the graph attached the query cache was disabled from Wed 5:30 to Thursday 03:00 > For greeneggs-mysql_commands-day.png the first drop is when query cache was turned back on. At the end I moved the active master to the other server. Other graphs are for this same time interval. > Memory usage calculation: > From http://dev.mysql.com/doc/refman/5.5/en/memory-use.html > per connection: > @read_buffer_size + @read_rnd_buffer_size + @sort_buffer_size + @join_buffer_size + @binlog_cache_size + @thread_stack + @@tmp_table_size = 19070976 > Max_used_connections 15 > Static component: > @key_buffer_size + @query_cache_size + @innodb_buffer_pool_size + @innodb_additional_mem_pool_size + @@innodb_log_buffer_size > 322961408 > select 15 * 19070976 + 322961408; = 609026048 > 609M max > From top: > PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND > 4532 mysql 20 0 1999m 1.4g 6072 S 4.0 71.9 1017:21 mysqld > I've still got the server running if more status is required. > show engine innodb status > ===================================== > 130830 0:46:23 INNODB MONITOR OUTPUT > ===================================== > Per second averages calculated from the last 17 seconds > ----------------- > BACKGROUND THREAD > ----------------- > srv_master_thread loops: 372124 1_second, 372076 sleeps, 37113 10_second, 1716 background, 1715 flush > srv_master_thread log flush and writes: 350597 > ---------- > SEMAPHORES > ---------- > OS WAIT ARRAY INFO: reservation count 999301, signal count 1149136 > Mutex spin waits 3647275, rounds 12020660, OS waits 198769 > RW-shared spins 896419, rounds 19893071, OS waits 574516 > RW-excl spins 68006, rounds 6887165, OS waits 204958 > Spin rounds per wait: 3.30 mutex, 22.19 RW-shared, 101.27 RW-excl > -------- > FILE I/O > -------- > I/O thread 0 state: waiting for completed aio requests (insert buffer thread) > I/O thread 1 state: waiting for completed aio requests (log thread) > I/O thread 2 state: waiting for completed aio requests (read thread) > I/O thread 3 state: waiting for completed aio requests (read thread) > I/O thread 4 state: waiting for completed aio requests (write thread) > I/O thread 5 state: waiting for completed aio requests (write thread) > Pending normal aio reads: 0 [0, 0] , aio writes: 0 [0, 0] , > ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 > Pending flushes (fsync) log: 0; buffer pool: 0 > 7906118 OS file reads, 39186717 OS file writes, 23493355 OS fsyncs > 0.65 reads/s, 16384 avg bytes/read, 66.29 writes/s, 29.06 fsyncs/s > ------------------------------------- > INSERT BUFFER AND ADAPTIVE HASH INDEX > ------------------------------------- > Ibuf: size 1, free list len 287, seg size 289, 111478 merges > merged operations: > insert 106175, delete mark 100854, delete 13481 > discarded operations: > insert 0, delete mark 0, delete 0 > Hash table size 553229, node heap has 584 buffer(s) > 244.87 hash searches/s, 22.53 non-hash searches/s > --- > LOG > --- > Log sequence number 1981376581482 > Log flushed up to 1981376581482 > Last checkpoint at 1981376562791 > Max checkpoint age 84223550 > Checkpoint age target 81591565 > Modified age 18691 > Checkpoint age 18691 > 0 pending log writes, 0 pending chkp writes > 22419510 log i/o's done, 26.00 log i/o's/second > ---------------------- > BUFFER POOL AND MEMORY > ---------------------- > Total memory allocated 275644416; in additional pool allocated 0 > Total memory allocated by read views 136 > Internal hash tables (constant factor + variable factor) > Adaptive hash index 13998304 (4425832 + 9572472) > Page hash 277432 (buffer pool 0 only) > Dictionary cache 10287074 (1107952 + 9179122) > File system 648160 (82672 + 565488) > Lock system 665688 (664936 + 752) > Recovery system 0 (0 + 0) > Dictionary memory allocated 9179122 > Buffer pool size 16383 > Buffer pool size, bytes 268419072 > Free buffers 1 > Database pages 15798 > Old database pages 5811 > Modified db pages 97 > Pending reads 0 > Pending writes: LRU 0, flush list 0, single page 0 > Pages made young 10760065, not young 0 > 0.53 youngs/s, 0.00 non-youngs/s > Pages read 7903370, created 882751, written 16452888 > 0.65 reads/s, 0.00 creates/s, 39.47 writes/s > Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 > Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s > LRU len: 15798, unzip_LRU len: 0 > I/O sum[1550]:cur[322], unzip sum[0]:cur[0] > -------------- > ROW OPERATIONS > -------------- > 0 queries inside InnoDB, 0 queries in queue > 1 read views open inside InnoDB > 0 transactions active inside InnoDB > 0 out of 1000 descriptors used > ---OLDEST VIEW--- > Normal read view > Read view low limit trx n:o 7D2D2F07 > Read view up limit trx id 7D2D2F07 > Read view low limit trx id 7D2D2F07 > Read view individually stored trx ids: > ----------------- > Main thread process no. 4532, id 140201824495360, state: sleeping > Number of rows inserted 1308400, updated 9736429, deleted 1227755, read 34888786828 > 1.00 inserts/s, 11.71 updates/s, 0.00 deletes/s, 249.22 reads/s > ------------------------ > LATEST DETECTED DEADLOCK > ------------------------ > 130830 0:27:15 > *** (1) TRANSACTION: > TRANSACTION 7D2C63D3, ACTIVE 0 sec starting index read > | Variable_name | Value | > | Aborted_clients | 57 | > | Aborted_connects | 0 | > | Access_denied_errors | 0 | > | Aria_pagecache_blocks_not_flushed | 0 | > | Aria_pagecache_blocks_unused | 15737 | > | Aria_pagecache_blocks_used | 3127 | > | Aria_pagecache_read_requests | 262055553 | > | Aria_pagecache_reads | 107163 | > | Aria_pagecache_write_requests | 69866678 | > | Aria_pagecache_writes | 0 | > | Aria_transaction_log_syncs | 0 | > | Binlog_commits | 12529081 | > | Binlog_group_commits | 12486299 | > | Binlog_snapshot_file | mariadb-bin.001408 | > | Binlog_snapshot_position | 57228334 | > | Binlog_bytes_written | 43273655871 | > | Binlog_cache_disk_use | 768128 | > | Binlog_cache_use | 12510967 | > | Binlog_stmt_cache_disk_use | 24 | > | Binlog_stmt_cache_use | 18077 | > | Busy_time | 0.000000 | > | Bytes_received | 60129853267 | > | Bytes_sent | 691695773018 | > | Com_admin_commands | 3809 | > | Com_assign_to_keycache | 0 | > | Com_alter_db | 0 | > | Com_alter_db_upgrade | 0 | > | Com_alter_event | 0 | > | Com_alter_function | 0 | > | Com_alter_procedure | 0 | > | Com_alter_server | 0 | > | Com_alter_table | 114 | > | Com_alter_tablespace | 0 | > | Com_analyze | 0 | > | Com_begin | 62278 | > | Com_binlog | 0 | > | Com_call_procedure | 0 | > | Com_change_db | 69728 | > | Com_change_master | 0 | > | Com_check | 1426 | > | Com_checksum | 0 | > | Com_commit | 45717 | > | Com_create_db | 1 | > | Com_create_event | 0 | > | Com_create_function | 0 | > | Com_create_index | 0 | > | Com_create_procedure | 1 | > | Com_create_server | 0 | > | Com_create_table | 42 | > | Com_create_trigger | 0 | > | Com_create_udf | 0 | > | Com_create_user | 0 | > | Com_create_view | 0 | > | Com_dealloc_sql | 24 | > | Com_delete | 388136 | > | Com_delete_multi | 5 | > | Com_do | 0 | > | Com_drop_db | 1 | > | Com_drop_event | 0 | > | Com_drop_function | 0 | > | Com_drop_index | 0 | > | Com_drop_procedure | 2 | > | Com_drop_server | 0 | > | Com_drop_table | 1 | > | Com_drop_trigger | 0 | > | Com_drop_user | 0 | > | Com_drop_view | 0 | > | Com_empty_query | 0 | > | Com_execute_sql | 24 | > | Com_flush | 6 | > | Com_grant | 0 | > | Com_ha_close | 0 | > | Com_ha_open | 0 | > | Com_ha_read | 0 | > | Com_help | 2 | > | Com_insert | 847948 | > | Com_insert_select | 551 | > | Com_install_plugin | 0 | > | Com_kill | 50 | > | Com_load | 0 | > | Com_lock_tables | 0 | > | Com_optimize | 0 | > | Com_preload_keys | 0 | > | Com_prepare_sql | 24 | > | Com_purge | 0 | > | Com_purge_before_date | 0 | > | Com_release_savepoint | 0 | > | Com_rename_table | 0 | > | Com_rename_user | 0 | > | Com_repair | 0 | > | Com_replace | 0 | > | Com_replace_select | 0 | > | Com_reset | 0 | > | Com_resignal | 0 | > | Com_revoke | 0 | > | Com_revoke_all | 0 | > | Com_rollback | 3 | > | Com_rollback_to_savepoint | 0 | > | Com_savepoint | 0 | > | Com_select | 47174988 | > | Com_set_option | 1189075 | > | Com_signal | 0 | > | Com_show_authors | 0 | > | Com_show_binlog_events | 3 | > | Com_show_binlogs | 1166 | > | Com_show_charsets | 0 | > | Com_show_client_statistics | 0 | > | Com_show_collations | 0 | > | Com_show_contributors | 0 | > | Com_show_create_db | 115 | > | Com_show_create_event | 0 | > | Com_show_create_func | 0 | > | Com_show_create_proc | 0 | > | Com_show_create_table | 4387 | > | Com_show_create_trigger | 0 | > | Com_show_databases | 7 | > | Com_show_engine_logs | 0 | > | Com_show_engine_mutex | 0 | > | Com_show_engine_status | 1167 | > | Com_show_events | 0 | > | Com_show_errors | 0 | > | Com_show_fields | 5721 | > | Com_show_function_status | 0 | > | Com_show_grants | 0 | > | Com_show_keys | 0 | > | Com_show_index_statistics | 0 | > | Com_show_master_status | 1 | > | Com_show_open_tables | 0 | > | Com_show_plugins | 0 | > | Com_show_privileges | 0 | > | Com_show_procedure_status | 0 | > | Com_show_processlist | 1039 | > | Com_show_profile | 0 | > | Com_show_profiles | 0 | > | Com_show_relaylog_events | 0 | > | Com_show_slave_hosts | 0 | > | Com_show_slave_status | 140780 | > | Com_show_status | 12670 | > | Com_show_storage_engines | 0 | > | Com_show_table_statistics | 0 | > | Com_show_table_status | 4287 | > | Com_show_tables | 6794 | > | Com_show_triggers | 4280 | > | Com_show_user_statistics | 0 | > | Com_show_variables | 2175 | > | Com_show_warnings | 0 | > | Com_slave_start | 10 | > | Com_slave_stop | 4 | > | Com_stmt_close | 24 | > | Com_stmt_execute | 24 | > | Com_stmt_fetch | 0 | > | Com_stmt_prepare | 24 | > | Com_stmt_reprepare | 0 | > | Com_stmt_reset | 0 | > | Com_stmt_send_long_data | 0 | > | Com_truncate | 0 | > | Com_uninstall_plugin | 0 | > | Com_unlock_tables | 2 | > | Com_update | 7082015 | > | Com_update_multi | 1 | > | Com_xa_commit | 0 | > | Com_xa_end | 0 | > | Com_xa_prepare | 0 | > | Com_xa_recover | 0 | > | Com_xa_rollback | 0 | > | Com_xa_start | 0 | > | Compression | OFF | > | Connections | 1164212 | > | Cpu_time | 0.000000 | > | Created_tmp_disk_tables | 2202858 | > | Created_tmp_files | 420678 | > | Created_tmp_tables | 5264753 | > | Delayed_errors | 0 | > | Delayed_insert_threads | 0 | > | Delayed_writes | 0 | > | Empty_queries | 14900340 | > | Executed_events | 0 | > | Executed_triggers | 0 | > | Feature_dynamic_columns | 0 | > | Feature_fulltext | 2 | > | Feature_gis | 28 | > | Feature_locale | 0 | > | Feature_subquery | 135848 | > | Feature_timezone | 6850 | > | Feature_trigger | 586 | > | Feature_xml | 0 | > | Flush_commands | 4 | > | Handler_commit | 63318523 | > | Handler_delete | 748245 | > | Handler_discover | 0 | > | Handler_icp_attempts | 71118314 | > | Handler_icp_match | 71108704 | > | Handler_mrr_init | 0 | > | Handler_mrr_key_refills | 0 | > | Handler_mrr_rowid_refills | 0 | > | Handler_prepare | 14968970 | > | Handler_read_first | 1155850 | > | Handler_read_key | 466199920 | > | Handler_read_last | 55346 | > | Handler_read_next | 27258307498 | > | Handler_read_prev | 8491322 | > | Handler_read_rnd | 66364460 | > | Handler_read_rnd_deleted | 1194 | > | Handler_read_rnd_next | 8174479457 | > | Handler_rollback | 21990 | > | Handler_savepoint | 0 | > | Handler_savepoint_rollback | 0 | > | Handler_tmp_update | 15018397 | > | Handler_tmp_write | 515819305 | > | Handler_update | 6481729 | > | Handler_write | 849123 | > | Innodb_adaptive_hash_cells | 553229 | > | Innodb_adaptive_hash_heap_buffers | 581 | > | Innodb_adaptive_hash_hash_searches | 1107671046 | > | Innodb_adaptive_hash_non_hash_searches | 249488632 | > | Innodb_background_log_sync | 350697 | > | Innodb_buffer_pool_pages_data | 15800 | > | Innodb_buffer_pool_bytes_data | 258867200 | > | Innodb_buffer_pool_pages_dirty | 295 | > | Innodb_buffer_pool_bytes_dirty | 4833280 | > | Innodb_buffer_pool_pages_flushed | 16456234 | > | Innodb_buffer_pool_pages_LRU_flushed | 48422 | > | Innodb_buffer_pool_pages_free | 1 | > | Innodb_buffer_pool_pages_made_not_young | 0 | > | Innodb_buffer_pool_pages_made_young | 10760117 | > | Innodb_buffer_pool_pages_misc | 582 | > | Innodb_buffer_pool_pages_old | 5812 | > | Innodb_buffer_pool_pages_total | 16383 | > | Innodb_buffer_pool_read_ahead_rnd | 0 | > | Innodb_buffer_pool_read_ahead | 2072088 | > | Innodb_buffer_pool_read_ahead_evicted | 91126 | > | Innodb_buffer_pool_read_requests | 10973288017 | > | Innodb_buffer_pool_reads | 5715842 | > | Innodb_buffer_pool_wait_free | 9 | > | Innodb_buffer_pool_write_requests | 132620870 | > | Innodb_checkpoint_age | 88015 | > | Innodb_checkpoint_max_age | 84223550 | > | Innodb_checkpoint_target_age | 81591565 | > | Innodb_data_fsyncs | 23497491 | > | Innodb_data_pending_fsyncs | 0 | > | Innodb_data_pending_reads | 0 | > | Innodb_data_pending_writes | 0 | > | Innodb_data_read | 129493962752 | > | Innodb_data_reads | 7906173 | > | Innodb_data_writes | 39194030 | > | Innodb_data_written | 586483447808 | > | Innodb_dblwr_pages_written | 16456234 | > | Innodb_dblwr_writes | 182242 | > | Innodb_deadlocks | 432 | > | Innodb_dict_tables | 1315 | > | Innodb_have_atomic_builtins | ON | > | Innodb_history_list_length | 3523 | > | Innodb_ibuf_discarded_delete_marks | 0 | > | Innodb_ibuf_discarded_deletes | 0 | > | Innodb_ibuf_discarded_inserts | 0 | > | Innodb_ibuf_free_list | 287 | > | Innodb_ibuf_merged_delete_marks | 100855 | > | Innodb_ibuf_merged_deletes | 13481 | > | Innodb_ibuf_merged_inserts | 106192 | > | Innodb_ibuf_merges | 111495 | > | Innodb_ibuf_segment_size | 289 | > | Innodb_ibuf_size | 1 | > | Innodb_log_waits | 0 | > | Innodb_log_write_requests | 73751900 | > | Innodb_log_writes | 22385577 | > | Innodb_lsn_current | 1981377626218 | > | Innodb_lsn_flushed | 1981377626218 | > | Innodb_lsn_last_checkpoint | 1981377538203 | > | Innodb_master_thread_1_second_loops | 372239 | > | Innodb_master_thread_10_second_loops | 37124 | > | Innodb_master_thread_background_loops | 1716 | > | Innodb_master_thread_main_flush_loops | 1715 | > | Innodb_master_thread_sleeps | 372191 | > | Innodb_max_trx_id | 2100117045 | > | Innodb_mem_adaptive_hash | 13965536 | > | Innodb_mem_dictionary | 10287074 | > | Innodb_mem_total | 275644416 | > | Innodb_mutex_os_waits | 198788 | > | Innodb_mutex_spin_rounds | 12021403 | > | Innodb_mutex_spin_waits | 3647342 | > | Innodb_oldest_view_low_limit_trx_id | 2100116933 | > | Innodb_os_log_fsyncs | 22425791 | > | Innodb_os_log_pending_fsyncs | 0 | > | Innodb_os_log_pending_writes | 0 | > | Innodb_os_log_written | 47227117568 | > | Innodb_page_size | 16384 | > | Innodb_pages_created | 882752 | > | Innodb_pages_read | 7903425 | > | Innodb_pages_written | 16456234 | > | Innodb_purge_trx_id | 2100116933 | > | Innodb_purge_undo_no | 0 | > | Innodb_row_lock_current_waits | 0 | > | Innodb_current_row_locks | 0 | > | Innodb_row_lock_time | 3209772 | > | Innodb_row_lock_time_avg | 74 | > | Innodb_row_lock_time_max | 31935 | > | Innodb_row_lock_waits | 43208 | > | Innodb_rows_deleted | 1227755 | > | Innodb_rows_inserted | 1308502 | > | Innodb_rows_read | 34888819968 | > | Innodb_rows_updated | 9738262 | > | Innodb_read_views_memory | 136 | > | Innodb_descriptors_memory | 8000 | > | Innodb_s_lock_os_waits | 574527 | > | Innodb_s_lock_spin_rounds | 19893402 | > | Innodb_s_lock_spin_waits | 896432 | > | Innodb_truncated_status_writes | 0 | > | Innodb_x_lock_os_waits | 204987 | > | Innodb_x_lock_spin_rounds | 6888035 | > | Innodb_x_lock_spin_waits | 68006 | > | Key_blocks_not_flushed | 0 | > | Key_blocks_unused | 5353 | > | Key_blocks_used | 4170 | > | Key_blocks_warm | 141 | > | Key_read_requests | 6295929 | > | Key_reads | 9626 | > | Key_write_requests | 21159 | > | Key_writes | 10962 | > | Last_query_cost | 0.000000 | > | Max_used_connections | 15 | > | Not_flushed_delayed_rows | 0 | > | Open_files | 142 | > | Open_streams | 0 | > | Open_table_definitions | 397 | > | Open_tables | 595 | > | Opened_files | 9756380 | > | Opened_table_definitions | 6425 | > | Opened_tables | 8666 | > | Opened_views | 0 | > | Performance_schema_cond_classes_lost | 0 | > | Performance_schema_cond_instances_lost | 0 | > | Performance_schema_file_classes_lost | 0 | > | Performance_schema_file_handles_lost | 0 | > | Performance_schema_file_instances_lost | 0 | > | Performance_schema_locker_lost | 0 | > | Performance_schema_mutex_classes_lost | 0 | > | Performance_schema_mutex_instances_lost | 0 | > | Performance_schema_rwlock_classes_lost | 0 | > | Performance_schema_rwlock_instances_lost | 0 | > | Performance_schema_table_handles_lost | 0 | > | Performance_schema_table_instances_lost | 0 | > | Performance_schema_thread_classes_lost | 0 | > | Performance_schema_thread_instances_lost | 0 | > | Prepared_stmt_count | 0 | > | Qcache_free_blocks | 6616 | > | Qcache_free_memory | 18486664 | > | Qcache_hits | 75724207 | > | Qcache_inserts | 8304484 | > | Qcache_lowmem_prunes | 1719071 | > | Qcache_not_cached | 2443705 | > | Qcache_queries_in_cache | 8992 | > | Qcache_total_blocks | 24978 | > | Queries | 142821923 | > | Questions | 133827961 | > | Rows_read | 35275818740 | > | Rows_sent | 1787826753 | > | Rows_tmp_read | 592687087 | > | Rpl_status | AUTH_MASTER | > | Select_full_join | 268533 | > | Select_full_range_join | 6393 | > | Select_range | 6218451 | > | Select_range_check | 0 | > | Select_scan | 3587397 | > | Slave_heartbeat_period | 1800.000 | > | Slave_open_temp_tables | 0 | > | Slave_received_heartbeats | 0 | > | Slave_retried_transactions | 0 | > | Slave_running | ON | > | Slow_launch_threads | 0 | > | Slow_queries | 3796864 | > | Sort_merge_passes | 269513 | > | Sort_range | 170798 | > | Sort_rows | 1870364969 | > | Sort_scan | 4917255 | > | Sphinx_error | | > | Sphinx_time | | > | Sphinx_total | | > | Sphinx_total_found | | > | Sphinx_word_count | | > | Sphinx_words | | > | Ssl_accept_renegotiates | 0 | > | Ssl_accepts | 0 | > | Ssl_callback_cache_hits | 0 | > | Ssl_cipher | | > | Ssl_cipher_list | | > | Ssl_client_connects | 0 | > | Ssl_connect_renegotiates | 0 | > | Ssl_ctx_verify_depth | 0 | > | Ssl_ctx_verify_mode | 0 | > | Ssl_default_timeout | 0 | > | Ssl_finished_accepts | 0 | > | Ssl_finished_connects | 0 | > | Ssl_session_cache_hits | 0 | > | Ssl_session_cache_misses | 0 | > | Ssl_session_cache_mode | NONE | > | Ssl_session_cache_overflows | 0 | > | Ssl_session_cache_size | 0 | > | Ssl_session_cache_timeouts | 0 | > | Ssl_sessions_reused | 0 | > | Ssl_used_session_cache_entries | 0 | > | Ssl_verify_depth | 0 | > | Ssl_verify_mode | 0 | > | Ssl_version | | > | Subquery_cache_hit | 4512 | > | Subquery_cache_miss | 3930 | > | Syncs | 4171361 | > | Table_locks_immediate | 85865730 | > | Table_locks_waited | 15 | > | Tc_log_max_pages_used | 0 | > | Tc_log_page_size | 0 | > | Tc_log_page_waits | 46 | > | Threadpool_idle_threads | 0 | > | Threadpool_threads | 0 | > | Threads_cached | 13 | > | Threads_connected | 2 | > | Threads_created | 15 | > | Threads_running | 2 | > | Uptime | 349494 | > | Uptime_since_flush_status | 349494 | > | Variable_name | Value | > | aria_block_size | 8192 | > | aria_checkpoint_interval | 30 | > | aria_checkpoint_log_activity | 1048576 | > | aria_force_start_after_recovery_failures | 0 | > | aria_group_commit | none | > | aria_group_commit_interval | 0 | > | aria_log_file_size | 1073741824 | > | aria_log_purge_type | immediate | > | aria_max_sort_file_size | 9223372036853727232 | > | aria_page_checksum | ON | > | aria_pagecache_age_threshold | 300 | > | aria_pagecache_buffer_size | 134217728 | > | aria_pagecache_division_limit | 100 | > | aria_recover | NORMAL | > | aria_repair_threads | 1 | > | aria_sort_buffer_size | 134217728 | > | aria_stats_method | nulls_unequal | > | aria_sync_log_dir | NEWFILE | > | aria_used_for_temp_tables | ON | > | auto_increment_increment | 2 | > | auto_increment_offset | 2 | > | autocommit | ON | > | automatic_sp_privileges | ON | > | back_log | 50 | > | basedir | /usr | > | big_tables | OFF | > | binlog_annotate_row_events | OFF | > | binlog_cache_size | 32768 | > | binlog_checksum | NONE | > | binlog_direct_non_transactional_updates | OFF | > | binlog_format | MIXED | > | binlog_optimize_thread_scheduling | ON | > | binlog_stmt_cache_size | 32768 | > | bulk_insert_buffer_size | 1048576 | > | character_set_client | latin1 | > | character_set_connection | latin1 | > | character_set_database | latin1 | > | character_set_filesystem | binary | > | character_set_results | latin1 | > | character_set_server | latin1 | > | character_set_system | utf8 | > | character_sets_dir | /usr/share/mysql/charsets/ | > | collation_connection | latin1_swedish_ci | > | collation_database | latin1_swedish_ci | > | collation_server | latin1_swedish_ci | > | completion_type | NO_CHAIN | > | concurrent_insert | ALWAYS | > | connect_timeout | 5 | > | datadir | /var/lib/mysql/ | > | date_format | %Y-%m-%d | > | datetime_format | %Y-%m-%d %H:%i:%s | > | deadlock_search_depth_long | 15 | > | deadlock_search_depth_short | 4 | > | deadlock_timeout_long | 50000000 | > | deadlock_timeout_short | 10000 | > | debug_no_thread_alarm | OFF | > | default_storage_engine | InnoDB | > | default_week_format | 0 | > | delay_key_write | ON | > | delayed_insert_limit | 100 | > | delayed_insert_timeout | 300 | > | delayed_queue_size | 1000 | > | div_precision_increment | 4 | > | engine_condition_pushdown | OFF | > | event_scheduler | OFF | > | expensive_subquery_limit | 100 | > | expire_logs_days | 3 | > | extra_max_connections | 1 | > | extra_port | 0 | > | flush | OFF | > | flush_time | 0 | > | foreign_key_checks | ON | > | ft_boolean_syntax | + -><()~*:""&| | > | ft_max_word_len | 84 | > | ft_min_word_len | 4 | > | ft_query_expansion_limit | 20 | > | ft_stopword_file | (built-in) | > | general_log | OFF | > | general_log_file | greeneggs.log | > | group_concat_max_len | 1024 | > | have_compress | YES | > | have_crypt | YES | > | have_csv | YES | > | have_dynamic_loading | YES | > | have_geometry | YES | > | have_innodb | YES | > | have_ndbcluster | NO | > | have_openssl | DISABLED | > | have_partitioning | YES | > | have_profiling | YES | > | have_query_cache | YES | > | have_rtree_keys | YES | > | have_ssl | DISABLED | > | have_symlink | YES | > | hostname | greeneggs.lentz.com.au | > | ignore_builtin_innodb | OFF | > | ignore_db_dirs | | > | init_connect | | > | init_file | | > | init_slave | | > | innodb_adaptive_flushing | ON | > | innodb_adaptive_flushing_method | estimate | > | innodb_adaptive_hash_index | ON | > | innodb_adaptive_hash_index_partitions | 1 | > | innodb_additional_mem_pool_size | 8388608 | > | innodb_autoextend_increment | 8 | > | innodb_autoinc_lock_mode | 1 | > | innodb_blocking_buffer_pool_restore | OFF | > | innodb_buffer_pool_instances | 1 | > | innodb_buffer_pool_populate | OFF | > | innodb_buffer_pool_restore_at_startup | 600 | > | innodb_buffer_pool_shm_checksum | ON | > | innodb_buffer_pool_shm_key | 0 | > | innodb_buffer_pool_size | 268435456 | > | innodb_change_buffering | all | > | innodb_checkpoint_age_target | 0 | > | innodb_checksums | ON | > | innodb_commit_concurrency | 0 | > | innodb_concurrency_tickets | 500 | > | innodb_corrupt_table_action | assert | > | innodb_data_file_path | ibdata1:10M:autoextend | > | innodb_data_home_dir | | > | innodb_dict_size_limit | 0 | > | innodb_doublewrite | ON | > | innodb_doublewrite_file | | > | innodb_fake_changes | OFF | > | innodb_fast_checksum | OFF | > | innodb_fast_shutdown | 1 | > | innodb_file_format | Antelope | > | innodb_file_format_check | ON | > | innodb_file_format_max | Antelope | > | innodb_file_per_table | ON | > | innodb_flush_log_at_trx_commit | 1 | > | innodb_flush_method | O_DIRECT | > | innodb_flush_neighbor_pages | area | > | innodb_force_load_corrupted | OFF | > | innodb_force_recovery | 0 | > | innodb_ibuf_accel_rate | 100 | > | innodb_ibuf_active_contract | 1 | > | innodb_ibuf_max_size | 134201344 | > | innodb_import_table_from_xtrabackup | 0 | > | innodb_io_capacity | 1000 | > | innodb_kill_idle_transaction | 0 | > | innodb_large_prefix | OFF | > | innodb_lazy_drop_table | 0 | > | innodb_lock_wait_timeout | 50 | > | innodb_locking_fake_changes | ON | > | innodb_locks_unsafe_for_binlog | OFF | > | innodb_log_block_size | 512 | > | innodb_log_buffer_size | 4194304 | > | innodb_log_file_size | 52428800 | > | innodb_log_files_in_group | 2 | > | innodb_log_group_home_dir | ./ | > | innodb_max_bitmap_file_size | 104857600 | > | innodb_max_changed_pages | 1000000 | > | innodb_max_dirty_pages_pct | 75 | > | innodb_max_purge_lag | 0 | > | innodb_merge_sort_block_size | 1048576 | > | innodb_mirrored_log_groups | 1 | > | innodb_old_blocks_pct | 37 | > | innodb_old_blocks_time | 0 | > | innodb_open_files | 400 | > | innodb_page_size | 16384 | > | innodb_print_all_deadlocks | OFF | > | innodb_purge_batch_size | 20 | > | innodb_purge_threads | 1 | > | innodb_random_read_ahead | OFF | > | innodb_read_ahead | linear | > | innodb_read_ahead_threshold | 56 | > | innodb_read_io_threads | 2 | > | innodb_recovery_stats | OFF | > | innodb_recovery_update_relay_log | OFF | > | innodb_replication_delay | 0 | > | innodb_rollback_on_timeout | OFF | > | innodb_rollback_segments | 128 | > | innodb_show_locks_held | 10 | > | innodb_show_verbose_locks | 0 | > | innodb_spin_wait_delay | 6 | > | innodb_stats_auto_update | 1 | > | innodb_stats_method | nulls_equal | > | innodb_stats_on_metadata | ON | > | innodb_stats_sample_pages | 8 | > | innodb_stats_update_need_lock | 1 | > | innodb_strict_mode | OFF | > | innodb_support_xa | ON | > | innodb_sync_spin_loops | 30 | > | innodb_table_locks | ON | > | innodb_thread_concurrency | 0 | > | innodb_thread_concurrency_timer_based | OFF | > | innodb_thread_sleep_delay | 10000 | > | innodb_track_changed_pages | OFF | > | innodb_use_atomic_writes | OFF | > | innodb_use_fallocate | OFF | > | innodb_use_global_flush_log_at_trx_commit | ON | > | innodb_use_native_aio | ON | > | innodb_use_sys_malloc | ON | > | innodb_use_sys_stats_table | OFF | > | innodb_version | 5.5.32-MariaDB-30.2 | > | innodb_write_io_threads | 2 | > | interactive_timeout | 28800 | > | join_buffer_size | 131072 | > | join_buffer_space_limit | 2097152 | > | join_cache_level | 2 | > | keep_files_on_create | OFF | > | key_buffer_size | 8388608 | > | key_cache_age_threshold | 300 | > | key_cache_block_size | 1024 | > | key_cache_division_limit | 100 | > | key_cache_segments | 0 | > | large_files_support | ON | > | large_page_size | 0 | > | large_pages | OFF | > | lc_messages | en_US | > | lc_messages_dir | /usr/share/mysql | > | lc_time_names | en_US | > | license | GPL | > | local_infile | ON | > | lock_wait_timeout | 31536000 | > | locked_in_memory | OFF | > | log | OFF | > | log_bin | ON | > | log_bin_trust_function_creators | OFF | > | log_error | | > | log_output | FILE | > | log_queries_not_using_indexes | ON | > | log_slave_updates | ON | > | log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk | > | log_slow_queries | ON | > | log_slow_rate_limit | 1 | > | log_slow_verbosity | query_plan | > | log_warnings | 2 | > | long_query_time | 3.000000 | > | low_priority_updates | OFF | > | lower_case_file_system | OFF | > | lower_case_table_names | 0 | > | master_verify_checksum | OFF | > | max_allowed_packet | 16777216 | > | max_binlog_cache_size | 18446744073709547520 | > | max_binlog_size | 104857600 | > | max_binlog_stmt_cache_size | 18446744073709547520 | > | max_connect_errors | 10 | > | max_connections | 100 | > | max_delayed_threads | 20 | > | max_error_count | 64 | > | max_heap_table_size | 16777216 | > | max_insert_delayed_threads | 20 | > | max_join_size | 18446744073709551615 | > | max_length_for_sort_data | 1024 | > | max_long_data_size | 16777216 | > | max_prepared_stmt_count | 16382 | > | max_relay_log_size | 0 | > | max_seeks_for_key | 4294967295 | > | max_sort_length | 1024 | > | max_sp_recursion_depth | 0 | > | max_tmp_tables | 32 | > | max_user_connections | 0 | > | max_write_lock_count | 4294967295 | > | metadata_locks_cache_size | 1024 | > | min_examined_row_limit | 0 | > | mrr_buffer_size | 262144 | > | multi_range_count | 256 | > | myisam_block_size | 1024 | > | myisam_data_pointer_size | 6 | > | myisam_max_sort_file_size | 9223372036853727232 | > | myisam_mmap_size | 18446744073709551615 | > | myisam_recover_options | BACKUP,QUICK | > | myisam_repair_threads | 1 | > | myisam_sort_buffer_size | 536870912 | > | myisam_stats_method | nulls_unequal | > | myisam_use_mmap | OFF | > | net_buffer_length | 16384 | > | net_read_timeout | 30 | > | net_retry_count | 10 | > | net_write_timeout | 60 | > | old | OFF | > | old_alter_table | OFF | > | old_passwords | OFF | > | open_files_limit | 2159 | > | optimizer_prune_level | 1 | > | optimizer_search_depth | 62 | > | optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=off | > | performance_schema | OFF | > | performance_schema_events_waits_history_long_size | 10000 | > | performance_schema_events_waits_history_size | 10 | > | performance_schema_max_cond_classes | 80 | > | performance_schema_max_cond_instances | 1000 | > | performance_schema_max_file_classes | 50 | > | performance_schema_max_file_handles | 32768 | > | performance_schema_max_file_instances | 10000 | > | performance_schema_max_mutex_classes | 200 | > | performance_schema_max_mutex_instances | 1000000 | > | performance_schema_max_rwlock_classes | 30 | > | performance_schema_max_rwlock_instances | 1000000 | > | performance_schema_max_table_handles | 100000 | > | performance_schema_max_table_instances | 50000 | > | performance_schema_max_thread_classes | 50 | > | performance_schema_max_thread_instances | 1000 | > | pid_file | /var/run/mysqld/mysqld.pid | > | plugin_dir | /usr/lib/mysql/plugin/ | > | plugin_maturity | unknown | > | port | 3306 | > | preload_buffer_size | 32768 | > | profiling | OFF | > | profiling_history_size | 15 | > | progress_report_time | 56 | > | protocol_version | 10 | > | query_alloc_block_size | 8192 | > | query_cache_limit | 131072 | > | query_cache_min_res_unit | 4096 | > | query_cache_size | 33554432 | > | query_cache_strip_comments | OFF | > | query_cache_type | ON | > | query_cache_wlock_invalidate | OFF | > | query_prealloc_size | 8192 | > | range_alloc_block_size | 4096 | > | read_buffer_size | 1048576 | > | read_only | ON | > | read_rnd_buffer_size | 524288 | > | relay_log | | > | relay_log_index | | > | relay_log_info_file | relay-log.info | > | relay_log_purge | ON | > | relay_log_recovery | OFF | > | relay_log_space_limit | 0 | > | replicate_annotate_row_events | OFF | > | replicate_do_db | | > | replicate_do_table | | > | replicate_events_marked_for_skip | replicate | > | replicate_ignore_db | | > | replicate_ignore_table | peoplesforum.cache | > | replicate_wild_do_table | | > | replicate_wild_ignore_table | peoplesforum.cache% | > | report_host | greeneggs | > | report_password | | > | report_port | 3306 | > | report_user | | > | rowid_merge_buff_size | 8388608 | > | rpl_recovery_rank | 0 | > | secure_auth | OFF | > | secure_file_priv | | > | server_id | 12302 | > | skip_external_locking | ON | > | skip_name_resolve | ON | > | skip_networking | OFF | > | skip_show_database | OFF | > | slave_compressed_protocol | OFF | > | slave_exec_mode | STRICT | > | slave_load_tmpdir | /tmp | > | slave_max_allowed_packet | 1073741824 | > | slave_net_timeout | 3600 | > | slave_skip_errors | 1062 | > | slave_sql_verify_checksum | ON | > | slave_transaction_retries | 10 | > | slave_type_conversions | | > | slow_launch_time | 2 | > | slow_query_log | ON | > | slow_query_log_file | /var/log/mysql/mariadb-slow.log | > | socket | /var/run/mysqld/mysqld.sock | > | sort_buffer_size | 262144 | > | sql_auto_is_null | OFF | > | sql_big_selects | ON | > | sql_big_tables | OFF | > | sql_buffer_result | OFF | > | sql_log_bin | ON | > | sql_log_off | OFF | > | sql_low_priority_updates | OFF | > | sql_max_join_size | 18446744073709551615 | > | sql_mode | NO_ENGINE_SUBSTITUTION | > | sql_notes | ON | > | sql_quote_show_create | ON | > | sql_safe_updates | OFF | > | sql_select_limit | 18446744073709551615 | > | sql_slave_skip_counter | 0 | > | sql_warnings | OFF | > | ssl_ca | | > | ssl_capath | | > | ssl_cert | | > | ssl_cipher | | > | ssl_key | | > | storage_engine | InnoDB | > | stored_program_cache | 256 | > | sync_binlog | 3 | > | sync_frm | ON | > | sync_master_info | 0 | > | sync_relay_log | 0 | > | sync_relay_log_info | 0 | > | system_time_zone | UTC | > | table_definition_cache | 400 | > | table_open_cache | 1024 | > | thread_cache_size | 128 | > | thread_concurrency | 10 | > | thread_handling | one-thread-per-connection | > | thread_pool_idle_timeout | 60 | > | thread_pool_max_threads | 500 | > | thread_pool_oversubscribe | 3 | > | thread_pool_size | 8 | > | thread_pool_stall_limit | 500 | > | thread_stack | 294912 | > | time_format | %H:%i:%s | > | time_zone | SYSTEM | > | timed_mutexes | OFF | > | tmp_table_size | 16777216 | > | tmpdir | /tmp | > | transaction_alloc_block_size | 8192 | > | transaction_prealloc_size | 4096 | > | tx_isolation | REPEATABLE-READ | > | unique_checks | ON | > | updatable_views_with_limit | YES | > | userstat | OFF | > | version | 5.5.32-MariaDB-1~wheezy-log | > | version_comment | mariadb.org binary distribution | > | version_compile_machine | x86_64 | > | version_compile_os | debian-linux-gnu | > | wait_timeout | 600 | > I'm planning on doing a debug build from MDEV-572 and maybe try to get valgrind to narrow it down (if that doesn't bring the server to a total halt). Better suggestions welcome. -- This message was sent by Atlassian JIRA (v6.2-OD-05-4#6207) ----- End forwarded message -----

1 0

[Maria-developers] MDEV-5115 review
by Kristian Nielsen 02 Jan '14

02 Jan '14

Hi Serg, Here is my review of your MDEV-5115 patch. First, a general comment: Another way to fix this would be a smaller patch. It would not attempt to implement anything related to the new event types. Instead, it would just have a small bit of code that allows to read the binary format of the new event types, but it would represent them internally as the old type, just ignoring any of the extra information, which appears to be currently unused outside of NDB. Probably then it would also make sense to not implement the renaming of the old event types. There would just be three extra cases in Log_event::read_log_event(), and a bit of extra code in the *_rows_log_event construstructors to be able to ignore any extra info. However, I do not really have much against your approach of doing a partial merge to get code that is closer to what is in MySQL, if you prefer that. I mention it because we discussed briefly already on IRC whether a smaller patch could be enough. A few detailed comments below. Otherwise, while I would have probably done the minimal patch myself, I am ok with this way also. So ok to push after fixing the few commit and code comments mentioned below. - Kristian. > ------------------------------------------------------------ > revno: 3921 > revision-id: sergii(a)pisem.net-20131203192301-afokgpslfq0f3xg8 > parent: sergii(a)pisem.net-20131201111624-xe3f1ul6otcyvyom > fixes bug: https://mariadb.atlassian.net/browse/MDEV-5115 > committer: Sergei Golubchik <sergii(a)pisem.net> > branch nick: 10.0 > timestamp: Tue 2013-12-03 20:23:01 +0100 > message: > MDEV-5115 RBR from MySQL 5.6 to MariaDB 10.0 does not work > > Patially merge WL#5917, to understand v2 row events "Partially merge WL#5917, which introduces new event types for row-based binlogging. This is needed to be able to replicate from a >=5.6 MySQL master to a MariaDB slave." > === modified file 'mysql-test/suite/binlog/t/binlog_old_versions.test' > --- mysql-test/suite/binlog/t/binlog_old_versions.test 2013-04-09 21:27:41 +0000 > +++ mysql-test/suite/binlog/t/binlog_old_versions.test 2013-12-03 19:23:01 +0000 > @@ -24,6 +24,17 @@ > > source include/not_embedded.inc; > > +--echo ==== Read binlog with v2 row events ==== > + > +# Read binlog. > +--exec $MYSQL_BINLOG --local-load=$MYSQLTEST_VARDIR/tmp/ suite/binlog/std_data/ver_trunk_row_v2.001 | $MYSQL --local-infile=1 > +# Show result. > +SELECT * FROM t1 ORDER BY a; > +SELECT * FROM t2 ORDER BY a; > +SELECT COUNT(*) FROM t3; > +# Reset. > +DROP TABLE t1, t2, t3; > + > > --echo ==== Read modern binlog (version 5.1.23) ==== > Is this the only test we have of reading the v2 row event types? I think it is not a particularly good test, as it tests mysqlbinlog reading them, while the interesting thing is a slave receiving them from the master. Even though some code is shared between the two there are lots of differences as well. The better test would be to install the pre-generated binlog file manually on a master and have the slave replicate it. However, it is easy to end up spending too much time on setting up these replication tests and getting them to work reliably on all platforms, so maybe not worth it. Maybe a good compromise is to instead ask Elena to test it manually against a real MySQL 5.6 server - after all, that is the actual case we want to work. > === modified file 'sql/log_event.h' > --- sql/log_event.h 2013-11-01 11:00:11 +0000 > +++ sql/log_event.h 2013-12-03 19:23:01 +0000 > @@ -659,11 +663,11 @@ enum Log_event_type > PRE_GA_DELETE_ROWS_EVENT = 22, > > /* > - These event numbers are used from 5.1.16 and forward > + These event numbers are used from 5.1.16 until mysql-trunk-xx Please change this comment to explain the actual situation. Ie. that they are used in MariaDB, but not in MySQL >= 5.6. > @@ -677,6 +681,20 @@ enum Log_event_type > HEARTBEAT_LOG_EVENT= 27, > > /* > + In some situations, it is necessary to send over ignorable > + data to the slave: data that a slave can handle in case there > + is code for handling it, but which can be ignored if it is not > + recognized. > + */ > + IGNORABLE_LOG_EVENT= 28, > + ROWS_QUERY_LOG_EVENT= 29, Add a comment that these are MySQL 5.6 events that are unimplemented in MariaDB. (Do we need another MDEV to be able to replicate against a MySQL master that produces ROWS_QUERY_LOG_EVENT? It is quite similar to our ANNOTATE_ROWS_EVENT, IIRC). > + /* Version 2 of the Row events */ > + WRITE_ROWS_EVENT = 30, > + UPDATE_ROWS_EVENT = 31, > + DELETE_ROWS_EVENT = 32, Add comment that these are only generated by MySQL 5.6.

2 1

[Maria-developers] review: [Commits] Rev 3917: MDEV-9095: Executing triggers on slave in row-based replication in file:///home/bell/maria/bzr/work-maria-10.0-MDEV-5095/
by Michael Widenius 19 Dec '13

19 Dec '13

Hi! > === modified file 'sql/log_event.cc' > === modified file 'sql/sql_delete.cc' > --- a/sql/sql_delete.cc 2013-10-16 09:38:42 +0000 > +++ b/sql/sql_delete.cc 2013-12-19 08:10:00 +0000 > @@ -525,12 +525,7 @@ bool mysql_delete(THD *thd, TABLE_LIST * > table->triggers->has_triggers(TRG_EVENT_DELETE, > TRG_ACTION_AFTER)) > { > - /* > - The table has AFTER DELETE triggers that might access to subject table > - and therefore might need delete to be done immediately. So we turn-off > - the batching. > - */ > - (void) table->file->extra(HA_EXTRA_DELETE_CANNOT_BATCH); > + table->prepare_triggers_for_delete_stmt_or_event(); > will_batch= FALSE; > } In the above code, you check for this twice: table->triggers->has_triggers(TRG_EVENT_DELETE, TRG_ACTION_AFTER)) Would be better to have prepare_triggers_for_delete_stmt_or_event() return 1 if we have delete triggers and do: if (table->prepare_triggers_for_delete_stmt_or_event()) will_batch= FALSE; else will_batch= ... <cut> > === modified file 'sql/sql_update.cc' > --- a/sql/sql_update.cc 2013-10-16 09:38:42 +0000 > +++ b/sql/sql_update.cc 2013-12-19 08:10:00 +0000 > @@ -707,12 +707,7 @@ int mysql_update(THD *thd, > table->triggers->has_triggers(TRG_EVENT_UPDATE, > TRG_ACTION_AFTER)) > { > - /* > - The table has AFTER UPDATE triggers that might access to subject > - table and therefore might need update to be done immediately. > - So we turn-off the batching. > - */ > - (void) table->file->extra(HA_EXTRA_UPDATE_CANNOT_BATCH); > + table->prepare_triggers_for_update_stmt_or_event(); > will_batch= FALSE; > } Same here. Regards, Monty

1 0

[Maria-developers] review of mdev-5095 (executing triggers on slave)
by Michael Widenius 19 Dec '13

19 Dec '13

Hi! Here is the review of mdev-5095 I got the diff from: bzr diff -r3942.. > === modified file 'mysql-test/r/mysqld--help.result' > --- mysql-test/r/mysqld--help.result 2012-10-18 21:33:06 +0000 > +++ mysql-test/r/mysqld--help.result 2013-12-03 10:53:01 +0000 > @@ -747,6 +747,14 @@ > --slave-net-timeout=# > Number of seconds to wait for more data from a > master/slave connection before aborting the read > + --slave-run-triggers-for-rbr=name > + Modes for how triggers in row-base replication on slave > + side will be executed. Legal values are NO (default), YES > + and LOGGING. NO means that trigger for RBR will not be > + running on slave YES and LOGGING means that triggers will > + be running on slave (if there was not triggers running on > + the naster), LOGGING also mens that flag about executed > + triggers will be written to binlog. Suggested new text: --slave-run-triggers-for-rbr=name Modes for how triggers in row-base replication on slave side will be executed. Legal values are NO (default), YES and LOGGING. NO means that trigger for RBR will not be running on slave. YES and LOGGING means that triggers will be running on slave, if there was not triggers running on the master for the statement. LOGGING also means that the executed triggers will be written to binlog. <cut> > === modified file 'sql/log_event.cc' <cut> > -#if !defined(MYSQL_CLIENT) && defined(HAVE_REPLICATION) > +#if defined(MYSQL_SERVER) && defined(HAVE_REPLICATION) Was the above needed or mostly an optimization? > + > +static void restore_empty_query_table_list(LEX *lex) > +{ > + if (lex->first_not_own_table()) > + (*lex->first_not_own_table()->prev_global)= NULL; > + lex->query_tables= NULL; > + lex->query_tables_last= &lex->query_tables; > +} Add a comment of what is the purpose of the above function. > @@ -8340,6 +8351,22 @@ int Rows_log_event::do_apply_event(Relay > /* A small test to verify that objects have consistent types */ > DBUG_ASSERT(sizeof(thd->variables.option_bits) == sizeof(OPTION_RELAXED_UNIQUE_CHECKS)); > > + if (slave_run_triggers_for_rbr) > + { > + LEX *lex= thd->lex; > + uint8 new_trg_event_map= get_trg_event_map(); > + > + DBUG_ASSERT(lex->query_tables == NULL); > + if ((lex->query_tables= rli->tables_to_lock)) > + rli->tables_to_lock->prev_global= &lex->query_tables; Add a comment why we have to set prev_global (not obvious). It's also not obvious why restore_empty_tables() is not resetting rli->tables_to_lock->prev_global as it's reseting the the rest of the changed things. > @@ -8429,8 +8462,11 @@ int Rows_log_event::do_apply_event(Relay > */ > TABLE_LIST *ptr= rli->tables_to_lock; > for (uint i=0 ; ptr && (i < rli->tables_to_lock_count); ptr= ptr->next_global, i++) > + { > const_cast<Relay_log_info*>(rli)->m_table_map.set_table(ptr->table_id, ptr->table); > - What does this test really do ? (I was quickly checking the code to understand what m_table_id stands for, but it was not obvious). > + if (m_table_id == ptr->table_id) > + master_had_triggers= ((RPL_TABLE_LIST*)ptr)->master_had_triggers; <cut> > if (table) > { > bool transactional_table= table->file->has_transactions(); > @@ -8618,6 +8656,9 @@ int Rows_log_event::do_apply_event(Relay > */ > thd->reset_current_stmt_binlog_format_row(); > thd->is_slave_error= 1; > + /* remove trigger's tables */ > + if (slave_run_triggers_for_rbr) > + restore_empty_query_table_list(thd->lex); > DBUG_RETURN(error); > } You do the above 'restore_empty_query_table_list(thd->lex);' before every return in this function, except the last return. - Isn't it better to do a 'error:' tag at the end of the function to ensure we don't miss any cases ? - Add a comment why we don't need to do this at the very end of Rows_log_event::do_apply_event(). Something like: /* We don't have to call restore_empty_query_table_list() here as it's called in rows_event_stmt_cleanup(). */ When is lex->query_tables used? Is it only used by 'open' ? If yes, isn't it enough to always reset it after the 'open' call? > @@ -9773,6 +9831,29 @@ Write_rows_log_event::do_before_row_oper > from the start. > */ > } > + if (slave_run_triggers_for_rbr && !master_had_triggers && m_table->triggers ) > + { > + if (m_table->triggers->has_triggers(TRG_EVENT_DELETE, > + TRG_ACTION_AFTER)) > + { > + /* > + The table has AFTER DELETE triggers that might access to > + subject table and therefore might need delete to be done > + immediately. So we turn-off the batching. > + */ > + (void) m_table->file->extra(HA_EXTRA_DELETE_CANNOT_BATCH); > + } > + if (m_table->triggers->has_triggers(TRG_EVENT_UPDATE, > + TRG_ACTION_AFTER)) > + { > + /* > + The table has AFTER UPDATE triggers that might access to subject > + table and therefore might need update to be done immediately. > + So we turn-off the batching. > + */ > + (void) m_table->file->extra(HA_EXTRA_UPDATE_CANNOT_BATCH); > + } > + } Good that you noticed and fixed the above. However, why not just call the function prepare_triggers_for_insert_stmt() that does exactly the same thing? <cut> > @@ -10749,6 +10894,17 @@ Delete_rows_log_event::do_before_row_ope > */ > return 0; > } > + if (slave_run_triggers_for_rbr && !master_had_triggers && m_table->triggers && > + m_table->triggers->has_triggers(TRG_EVENT_DELETE, > + TRG_ACTION_AFTER)) > + { > + /* > + The table has AFTER DELETE triggers that might access to subject table > + and therefore might need delete to be done immediately. So we turn-off > + the batching. > + */ > + (void) m_table->file->extra(HA_EXTRA_DELETE_CANNOT_BATCH); > + } Would be better if we could do a function 'prepare_triggers_for_delete_stmt()' and use the same code here and in sql_delete.cc <cut> > @@ -10877,6 +11049,18 @@ Update_rows_log_event::do_before_row_ope > > m_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET; > > + if (slave_run_triggers_for_rbr && !master_had_triggers && m_table->triggers && > + m_table->triggers->has_triggers(TRG_EVENT_UPDATE, > + TRG_ACTION_AFTER)) > + { > + /* > + The table has AFTER UPDATE triggers that might access to subject > + table and therefore might need update to be done immediately. > + So we turn-off the batching. > + */ > + (void) m_table->file->extra(HA_EXTRA_UPDATE_CANNOT_BATCH); > + } > + Would be better if we could do a function 'prepare_triggers_for_update_stmt()' and use the same code here and in sql_update.cc <cut> Regards, Monty

2 4

[Maria-developers] FW: Rev 3758: MDEV-5470: Cost-based subquery item pushdown must take into account semi-joins
by Sergei Petrunia 19 Dec '13

19 Dec '13

Hi Timour, Please find below a patch that should implement execution-time protection against malfunction between condition moving and semi-joins. There needs also to be an optimization-time counterpart, which should have the same logic. Will you code it yourself or want me to? ----- Forwarded message from Sergei Petrunia <psergey(a)askmonty.org> ----- Date: Thu, 19 Dec 2013 02:49:22 +0400 From: Sergei Petrunia <psergey(a)askmonty.org> To: commits(a)mariadb.org Subject: Rev 3758: MDEV-5470: Cost-based subquery item pushdown must take into account semi-joins At http://bazaar.launchpad.net/~maria-captains/maria/10.0-mdev83 ------------------------------------------------------------ revno: 3758 committer: Sergey Petrunya <psergey(a)askmonty.org> branch nick: 10.0-mdev83 timestamp: Thu 2013-12-19 06:50:28 +0400 message: MDEV-5470: Cost-based subquery item pushdown must take into account semi-joins - When moving expensive conditions, take into account that semi-joins limit where they can be moved. - This is only execution part in JOIN::setup_dynamic_conditions(). Optimization part is not yet present. - No testcases yet. - Moving inside SJ-Materialization nests doesn't seem to be supported (either before this patch or after. This patch itself does take SJ-Materialization into account) === modified file 'sql/item_cmpfunc.cc' --- sql/item_cmpfunc.cc 2013-11-01 11:10:38 +0000 +++ sql/item_cmpfunc.cc 2013-12-19 02:50:28 +0000 @@ -6591,6 +6591,8 @@ void Item_func_collect_stats::dbug_print } #endif /*DBUG_OFF*/ +JOIN_TAB *find_last_tab_for_dynamic_cond(table_map cond_tables, + JOIN *join, JOIN_TAB *join_tab); Item_func_dynamic_cond::Item_func_dynamic_cond(Item *cond, JOIN_TAB *atab, JOIN_TAB **ctab) : Item_func_collect_stats(cond) @@ -6624,13 +6626,15 @@ Item_func_dynamic_cond::Item_func_dynami if (active_tab->bush_root_tab) { first_tab= active_tab->bush_root_tab->bush_children->start; - last_tab= active_tab->bush_root_tab->bush_children->end; + //last_tab= active_tab->bush_root_tab->bush_children->end; } else { first_tab= join->join_tab + join->const_tables; - last_tab= join->join_tab + join->top_join_tab_count; + //last_tab= join->join_tab + join->top_join_tab_count; } + last_tab= find_last_tab_for_dynamic_cond(cond->used_tables(), + active_tab->join, active_tab); DBUG_ASSERT(active_tab >= first_tab && active_tab < last_tab); === modified file 'sql/item_cmpfunc.h' --- sql/item_cmpfunc.h 2013-10-18 19:56:55 +0000 +++ sql/item_cmpfunc.h 2013-12-19 02:50:28 +0000 @@ -561,14 +561,20 @@ class Item_func_collect_stats : public I class Item_func_dynamic_cond : public Item_func_collect_stats { -protected: +protected: // as if there were any ancestor classes? List<Item_subselect> subqueries; /* The join_tab where the condition is currently 'pushed'.*/ st_join_table *active_tab; /* The join_tab currently being executed. */ st_join_table **exec_tab; /* The boundaries of the sub-plan where this predicate can be pushed. */ - st_join_table *first_tab, *last_tab; + st_join_table *first_tab; + st_join_table *last_tab; //psergey-todo: this should point to the last tab. + // conditions depending on semi-join tables have + // limits re. how far they can be moved. +public: + inline st_join_table * get_last_tab() { return last_tab; } +protected: /* The relative access cost based on optimizer estimates. This is the cost of all access methods per one record from the first table in the plan. === modified file 'sql/opt_subselect.cc' --- sql/opt_subselect.cc 2013-11-04 15:56:09 +0000 +++ sql/opt_subselect.cc 2013-12-19 02:50:28 +0000 @@ -1480,6 +1480,7 @@ static bool convert_subq_to_sj(JOIN *par sj_nest->sj_subq_pred= subq_pred; sj_nest->original_subq_pred_used_tables= subq_pred->used_tables() | subq_pred->left_expr->used_tables(); + sj_nest->sj_strategys_join_tab= uint(-1); /* Nests do not participate in those 'chains', so: */ /* sj_nest->next_leaf= sj_nest->next_local= sj_nest->next_global == NULL*/ emb_join_list->push_back(sj_nest); @@ -4321,6 +4322,21 @@ int init_dups_weedout(JOIN *join, uint f /* + For each SJ-inner table in [start_tab; end_tab], set emb_sj_nest to idx +*/ +static void update_sj_strategys_join_tab(JOIN_TAB *start_tab, + JOIN_TAB *end_tab, + uint idx) +{ + for (JOIN_TAB *j= start_tab; j < end_tab; j++) + { + TABLE_LIST *emb_nest; + if ((emb_nest= j->emb_sj_nest)) + emb_nest->sj_strategys_join_tab= idx; + } +} + +/* Setup the strategies to eliminate semi-join duplicates. SYNOPSIS @@ -4448,6 +4464,10 @@ int setup_semijoin_dups_elimination(JOIN for (uint j= i; j < i + pos->n_sj_tables; j++) join->join_tab[j].inside_loosescan_range= TRUE; + + update_sj_strategys_join_tab(join->join_tab + i, + join->join_tab + i + pos->n_sj_tables, + i + pos->n_sj_tables - 1); /* Calculate key length */ keylen= 0; @@ -4461,6 +4481,7 @@ int setup_semijoin_dups_elimination(JOIN tab[pos->n_sj_tables - 1].do_firstmatch= tab; i+= pos->n_sj_tables; pos+= pos->n_sj_tables; + break; } case SJ_OPT_DUPS_WEEDOUT: @@ -4503,6 +4524,9 @@ int setup_semijoin_dups_elimination(JOIN break; } } + update_sj_strategys_join_tab(join->join_tab + i, + join->join_tab + i + pos->n_sj_tables, + i + pos->n_sj_tables - 1); init_dups_weedout(join, first_table, i, i + pos->n_sj_tables - first_table); i+= pos->n_sj_tables; @@ -4558,6 +4582,9 @@ int setup_semijoin_dups_elimination(JOIN } } j[-1].do_firstmatch= jump_to; + update_sj_strategys_join_tab(join->join_tab + i, + join->join_tab + i + pos->n_sj_tables, + i + pos->n_sj_tables - 1); i+= pos->n_sj_tables; pos+= pos->n_sj_tables; === modified file 'sql/sql_select.cc' --- sql/sql_select.cc 2013-11-08 12:36:02 +0000 +++ sql/sql_select.cc 2013-12-19 02:50:28 +0000 @@ -24608,8 +24608,8 @@ double JOIN::static_pushdown_cost(uint i @param pred the predicate to be wrapped in Item_func_dynamic_cond @param init_tab the initial 'active' JOIN_TAB where the dynamic predicate is pushed to - @param cur_tab pointer to the JOIN_TAB* that stores the current JOIN_TAB - during execution + @param active_tab_ptr pointer to the JOIN_TAB* that stores the current JOIN_TAB + during execution */ static Item_func_collect_stats * wrap_with_dynamic_conds(Item *pred, JOIN_TAB *init_tab, JOIN_TAB **active_tab_ptr, @@ -24674,6 +24674,81 @@ wrap_with_dynamic_conds(Item *pred, JOIN } +/* + @brief Find the last JOIN_TAB that a condition can be attached. + + + @param cond_tables used_tables() of the condition that we intend to move. + @param join_tab The left-most join tab where the condition can be + attached (in other words, default attachment point) + + @detail + In a regular inner join, one can take a condition that is attached to a + table $T, and move it to any table that comes after $T. + + When semi-joins are present, this is no longer true. Detailed explanation + can be found in maria-developers@, email "Movable conditions and semi-joins". + In short: + + - Inside an SJ-Materialization nest, a condition can be moved to any table to + the right, but it must stay within the nest. (We don't support nested + semi-joins, so one will not find semi-joins inside an SJ-M nest) + + - All other Semi-Join strategies have a "range" where they apply. If a + condition depends on a table that is within a semi-join, it must not be + moved over the right edge of the range. +*/ + +JOIN_TAB *find_last_tab_for_dynamic_cond(table_map cond_tables, + JOIN *join, JOIN_TAB *join_tab) +{ + if (join_tab->bush_root_tab) + { + /* + We are inside SJ-Materialization nest. There are no semi-joins here, + return the last tab in the nest. + */ + return join_tab->bush_root_tab->bush_children->end; + } + + /* Start from the right-most bound */ + uint last_tab = join->top_join_tab_count; + + cond_tables= cond_tables & ~(PSEUDO_TABLE_BITS | join->const_table_map); + /* + Walk through semi-joins that this table depends on, and check their last + join tabs. The smallest last join tab sets the limit about how far right + the condition can be moved. + */ + Table_map_iterator tm_it(cond_tables); + int tableno; + while ((tableno = tm_it.next_bit()) != Table_map_iterator::BITMAP_END) + { + TABLE_LIST *sj_nest; + if ((sj_nest= join->map2table[tableno]->emb_sj_nest)) + { + if (sj_nest->is_active_sjm()) + { + /* + Whoa this condition also depends on a table from within + SJ-Materialization nest. (Can happen possible because of equality + propagation/substitution). If we didn't return earlier in this + function, we're not inside a semi-join nest. A value that depends + on a table within a SJ-Materialization nest means that it depends on + the materialized table columns. + */ + continue; + } + + if (sj_nest->sj_strategys_join_tab != uint(-1) && + sj_nest->sj_strategys_join_tab < last_tab) + last_tab= sj_nest->sj_strategys_join_tab; + } + } + return join->join_tab + last_tab; +} + + /** Make expensive subqueries dynamically pushdownable. */ @@ -24703,7 +24778,12 @@ bool JOIN::setup_dynamic_conditions() Update the least cardinality join_tab for each tab for the chosen join order. Check if there are any conditions at all that need to be made dynamic. */ - min_tab= NULL; + min_tab= NULL; + /* + psergey-todo: the following loop should walk inside SJ-Materialization + nests, too. Maybe, this whole function should be invoked for each + SJ-Materilization nest. + */ for (tab= last_tab - 1; tab >= first_tab; tab--) { if (optimizer_flag(thd, OPTIMIZER_SWITCH_STATIC_COND_PUSHDOWN)) @@ -24746,7 +24826,8 @@ bool JOIN::setup_dynamic_conditions() */ for (tab= first_tab; tab < last_tab; tab++) { - Item_func_collect_stats *wrapped_select_cond= NULL, *cur_wrapped_cond; + Item_func_collect_stats *wrapped_select_cond= NULL; + Item_func_dynamic_cond *cur_wrapped_cond; if (tab->select_cond && !(wrapped_select_cond= @@ -24800,14 +24881,29 @@ bool JOIN::setup_dynamic_conditions() Add all dynamic conditions pushed to previous JOIN_TABs also to the current JOIN_TAB. This allows to "move" a dynamic condition from one tab to another by enabling it for the corrseponding tab. + + psergey-todo: conditions have limits on how far to the right they can be + moved (Item_func_dynamic_cond::last_tab). Don't attach conditions to + where they can't be moved. */ if (!prev_dynamic_conds.is_empty()) { - List_iterator_fast<Item_func_dynamic_cond> dyn_cond_it(prev_dynamic_conds); - if (!wrapped_select_cond) - wrapped_select_cond= dyn_cond_it++; + List_iterator<Item_func_dynamic_cond> dyn_cond_it(prev_dynamic_conds); while ((cur_wrapped_cond= dyn_cond_it++)) - wrapped_select_cond->add_pred(cur_wrapped_cond); + { + if (!wrapped_select_cond) + wrapped_select_cond= cur_wrapped_cond; + else + wrapped_select_cond->add_pred(cur_wrapped_cond); + + /* + psergey-todo: conditions have limits on how far to the right they can be + moved (Item_func_dynamic_cond::last_tab). Don't attach conditions to + where they can't be moved. + */ + if (cur_wrapped_cond->get_last_tab() == tab) + dyn_cond_it.remove(); + } } prev_dynamic_conds.concat(&cur_dynamic_conds); cur_dynamic_conds.empty(); === modified file 'sql/table.h' --- sql/table.h 2013-09-25 18:07:06 +0000 +++ sql/table.h 2013-12-19 02:50:28 +0000 @@ -1721,6 +1721,9 @@ struct TABLE_LIST table_map sj_inner_tables; /* Number of IN-compared expressions */ uint sj_in_exprs; + + //psergey-todo: add the index of last table in the join order + uint sj_strategys_join_tab; /* If this is a non-jtbm semi-join nest: corresponding subselect predicate */ Item_in_subselect *sj_subq_pred; ----- End forwarded message ----- -- BR Sergei -- Sergei Petrunia, Software Developer MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] MariaDB r3955 make fail; " error: ‘isfinite’ was not declared in this scope"
by darx＠sent.com 19 Dec '13

19 Dec '13

hi, upgrading a MariaDB 10/git src build from r3911 -> r3955, on x86_64 after configure, make fails with a warning & an error @, ... [ 19%] Building CXX object storage/connect/CMakeFiles/connect.dir/ha_connect.cc.o cd /usr/local/src/mariadb/bld/storage/connect && /usr/bin/g++-4.8 -DFORCE_INIT_OF_VARS -DHAVE_CONFIG_H -DHUGE_SUPPORT -DLIBXML2_SUPPORT -DLINUX -DMARIADB -DMYSQL_DYNAMIC_PLUGIN -DMYSQL_SUPPORT -DODBC_SUPPORT -DPIVOT_SUPPORT -DUBUNTU -DUNIX -DZIP_SUPPORT -Dconnect_EXPORTS -Wall -O2 -fmessage-length=0 -D_FORTIFY_SOURCE=2 -fstack-protector -march=amdfam10 -mtune=amdfam10 -std=c++11 -felide-constructors -fno-exceptions -fno-rtti -Wall -Wno-unused-parameter -fno-exceptions -fno-rtti -fpermissive -fexceptions -fPIC -O2 -g -DNDEBUG -DDBUG_OFF -DMY_PTHREAD_FASTMUTEX=1 -fPIC -I/usr/local/src/mariadb/bld/include -I/usr/include/libxml2 -I/usr/local/src/mariadb/include -I/usr/local/src/mariadb/sql -I/usr/local/src/mariadb/bld/pcre -I/usr/local/src/mariadb/pcre -I/usr/local/ssl/include -O2 -fmessage-length=0 -D_FORTIFY_SOURCE=2 -fstack-protector -march=amdfam10 -mtune=amdfam10 -Wall -Wmissing-declarations -Wno-write-strings -Wno-unused-variable -Wno-unused-value -Wno-unused-function -Wno-parentheses -o CMakeFiles/connect.dir/ha_connect.cc.o -c /usr/local/src/mariadb/storage/connect/ha_connect.cc In file included from /usr/local/src/mariadb/sql/gcalc_tools.h:21:0, from /usr/local/src/mariadb/sql/spatial.h:28, from /usr/local/src/mariadb/sql/item.h:3667, from /usr/local/src/mariadb/sql/sql_lex.h:26, from /usr/local/src/mariadb/sql/sql_class.h:463, from /usr/local/src/mariadb/storage/connect/ha_connect.cc:104: /usr/local/src/mariadb/sql/gcalc_slicescan.h:29:40: warning: invalid suffix on literal; C++11 requires a space between literal and identifier [-Wliteral-suffix] #define GCALC_DBUG_ENTER(a) DBUG_ENTER("Gcalc "a) ^ In file included from /usr/local/src/mariadb/sql/item.h:3669:0, from /usr/local/src/mariadb/sql/sql_lex.h:26, from /usr/local/src/mariadb/sql/sql_class.h:463, from /usr/local/src/mariadb/storage/connect/ha_connect.cc:104: /usr/local/src/mariadb/sql/item_func.h: In member function ‘double Item_func::check_float_overflow(double)’: /usr/local/src/mariadb/sql/item_func.h:281:26: error: ‘isfinite’ was not declared in this scope return isfinite(value) ? value : raise_float_overflow(); ^ /usr/local/src/mariadb/sql/item_func.h:281:26: note: suggested alternative: In file included from /usr/include/c++/4.8/random:38:0, from /usr/include/c++/4.8/bits/stl_algo.h:65, from /usr/include/c++/4.8/algorithm:62, from /usr/local/src/mariadb/sql/mdl.h:32, from /usr/local/src/mariadb/sql/table.h:22, from /usr/local/src/mariadb/sql/field.h:29, from /usr/local/src/mariadb/sql/unireg.h:172, from /usr/local/src/mariadb/sql/sql_class.h:24, from /usr/local/src/mariadb/storage/connect/ha_connect.cc:104: /usr/include/c++/4.8/cmath:596:5: note: ‘std::isfinite’ isfinite(_Tp __x) ^ make[2]: *** [storage/connect/CMakeFiles/connect.dir/ha_connect.cc.o] Error 1 make[2]: Leaving directory `/usr/local/src/mariadb/bld' make[1]: *** [storage/connect/CMakeFiles/connect.dir/all] Error 2 make[1]: Leaving directory `/usr/local/src/mariadb/bld' make: *** [all] Error 2

1 0

Re: [Maria-developers] metadata_lock_info plugin
by Sergey Vojtovich 13 Dec '13

13 Dec '13

Hi Kentoku, thanks for your contribution and sorry for this long delay. I finally managed to go through your code. The most important thing I'm worried about is that there is no protection of ticket lists: lists may be updated while we iterate them and it may cause server crash in concurrent environment. From what I can see MDL_context is mostly supposed to be accessed only by thread which owns this context. As such it is not protected by any locks. I believe we should use global lists instead, something like: mysql_mutex_lock(&mdl_locks.m_mutex); iterate mdl_locks.m_locks (hash of MDL_lock) { mysql_prlock_rdlock(&lock->m_rwlock); iterate lock->m_granted (list of MDL_ticket) { // store data } mysql_prlock_unlock(&lock->m_rwlock); } mysql_mutex_lock(&mdl_locks.m_mutex); A few more suggestions in no particular order: - instead of making m_tickets public I'd suggest to try to make i_s_metadata_lock_info_fill_table() a friend function (shouldn't be relevant anymore) - why check for thd->killed? (shouldn't be relevant anymore) - metadata_lock_info_lock_mode_length[] includes ending zero, and then we store e.g. "MDL_SHARED\0" whereas we should store "MDL_SHARED" I'd suggest to declare these arrays as following: static const LEX_STRING metadata_lock_info_lock_mode_str[]= { STRING_WITH_LEN("MDL_INTENTION_EXCLUSIVE"), // or C_STRING_WITH_LEN() ... }; - Roberto suggests to rename ID to THREAD_ID and I agree - Roberto suggests to add QUERY_ID column and I agree - Roberto suggests to add LOCK_DURATION column and I agree - Roberto suggests to use m_namespace_to_wait_state_name instead of metadata_lock_info_lock_names, but I tend to disagree because the former says "Waiting for...". Though I'd suggest to make array of LEX_STRING's to avoid calling strdup(). - Roberto suggests to replace DB with KEY and NAME with SUB_KEY, but I tend to disagree since in most cases it is db and table name. I'd vote for TABLE_SCHEMA and TABLE_NAME to stay in line with the rest I_S tables. Regards, Sergey On Mon, Jul 01, 2013 at 03:46:22AM +0900, kentoku wrote: > Hi Sergey, > Sorry. I think this plugin needs more locks "mdl_locks.m_mutex", > "mdl_locks.m_global_lock" and "mdl_locks.m_commit_lock". Is it right? Would > you please teach me how can I use it? > Thanks, > Kentoku > > > 2013/7/1 kentoku <kentokushiba(a)gmail.com> > > > Hi Sergey, > > > > I made new information schema plugin which named "metadata_lock_info" > > plugin. This plugin makes it possible knowing "who has metadata locks". In > > development stage, sometimes DBAs meet metadata locks when using alter > > table statement. This metadata locks are sometimes caused by GUI tools. In > > service stage, sometimes DBAs meet metadata locks when using alter table > > statement. This metadata locks are sometimes caused by long batch > > processing. In many cases, the DBAs need to judge immediately. So I made it > > for all DBAs. > > Would you please review this plugin? > > lp:~kentokushiba/maria/10.0.3-metadata_lock_info > > Note: This plugin needs a change of sql/mdl.h > > > > Thanks, > > Kentoku > >

3 15

Re: [Maria-developers] Rev 3915: MDEV-5388 - Reduce usage of LOCK_open: unused_tables
by Sergei Golubchik 13 Dec '13

13 Dec '13

Hi, Sergey! Did you benchmark the improvement you get with this patch? On Dec 05, Sergey Vojtovich wrote: > At lp:maria/10.0 > > ------------------------------------------------------------ > revno: 3915 > revision-id: svoj(a)mariadb.org-20131205084430-7rt735tbanw0i6uq > parent: svoj(a)mariadb.org-20130827121233-b9mguuxctnjlq5wd > committer: Sergey Vojtovich <svoj(a)mariadb.org> > branch nick: 10.0 > timestamp: Thu 2013-12-05 12:44:30 +0400 > message: > MDEV-5388 - Reduce usage of LOCK_open: unused_tables > > Removed unused_tables, find LRU object by timestamp instead. > === modified file 'sql/table_cache.cc' > --- a/sql/table_cache.cc 2013-08-27 12:12:33 +0000 > +++ b/sql/table_cache.cc 2013-12-05 08:44:30 +0000 > @@ -324,20 +248,44 @@ void tc_add_table(THD *thd, TABLE *table > DBUG_ASSERT(table->in_use == thd); > mysql_mutex_lock(&LOCK_open); > table->s->tdc.all_tables.push_front(table); > - tc_count++; > /* If we have too many TABLE instances around, try to get rid of them */ > - if (tc_count > tc_size && unused_tables) > + if (tc_count == tc_size) I'd rather put (tc_count >= tc_size), looks safer. > { > - TABLE *purge_table= unused_tables; > - tc_remove_table(purge_table); > - mysql_rwlock_rdlock(&LOCK_flush); > + TDC_iterator tdc_it; > mysql_mutex_unlock(&LOCK_open); > - intern_close_table(purge_table); > - mysql_rwlock_unlock(&LOCK_flush); > - check_unused(thd); > + > + tdc_it.init(); > + mysql_mutex_lock(&LOCK_open); > + if (tc_count == tc_size) and here > + { > + TABLE *purge_table= 0; > + TABLE_SHARE *share; I think a status variable for this would be nice. So that one could make an informed decision about the desired size of the cache. > + while ((share= tdc_it.next())) > + { > + TABLE_SHARE::TABLE_list::Iterator it(share->tdc.free_tables); > + TABLE *entry; > + while ((entry= it++)) > + if (!purge_table || entry->tc_time < purge_table->tc_time) > + purge_table= entry; > + } > + if (purge_table) > + { > + tdc_it.deinit(); > + purge_table->s->tdc.free_tables.remove(purge_table); > + purge_table->s->tdc.all_tables.remove(purge_table); > + mysql_rwlock_rdlock(&LOCK_flush); > + mysql_mutex_unlock(&LOCK_open); > + intern_close_table(purge_table); > + mysql_rwlock_unlock(&LOCK_flush); > + check_unused(thd); > + return; > + } > + } > + tdc_it.deinit(); Okay, so you only purge one table at time? Since purge is kind of an expensive operation, it's better to amortize the cost of it by purging many tables at once. > } > - else > - mysql_mutex_unlock(&LOCK_open); > + /* Nothing to evict, increment tc_count. */ > + tc_count++; > + mysql_mutex_unlock(&LOCK_open); > } > 2 Regards, Sergei

2 6

Re: [Maria-developers] Rev 3914: MDEV-4956 - Reduce usage of LOCK_open: TABLE_SHARE::tdc.used_tables
by Sergei Golubchik 13 Dec '13

13 Dec '13

Hi, Sergey! On Dec 10, Sergey Vojtovich wrote: > At lp:maria/10.0 > > ------------------------------------------------------------ > revno: 3914 > revision-id: svoj(a)mariadb.org-20131210150036-0hwh1gy4z3jalwsg > parent: bar(a)mnogosearch.org-20131203101253-mbz05d9jtkb3iycp > committer: Sergey Vojtovich <svoj(a)mariadb.org> > branch nick: 10.0-mdev4956 > timestamp: Tue 2013-12-10 19:00:36 +0400 > message: > MDEV-4956 - Reduce usage of LOCK_open: TABLE_SHARE::tdc.used_tables > > - tc_acquire_table and tc_release_table do not access > TABLE_SHARE::tdc.used_tables anymore > - in tc_acquire_table(): release LOCK_tdc after we relase LOCK_open > (saves a few CPU cycles in critical section) > - in tc_release_table(): if we reached table cache threshold, evict > to-be-released table without moving it to unused_tables. unused_tables > must be empty at this point. Looks ok to me Regards, Sergei

1 0

[Maria-developers] Issue with index condition pushdown being used with no end_range set
by Zardosht Kasheff 12 Dec '13

12 Dec '13

Hello all, I've noticed a scenario with index condition pushdown (ICP) in MariaDB that leads to really bad performance in TokuDB. I don't know if it is a bug in ICP or if TokuDB is misusing/misunderstanding the API. Here is the problem. Suppose we run the following query on the following schema: SELECT * FROM foo WHERE col1 = '7' ORDER BY b desc | foo | CREATE TABLE `foo` ( `id` int(11) NOT NULL AUTO_INCREMENT, `col1` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`), CLUSTERING KEY `col1_2` (`col1`,`b`) ) ENGINE=TokuDB AUTO_INCREMENT=8001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=TOKUDB_ZLIB | The output of explain is: MariaDB [test]> explain SELECT * FROM foo WHERE col1 = '7' ORDER BY b desc; +------+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ | 1 | SIMPLE | foo | ref | col1_2 | col1_2 | 302 | const | 1320 | Using where | +------+-------------+-------+------+---------------+--------+---------+-------+------+-------------+ 1 row in set (2.32 sec) The query is doing a reverse range scan, as it should. We get handler::idx_cond_push called, but end_range is not set. As a result, TokuDB thinks it can use index condition pushdown to filter rows. As we do this and get to the end, because end_range is not set, we never get a result of ICP_OUT_OF_RANGE, and always get a result of ICP_NO_MATCH. So, when we go to retrieve that first row past the end of the range, the row that will tell MySQL it should stop searching, TokuDB never finds a row and never gets ICP_NO_MATCH. It scans to the beginning of the index (because it is running in reverse order), getting ICP_NO_MATCH for every row it encounters. Although my index is clustering, I've seen this with a normal key as well. If col1 is an int instead of the funky varchar, handler::idx_cond_push is never called, so this problem does not exist. It seems to me that if end_range is not set and we cannot reliably learn when we go out of range, we should not have handler::idx_cond_push called, otherwise we can get bad performance such as the example above. Thoughts? Thanks --Zardosht

1 1

Re: [Maria-developers] [Commits] Rev 3916: MDEV-5403 - Reduce usage of LOCK_open: tc_count
by Sergei Golubchik 11 Dec '13

11 Dec '13

Hi, Sergey! Not a review, only a comment on TDC_atomic part: 1. This is perfectly generally useful, not TDC specific at all. I'd consider moving it to my_atomic.h under #ifdef __cplusplus 2. These are supposed to be trivial inlined functions. I don't think you should use inheritance and virtual methods here. Everything should be in the template. I understand that function names (add64/add32/etc) are a bit tricky. Two approaches: pass them as template parameters (like in template <typename T, ADD, CAS, ...>) or - perhaps, if you put this into my_atomic.h you'll be able to generate the necessary code without using my_atomic_add32/etc ? Regards, Sergei

2 1

Re: [Maria-developers] [svoj@mariadb.org: [Commits] Rev 3807: MDEV-4956 - Reduce usage of LOCK_open: TABLE_SHARE::tdc.used_tables in lp:maria/10.0]
by Sergei Golubchik 11 Dec '13

11 Dec '13

Hi, Sergey! Looks pretty much ok. See few comments/questions below. On Oct 24, Sergey Vojtovich wrote: > ------------------------------------------------------------ > revno: 3807 > revision-id: svoj(a)mariadb.org-20130827121233-xh1uyhgfwbhedqyf > parent: jplindst(a)mariadb.org-20130823060357-pww92qxla7o8iir7 > committer: Sergey Vojtovich <svoj(a)mariadb.org> > branch nick: 10.0 > timestamp: Tue 2013-08-27 16:12:33 +0400 > message: > MDEV-4956 - Reduce usage of LOCK_open: TABLE_SHARE::tdc.used_tables > > - tc_acquire_table and tc_release_table do not access > TABLE_SHARE::tdc.used_tables anymore ok > - in tc_acquire_table(): release LOCK_tdc after we relase LOCK_open > (saves a few CPU cycles in critical section) This is a bit counter-intuitive. Your change is to hold the lock more than necessary. Does it give a measurable performance improvement? > - in tc_release_table(): if we reached table cache threshold, evict > to-be-released table without moving it to unused_tables. unused_tables > must be empty at this point (added assertion). Why unused_tables must be empty at this point? > === modified file 'sql/sql_base.cc' > --- a/sql/sql_base.cc 2013-08-14 08:48:50 +0000 > +++ b/sql/sql_base.cc 2013-08-27 12:12:33 +0000 > @@ -370,7 +372,7 @@ void free_io_cache(TABLE *table) > > void kill_delayed_threads_for_table(TABLE_SHARE *share) > { > - TABLE_SHARE::TABLE_list::Iterator it(share->tdc.used_tables); > + TABLE_SHARE::All_share_tables_list::Iterator it(share->tdc.all_tables); In this function, I'd suggest to add some flag to be able to skip the mutex and the loop altogether - as in the absolute majority of cases there will be no delayed insert thread to kill. Either use a global flag to know if there's a delayed thread running, or create a per-share flag. > TABLE *tab; > Regards, Sergei

2 4