January 2012 - developers - lists.mariadb.org

[Maria-developers] 回复: Re: IO statistic in slow log
by jiangchengyao 05 Jan '12

05 Jan '12

Yes, Logical IO means pages from buffer pool, physical IO means pages read from disk 2012-01-05 jiangchengyao 发件人：MARK CALLAGHAN 发送时间：2012-01-05 04:39 主题：Re: [Maria-developers] IO statistic in slow log 收件人：jiangchengyao 抄送：maria-developers What are the difference between logical and physical IO? I assume you are using Oracle's terminology where a logical IO means that a page was read from the InnoDB buffer cache. On Thu, Dec 29, 2011 at 6:41 PM, jiangchengyao <jiangchengyao(a)gmail.com> wrote: > Hi, all > > I write a patch to show physical & logical IO statistics in slow log. Now it > only support XtraDB storage engine, but it is easy to realize in other > engine. You may found it like this in slow log: > > # Time: 111227 16:29:54 > # User@Host: root[root] @ localhost [::1] > # Query_time: 0.310018 Lock_time: 0.203012 Rows_sent: 1 Rows_examined: > 30000 Logical_IO: 30145 Physical_IO: 50 > use tpcc; > SET timestamp=1324974594; > select count(1) from history; > > I think this feature is much helpful for DBA or Developer to know what the > real IO one SQL has. In addition, it is simple, only a few changes should be > made. > > The branch is: https://code.launchpad.net/~jiangchengyao/+junk/mariadb > > Regards, > David > > 2011-12-30 > ________________________________ > jiangchengyao > > _______________________________________________ > Mailing list: https://launchpad.net/~maria-developers > Post to : maria-developers(a)lists.launchpad.net > Unsubscribe : https://launchpad.net/~maria-developers > More help : https://help.launchpad.net/ListHelp > -- Mark Callaghan mdcallag(a)gmail.com

1 0

Re: [Maria-developers] request for a feature -- LIMIT_ROWS_EXAMINED
by MARK CALLAGHAN 04 Jan '12

04 Jan '12

Start with it in the current development branch. If I am not able to backport that then we can talk again. On Tue, Jan 3, 2012 at 1:33 AM, Timour Katchaounov <timour(a)montyprogram.com> wrote: > Mark, > > There is one thing we didn't specify - in which MariaDB version > do you want the feature? If you want just a patch, then it could > be in any version, but if you'd like it to be in a released version, > then it should be in 5.5. > > If you want it to be both in an older version, and in 5.5, then > there will be slight differences IMO because in 5.3 there are few > new join algorithms, and the related code has changed. > > Timour > > > On 23.12.2011 06:38, MARK CALLAGHAN wrote: >> >> Yes, I don't think it is a lot of work. I started on it myself but >> don't trust myself to modify optimizer code. I think we can find a >> bigger project to collaborate on in 2012 -- probably something related >> to replication. >> >> On Wed, Dec 21, 2011 at 12:57 PM, Michael Widenius<monty(a)askmonty.org> >> wrote: >>> >>> >>> Hi! >>> >>>>>>>> "MARK" == MARK CALLAGHAN<mdcallag(a)gmail.com> writes: >>> >>> >>> MARK> A warning would be excellent. An error would be a problem. >>> Returning >>> MARK> OK is probably not the best result when the examined limit is >>> reached. >>> >>> Ok, we will try to develop this feature (with a ok + warning) this year. >>> (Should not be that many hours of work). >>> >>> Regards, >>> Monty >> >> >> >> > -- Mark Callaghan mdcallag(a)gmail.com

1 0

[Maria-developers] WL#248 New (by Igor): Collect engine independent statistics on table columns and indexes to store it in persistent system tables
by worklog-noreply＠askmonty.org 04 Jan '12

