- developers - lists.mariadb.org

[Maria-developers] WL#177 New (by Mdcallag): add server-side error logging
by worklog-noreply＠askmonty.org 22 Feb '11

22 Feb '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: add server-side error logging CREATION DATE..: Tue, 22 Feb 2011, 19:42 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 177 (http://askmonty.org/worklog/?tid=177) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: I want to log all or a sample of errors on the server side into a new log file (not general, error or slow query log) and into a file that can be rotated. In the facebook patch I added code to sample query errors into the slow query log using - -log-sample-error-rate: * when 0 do not sample * when 1 then log all errors * when > 1 then sample 1 of --log-sample-error-rate queries This should have: * query text * username * timestamp * database * errno * error text * client IP The log should support rotation and possibly auto-rotation when it reaches a certain size. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

Re: [Maria-developers] Fwd: [Commits] Rev 2901: BNLH algorithm always used a full table scan over the joined table in file:///home/igor/maria/maria-5.3-mwl128-hashrange/
by Sergey Petrunya 22 Feb '11

22 Feb '11

On Fri, Feb 11, 2011 at 08:49:37PM -0800, Igor Babaev wrote: > Hi Sergey, > > Please review this patch that fixes a defect of the current > implementation of the mwl #128 that never couples hash join > with range/index_merge scans. > The patch also adds prefix #hash# to the names of the used hash indexes. > (we agreed upon this at the last optimizer meeting). > > Regards, > Igor. > > -------- Original Message -------- > Subject: [Commits] Rev 2901: BNLH algorithm always used a full table > scan over the joined table in > file:///home/igor/maria/maria-5.3-mwl128-hashrange/ > Date: Fri, 11 Feb 2011 20:41:23 -0800 (PST) > From: Igor Babaev <igor(a)askmonty.org> > Reply-To: maria-developers(a)lists.launchpad.net > To: <commits(a)mariadb.org> > > At file:///home/igor/maria/maria-5.3-mwl128-hashrange/ > > ------------------------------------------------------------ > revno: 2901 > revision-id: igor(a)askmonty.org-20110212044122-w9n3jdk3d2ps0w3o > parent: igor(a)askmonty.org-20110207231903-3rqbfs50d33lk3r9 > committer: Igor Babaev <igor(a)askmonty.org> > branch nick: maria-5.3-mwl128-hashrange > timestamp: Fri 2011-02-11 20:41:22 -0800 > message: > BNLH algorithm always used a full table scan over the joined table > even in the cases when there existed range/index-merge scans that > were cheaper than the full table scan. > This was a defect/bug of the implementation of mwl #128. > Now hash join can work not only with full table scan of the joined > table, but also with full index scan, range and index-merge scans. > Accordingly, in the cases when hash join is used the column 'type' > in the EXPLAINs can contain now 'hash_ALL', 'hash_index', 'hash_range' > and 'hash_index_merge'. If hash join is coupled with a range/index_merge > scan then the columns 'key' and 'key_len' contain info not only on > the used hash index, but also on the indexes used for the scan. ... > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2011-02-06 04:57:03 +0000 > +++ b/sql/sql_select.cc 2011-02-12 04:41:22 +0000 ... > @@ -8419,6 +8438,11 @@ > sort_by_tab->type= JT_ALL; > sort_by_tab->read_first_record= join_init_read_record; > } > + else if (sort_by_tab->type == JT_HASH_NEXT) > + { > + sort_by_tab->type= JT_HASH; > + sort_by_tab->read_first_record= join_init_read_record; > + } > } > break; > } I have a question only to this part of the patch: I don't understand how the above code could work. The if statement above the one you've added checks if sort_by_tab was using [ordered] index scan to produce records in orderer, and if yes, switches it to a full scan (because use of join buffering will break ordering anyway). I suppose the part you've added does something similar, but I totally fail to understand what exactly it does. Now, some comments on the new EXPLAIN output: ISSUE1. Consider a query: MariaDB [j1]> explain select * from t3, t4 where t3.col1=t4.col2; +----+-------------+-------+----------+---------------+-----------+---------+------------+------+--------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+----------+---------------+-----------+---------+------------+------+--------------------------------------------------+ | 1 | SIMPLE | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where | | 1 | SIMPLE | t4 | hash_ALL | NULL | #hash#$hj | 5 | j1.t3.col1 | 1000 | Using where; Using join buffer (flat, BNLH join) | +----+-------------+-------+----------+---------------+-----------+---------+------------+------+--------------------------------------------------+ 2 rows in set (0.00 sec) AFAIU #hash#$hj means the hash key on the hashtable that's used for hash join. I think the name is too convoluted. The hashtable has only one key, if we need to tell it from t4's indexes it would be sufficient to use "#hashkey#", without the cryptic "$hj". As for "#" characters: EXPLAIN already shows "internal" tables/indexes: MariaDB [j1]> explain select * from (select * from t1) X; +----+-------------+------------+------+---------------+------+---------+------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+------+---------------+------+---------+------+------+-------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 1000 | | | 2 | DERIVED | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | | +----+-------------+------------+------+---------------+------+---------+------+------+-------+ # The following is from MWL#90 tree: MariaDB [test]> explain select * from t10 where a in (select max(b) from t10 group by a); +----+-------------+-------------+--------+---------------+--------------+---------+------------+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------+--------+---------------+--------------+---------+------------+------+---------------------------------+ | 1 | PRIMARY | t10 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where | | 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 5 | test.t10.a | 1 | | | 2 | SUBQUERY | t10 | ALL | NULL | NULL | NULL | NULL | 1000 | Using temporary; Using filesort | +----+-------------+-------------+--------+---------------+--------------+---------+------------+------+---------------------------------+ 3 rows in set (0.01 sec) # The following is from current 5.3: MariaDB [j1]> explain select distinct * from t1 where a in (select b from t2); +----+-------------+------------+--------+---------------+------------+---------+------+------+-----------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+--------+---------------+------------+---------+------+------+-----------------+ | 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 1000 | Using temporary | | 1 | PRIMARY | subselect2 | eq_ref | unique_key | unique_key | 5 | func | 1 | | | 2 | SUBQUERY | t2 | ALL | NULL | NULL | NULL | NULL | 1000 | Distinct | +----+-------------+------------+--------+---------------+------------+---------+------+------+-----------------+ ... and it either uses no quoting at all (see "unique_key"), or angle brackets quoting (see <subquery2>). I think we should not multiply the ways of quoting, so please consider switching to <quoting> or to not using any quoting at all. ISSUE2 MariaDB [j1]> EXPLAIN -> SELECT City.Name, Country.Name, CountryLanguage.Language -> FROM City,Country,CountryLanguage -> WHERE City.Country=Country.Code AND -> CountryLanguage.Country=Country.Code AND -> City.Name LIKE 'L%' AND Country.Population > 3000000 AND -> CountryLanguage.Percentage > 50 AND -> LENGTH(Language) < LENGTH(City.Name) - 2; +----+-------------+-----------------+----------+--------------------+---------------+---------+----------------------------+------+--------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-----------------+----------+--------------------+---------------+---------+----------------------------+------+--------------------------------------------------+ | 1 | SIMPLE | CountryLanguage | ALL | PRIMARY,Percentage | NULL | NULL | NULL | 984 | Using where | | 1 | SIMPLE | Country | hash_ALL | PRIMARY | #hash#PRIMARY | 3 | j1.CountryLanguage.Country | 239 | Using where; Using join buffer (flat, BNLH join) | | 1 | SIMPLE | City | hash_ALL | Country | #hash#Country | 3 | j1.CountryLanguage.Country | 4079 | Using where; Using join buffer (flat, BNLH join) | +----+-------------+-----------------+----------+--------------------+---------------+---------+----------------------------+------+--------------------------------------------------+ 3 rows in set (0.01 sec) "key" column shows "#hash#PRIMARY". I suppose, this is because hash join opimizer is internally hooked into ref optimizer, and it used the PRIMARY index. This might be useful for developer, but is this information meaningful for the user? If one changes #hash#PRIMARY to #hash#IDX2 (suppose there is an index called IDX2 which has the same definition as primary key), or to #hash#$hj, will it be a different query plan? I think it should be "<hashkey>" always. ISSUE3 For hash_range, "key" column shows {hashkey}:{range_key}, which allows to determine that which key was used for range access. For hash_index type, "key" column shows only the hash's pseudo-index: MariaDB [j3]> EXPLAIN SELECT t2.i FROM t1,t2 WHERE t1.cu = t2.cl; +----+-------------+-------+------------+---------------+-----------+---------+------+------+--------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+---------------+-----------+---------+------+------+--------------------------------------------------+ | 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 6 | | | 1 | SIMPLE | t1 | hash_index | cu1,cu2 | #hash#cu1 | 33 | func | 10 | Using where; Using join buffer (flat, BNLH join) | +----+-------------+-------+------------+---------------+-----------+---------+------+------+--------------------------------------------------+ What is the index that's used for scanning the table t1? "#hash#cu1" shows that hash join optimizer used access on index "cu1" in its analysis, but generally it doesn't mean that full index scan on table t1 was done on index cu1, doesn't it? I would very much prefer to see #hash#cu1:USED_INDEX there (or taking into account previous suggestions, hashkey:USED_INDEX). It will also be consistent with how hash_range and hash_index_merge use that column. ISSUE4 If key and key_len use hash_part:real_part notation, i.e. use semicolon, why use underscore in hash_ALL, hash_index, etc? Won't hash:ALL, hash:index look more consistent with other columns? BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 2

Re: [Maria-developers] [Commits] Rev 2833: Fixed LP bug #664508. in file:///home/igor/maria/maria-5.3-mwl128-bug664508/
by Sergey Petrunya 21 Feb '11

21 Feb '11

Hello Igor, On Sun, Oct 24, 2010 at 02:22:02PM -0700, Igor Babaev wrote: > At file:///home/igor/maria/maria-5.3-mwl128-bug664508/ > > ------------------------------------------------------------ > revno: 2833 > revision-id: igor(a)askmonty.org-20101024212201-t8iius4jikcvm3zb > parent: igor(a)askmonty.org-20101022223047-phunrh5g3xzrx2d9 > committer: Igor Babaev <igor(a)askmonty.org> > branch nick: maria-5.3-mwl128-bug664508 > timestamp: Sun 2010-10-24 14:22:01 -0700 > message: > Fixed LP bug #664508. > When join buffers are employed no index scan for the first > table with grouping columns can be used. ... > === modified file 'sql/sql_select.cc' > --- a/sql/sql_select.cc 2010-10-18 20:56:48 +0000 > +++ b/sql/sql_select.cc 2010-10-24 21:22:01 +0000 > @@ -7980,8 +7980,11 @@ > JOIN_TAB *tab=join->join_tab+i; > if (tab->use_join_cache) > { > - JOIN_TAB *sort_by_tab= join->get_sort_by_join_tab(); > - if (sort_by_tab && !join->need_tmp) > + JOIN_TAB *sort_by_tab= join->group && join->simple_group && > + join->group_list ? > + join->join_tab+join->const_tables : > + join->get_sort_by_join_tab(); > + if (sort_by_tab) > { > join->need_tmp= 1; > join->simple_order= join->simple_group= 0; Here sort_by_tab is assigned inside the " for (i=join->const_tables ; ..." loop. I think the code will look much more cleaner if it was calculated before the loop (and actually we don't need to loop at all if sort_by_tab==NULL). > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits -- BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] MariaDB Binary Log File Corruption
by Daniel Bartholomew 20 Feb '11