04 Jan '12

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Collect engine independent statistics on table columns and indexes to store it in persistent system tables CREATION DATE..: Wed, 04 Jan 2012, 04:11 SUPERVISOR.....: IMPLEMENTOR....: Igor COPIES TO......: Igor Knielsen Monty Psergey Sanja Sergei Timour Wlad CATEGORY.......: Server-BackLog TASK ID........: 248 (http://askmonty.org/worklog/?tid=248) VERSION........: Server-5.5 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 120 (hours remain) ORIG. ESTIMATE.: 120 PROGRESS NOTES: DESCRIPTION: In a special mode the ANALYZE command will collect statistics on table cardinality, table columns and table indexes. This statistics will be stored in system tables table_stat, column_stat and index_stat. For any database table the system table table_stat will contain the number of rows in the table. For any table column the system table column_stat will contain min and max value, the share of null values, the average length of a column value, and the the average frequency of the column values. For any index defined on a database table the system table index_stat will contain number of records per every key prefix. This task will be implemented in 3 steps: 1. A pure back-port of the WL#4777 from mysql-5.4 code line will be performed. 2. New possibility to collect statistics only on specified columns and/or indexes will be added. 3. A new possibility to scan only a certain percentage of the table records in a pseudo-random manner will be added. The collected statistics is supposed to be used by the optimizer, but this will be an objective of some other task. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

Re: [Maria-developers] [Commits] Rev 3376: Fixed LP bug #910083. in file:///home/igor/maria/maria-5.3-bug910083/
by Igor Babaev 03 Jan '12

03 Jan '12

On 01/03/2012 06:44 AM, Timour Katchaounov wrote: > Igor, > >> On 01/03/2012 12:49 AM, Timour Katchaounov wrote: >>> Hi Igor, >>> >>> The reason for removing this specific call to >>> engine->set_thd() was because Monty claimed this is >>> the correct way, and because with the new assert we >>> couldn't find any case where the new assert would fail. >>> >>> Apparently the above assert was incorrect, however, the >>> current commit message doesn't have any explanation what >>> was wrong, why the assert you removed is wrong, and why >>> it is needed to call engine->set_thd(). >> >> Igor, >> >> I would rather say that you patch did not have any explanation >> why you had removed the call. Should it be a reference to Monty's >> opinion?I have to admit it would be pretty pretty lame. > > There is a comment in the patch for bug lp:685411, this is the > relevant diff: > > @@ -183,7 +185,8 @@ bool Item_subselect::fix_fields(THD *thd > bool res; > > DBUG_ASSERT(fixed == 0); > - engine->set_thd((thd= thd_param)); > + /* There is no reason to get a different THD. */ > + DBUG_ASSERT(thd == thd_param); > if (!done_first_fix_fields) > { > done_first_fix_fields= TRUE; > > The reasoning is simple - there is no reason for a subquery to > get a different THD object compared to that of the containing > query. > > So my question to your patch is whether you figured out why the > above assumption is not true. If you did, it would be nice to have > an explanation. I am pretty sure the assumption is true for SELECTs. > If that is the case, then I would refine your fix by testing whether > a statement is a SELECT, or UPDATE (or INSERT?) and updating the THD > only when needed. It's relevant for the scenario when the fixed item is created by a different client: for example, when the item belongs to SP and this SP was created by a different client. I returned the code that you removed. I don't think I have to explain why you were wrong. > > Timour > >> >> Regards, >> Igor. >> >>> >>> Timour >>> >>> On 3.01.2012 06:06, Igor Babaev wrote: >>>> At file:///home/igor/maria/maria-5.3-bug910083/ >>>> >>>> ------------------------------------------------------------ >>>> revno: 3376 >>>> revision-id: igor(a)askmonty.org-20120103040636-nc6o55vsxqadd1n0 >>>> parent: psergey(a)askmonty.org-20111230211905-he458ysn3sse6wlm >>>> committer: Igor Babaev<igor(a)askmonty.org> >>>> branch nick: maria-5.3-bug910083 >>>> timestamp: Mon 2012-01-02 20:06:36 -0800 >>>> message: >>>> Fixed LP bug #910083. >>>> The patch for bug 685411 erroneously removed a call of >>>> engine->set_thd() >>>> from Item_subselect::fix_fields(). >>>> >>>> >>>> >>>> _______________________________________________ >>>> commits mailing list >>>> commits(a)mariadb.org >>>> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits >>

1 0

Re: [Maria-developers] [Commits] Rev 3376: Fixed LP bug #910083. in file:///home/igor/maria/maria-5.3-bug910083/
by Igor Babaev 03 Jan '12

03 Jan '12

On 01/03/2012 12:49 AM, Timour Katchaounov wrote: > Hi Igor, > > The reason for removing this specific call to > engine->set_thd() was because Monty claimed this is > the correct way, and because with the new assert we > couldn't find any case where the new assert would fail. > > Apparently the above assert was incorrect, however, the > current commit message doesn't have any explanation what > was wrong, why the assert you removed is wrong, and why > it is needed to call engine->set_thd(). Igor, I would rather say that you patch did not have any explanation why you had removed the call. Should it be a reference to Monty's opinion?I have to admit it would be pretty pretty lame. Regards, Igor. > > Timour > > On 3.01.2012 06:06, Igor Babaev wrote: >> At file:///home/igor/maria/maria-5.3-bug910083/ >> >> ------------------------------------------------------------ >> revno: 3376 >> revision-id: igor(a)askmonty.org-20120103040636-nc6o55vsxqadd1n0 >> parent: psergey(a)askmonty.org-20111230211905-he458ysn3sse6wlm >> committer: Igor Babaev<igor(a)askmonty.org> >> branch nick: maria-5.3-bug910083 >> timestamp: Mon 2012-01-02 20:06:36 -0800 >> message: >> Fixed LP bug #910083. >> The patch for bug 685411 erroneously removed a call of >> engine->set_thd() >> from Item_subselect::fix_fields(). >> >> >> >> _______________________________________________ >> commits mailing list >> commits(a)mariadb.org >> https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits

1 0

Re: [Maria-developers] [Commits] Rev 3202: Fix compile error in file:///H:/bzr/5.5/
by Sergei Golubchik 03 Jan '12

03 Jan '12

Hi, Vladislav! What was the error that you fixed? On Jan 03, Vladislav Vaintroub wrote: > ------------------------------------------------------------ > revno: 3202 > committer: Vladislav Vaintroub <wlad(a)montyprogram.com> > branch nick: 5.5 > timestamp: Tue 2012-01-03 00:17:36 +0100 > message: > Fix compile error > === modified file 'storage/maria/ma_write.c' > --- a/storage/maria/ma_write.c 2012-01-02 20:56:16 +0000 > +++ b/storage/maria/ma_write.c 2012-01-02 23:17:36 +0000 > @@ -472,10 +472,11 @@ > int error; > my_off_t new_root= *root; > uchar key_buff[MARIA_MAX_KEY_BUFF]; > - MARIA_KEY UNINIT_VAR(org_key); /* Set/used when now_transactional=TRUE */ > + MARIA_KEY org_key; /* Set/used when now_transactional=TRUE */ > + my_bool transactional= share->now_transactional; > DBUG_ENTER("_ma_ck_write_btree_with_log"); > > - if (share->now_transactional) > + if (transactional) > { > /* Save original value as the key may change */ > org_key= *key; > @@ -483,7 +484,7 @@ > } > > error= _ma_ck_real_write_btree(info, key, &new_root, comp_flag); > - if (!error && share->now_transactional) > + if (!error && transactional) > { > /* Log the original value */ > *key= org_key; > > _______________________________________________ > commits mailing list > commits(a)mariadb.org > https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits Regards, Sergei

2 1