20 Feb '11

Hello everyone, I came across this question in the Knowledgebase: http://kb.askmonty.org/v/binary-log-file-corruption-happens-each-day The poster was able to solve the problem by increasing the slave max_allowed_packet size from 32M to 256M. Any thoughts from the list about this? My guess is that this not a MariaDB-specific thing; just a difference between 5.0 and 5.1 (and higher). But I'm asking anyway in case I'm mistaken and/or if a note should be added to the documentation for max_allowed_packet. Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

2 1

[Maria-developers] question about when locks are released
by MARK CALLAGHAN 18 Feb '11

18 Feb '11

Vamsi, In the innodb-release-locks-early patch, do you remember why you release the locks only _after_ the sync in prepare(), and not before? -- Mark Callaghan mdcallag(a)gmail.com

1 1

[Maria-developers] WL#176 New (by Dreas): protocl typo in replication error
by worklog-noreply＠askmonty.org 17 Feb '11

17 Feb '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: protocl typo in replication error CREATION DATE..: Thu, 17 Feb 2011, 16:58 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Maria-RawIdeaBin TASK ID........: 176 (http://askmonty.org/worklog/?tid=176) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 10 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Using 5.1.50-mariadb84: ===== Last_IO_Error: error connecting to master 'repl2@mysql1.*****.com:3306' - retry- time: 60 retries: 86400 message: SSL error: protocl version mismatch ==== "protocl" seems to be a typo. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] work in progress for the Facebook patch
by MARK CALLAGHAN 16 Feb '11

16 Feb '11

I am writing this to describe work in progress for the Facebook patch. Comments are welcome. In the future we hope to get parts of the Facebook patch into public forks. We have two big projects in progress: * admission control * InnoDB compression for OLTP --- Admission control enforces max concurrent queries per account. MySQL already limits max concurrent connections per account, but we want to set the connection limit to a much larger value than the query limit. The limit will be enforced at statement start and will allow queries to timeout in the queue while waiting to run. By "descheduled" I mean that a connection decrements the count of concurrent queries for a particular account. Connections will be descheduled when: * a statement ends * a connection is about to send results back to a client -- only when the socket write is to be done, not when data is buffered * srv_suspend_mysql_thread is called on a row lock wait * possibly when a disk read is done within innodb * when enter_cond is called By "scheduled" I mean that a connection increments the count of concurrent queries for an account. If done with "noforce" then the caller will wait until this doesn't exceed concurrency limits for the account. If done with "force" then this doesn't wait and can exceed the concurrency limits. Schedule with noforce is done: * at statement start * after a socket write Schedule with force is done: * when exit_cond is called * when a thread wakes after calling srv_suspend_mysql_thread This change will allow us to run with higher values for max concurrent connections and we assume that will significantly increase mutex contention on kernel_mutex so we have begun to evaluate: * using the pthread mutex implementation that does not spin in more cases * limit the max number of threads that can do concurrent busy-wait loops for InnoDB's mutex and rw-lock * removing use of the sync array within InnoDB * changing Innodb to wake one rather than all waiters when a mutex is unlocked * somehow making read_view_open_now faster --- InnoDB compression for OLTP tries to make compression faster for OLTP workloads. We have work in progress to change InnoDB to not log page images for many but not all insert/update/delete operations. When InnoDB compression is used, InnoDB can log page images. That was not done before and for my production workloads this doubles the rate at which IO is done to the transaction log and increases the dirty page write rate by at least 20% because checkpoint IO must be done sooner. We previously added an option that allows the LZ compression level to be set globally. I think the default is level 6. We will evaluate: * using QuickLZ which provides faster compression and decompression than zlib * adding compress_ops and compress_ops_ok per table to information_schema.table_statistics * adding an option to limit compression to the clustered index and not compress secondary indexes -- Mark Callaghan mdcallag(a)gmail.com

1 0

[Maria-developers] MariaDB and MySQL 5.5
by MARK CALLAGHAN 11 Feb '11

11 Feb '11

When will there be a MariaDB branch based on MySQL 5.5? I want to use that as the basis for the 5.5 version of the Facebook patch. -- Mark Callaghan mdcallag(a)gmail.com

2 1

[Maria-developers] WL#175 New (by Mdcallag): Unique ID in binlog event metadata
by worklog-noreply＠askmonty.org 11 Feb '11

11 Feb '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Unique ID in binlog event metadata CREATION DATE..: Fri, 11 Feb 2011, 05:03 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 175 (http://askmonty.org/worklog/?tid=175) VERSION........: Server-9.x STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: While I would love to have global transaction IDs for MySQL replication (http://askmonty.org/worklog/Server-RawIdeaBin/?tid=31) that is a huge project. I think we can automate slave failover if binlog events have a metadata attribute that is a unique ID and that metadata is sticky like server ID when a slave is run with --log-slave-updates. Assuming this metadata is added, when it is time to do slave failover, first determine the serverID + uniqueID for the last binlog event group executed on the slave, then find that filename/offset for that event on the new master, then CHANGE MASTER on the slave to start replication on the new master just past that offset. I mention that serverID can be used in addition to the uniqueID, so maybe uniqueID only needs to be unique within the binlog events generated for one serverID. The search on the new master is more efficient when the uniqueID are generated in increasing order. But I am not sure whether that is required. We might need a new server command that maps (serverID, uniqueID) to (filename, offset), but I am not sure that is required. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

Re: [Maria-developers] Windows installer MWL#55 finished.
by Vladislav Vaintroub 08 Feb '11

08 Feb '11

> -----Original Message----- > From: kahzoo(a)gmail.com [mailto:kahzoo@gmail.com] On Behalf Of Kazuhisa > Ichikawa > Sent: Dienstag, 8. Februar 2011 04:46 > To: Vladislav Vaintroub > Cc: Philip Stoev; maria-developers(a)lists.launchpad.net > Subject: Re: [Maria-developers] Windows installer MWL#55 finished. > > Hello, Vladislav. > All previous 5.2.5 installer created its data directory even when > upgrading existing instance. Therefore, if datadir was not set > explicity in the inherited my.ini, it was using the new data directory > (in other words, it was not reusing the previous database files, I'm > afraid). Yes. Somewhat unusual case my.ini without datadir was not handled correctly though (case without my.ini at all was handled). Fixed that, MSIs are in the usual location. Thanks for input! > - Kazuhisa

1 0