- developers - lists.mariadb.org

[Maria-developers] TCP CRCs - relevant for client/server including replication communications
by Arjen Lentz 05 Jul '11

05 Jul '11

Hi all See http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.27.7611 In a nutshell, you cannot expect TCP streams to be error free, instead expect a packet to go wrong every few days; that's quite significant, particularly when talking about replication streams. And most if not all of us in the real world have encountered this. It's a serious case for having CRCs in the binlog (yes I know it's put in from Google patches to later versions in 5.5+ but I really want it in MariaDB, urgently!), but also as a potential consideration for other communications, including client/server. The replication streams are the most critical though, that's where stuff has been seen to break in practice - and that's when your lucky and the fail results in a syntax error - if it changes data rather than keywords, it can remain invisible. (in such cases you could have a replication slave copying a transaction "perfectly" except it has wrong data...) Regards, Arjen. -- Arjen Lentz, Exec.Director @ Open Query (http://openquery.com) Remote expertise & maintenance for MySQL/MariaDB server environments. Follow us at http://openquery.com/blog/ & http://twitter.com/openquery

2 2

[Maria-developers] Derived table nests not removed by simplify_joins?
by Sergey Petrunya 04 Jul '11

04 Jul '11

Hello Igor, I've discovered the following: Consider a testcase for https://bugs.launchpad.net/maria/+bug/803365. There we have a query that's using a derived table that's on the inner side of an outer join: SELECT * FROM t1 WHERE t1.f1 IN ( SELECT t2.f2 FROM t2 LEFT JOIN ( SELECT * FROM t3 ) AS alias1 ON alias1.f3 = t2.f2 ); If I follow it in debugger to right after simplify_joins(), I can see this structure (the [] brackets denote List<TABLE_LIST> bounds): ['t1'---'(sj-nest)'] | ['alias1'---'t2' ] | [t3] 'alias1' is on the inner side of an outer join (it has non-NULL on_expr, and outer_join==1). It has got a single child, t3, which has on_expr==NULL and outer_join==0. Is it really correct that simplify_joins() didn't remove 'alias1', like it does with regular join nests? Could it be that simplify_joins() code can't handle single-child join nests just because the parser never produced them (but after conversion of derived tables to join nests they are now possible?) BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

[Maria-developers] Default optimizer_switch settings
by Sergey Petrunya 04 Jul '11

04 Jul '11

Hello, Following what has been decided on the optimizer call, I'm nearly done with making semi-join optimizations off by default in 5.3. However, some of 5.3's new optimizations are still turned on by default. Here is a list of 5.3 features and flags that control them: index_merge_sort_intersection=off semijoin=off firstmatch=off loosescan=off ^^ just done the above. materialization=off derived_merge=on derived_with_keys=on ^^ Are we sure about this? index_condition_pushdown=on ^^ This needs to be discussed too. partial_match_rowid_merge=on partial_match_table_scan=on ^^ These are still off because materialization is off. subquery_cache=on ^^ Need to discuss this. mrr=on mrr_cost_based=off mrr_sort_keys=on ^^ Need to discuss this. I take we should disable MRR, too (since BKA is disabled anyway?) join_cache_incremental=on join_cache_hashed=on join_cache_bka=on optimize_join_buffer_size=on ^^ These are not really on because default join_cache_level setting prevents any of these from being used. Another question is that we *MUST* have documentation in place if we're making a release with features that are disabled by default. BR Sergey -- Sergey Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

1 0

Re: [Maria-developers] [Commits] Rev 3077: Fixed LP bug #804686. in file:///home/igor/maria/maria-5.3-merge/
by Oleksandr Byelkin 04 Jul '11

04 Jul '11

Hi! 04.07.2011 00:59, Igor Babaev пишет: > At file:///home/igor/maria/maria-5.3-merge/ > > ------------------------------------------------------------ > revno: 3077 > revision-id: igor(a)askmonty.org-20110703215901-xmisn92l37tswlw8 > parent: monty(a)askmonty.org-20110702143759-sy79jwmahe66we7g > committer: Igor Babaev<igor(a)askmonty.org> > branch nick: maria-5.3-merge > timestamp: Sun 2011-07-03 14:59:01 -0700 > message: > Fixed LP bug #804686. > The assert conditions in the functions Item_direct_ref_to_ident::transform > and Item_direct_ref_to_ident::compile could be not valid after constant > propagation when fields and field references may be substituted for constants. > Not only these invalid asserts have been removed, but the functions containing > them have been removed as well because now Item_ref::transform and > Item_ref::compile can be used instead of them. The patch is OK.

1 0

[Maria-developers] WL#229 New (by Monty): Multiple InnoDB Buffer pools
by worklog-noreply＠askmonty.org 30 Jun '11

30 Jun '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Multiple InnoDB Buffer pools CREATION DATE..: Thu, 30 Jun 2011, 18:16 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 229 (http://askmonty.org/worklog/?tid=229) VERSION........: Server-5.7 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: Innodb multiple buffer pools, but size of each, and to which pool will a page go configured by the dba (instead of random/hash as in 5.5, at table level) ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#228 New (by Monty): Dynamic ENUM
by worklog-noreply＠askmonty.org 30 Jun '11

30 Jun '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Dynamic ENUM CREATION DATE..: Thu, 30 Jun 2011, 18:13 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 228 (http://askmonty.org/worklog/?tid=228) VERSION........: Server-5.7 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 200 (hours remain) ORIG. ESTIMATE.: 200 PROGRESS NOTES: DESCRIPTION: Create a ENUM-type like data type that does string compression (at a scale). For example, create a type LOOKUP(255) CHARSET utf8. We store browser user-agent strings here, you store bigint unsigned numbers internally, and create a lookup structure so that each occurrence of a string is physically stored only once. Convert from VARCHAR to LOOKUP using ALTER TABLE. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#227 New (by Monty): Add a time types that contains timezone
by worklog-noreply＠askmonty.org 30 Jun '11

30 Jun '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Add a time types that contains timezone CREATION DATE..: Thu, 30 Jun 2011, 18:11 SUPERVISOR.....: Monty IMPLEMENTOR....: Sergei COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 227 (http://askmonty.org/worklog/?tid=227) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 160 (hours remain) ORIG. ESTIMATE.: 160 PROGRESS NOTES: DESCRIPTION: Create DATE_TZ, TIME_TZ, DATETIME_TZ and TIMESTAMP_TZ types. Relegate the timezone in a session to the same function as the charset in a session ('this is what the client uses') and convert accordingly. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#226 New (by Monty): Replace the MYISAM mysql.* tables with Aria / XtraDB
by worklog-noreply＠askmonty.org 30 Jun '11

30 Jun '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Replace the MYISAM mysql.* tables with Aria / XtraDB CREATION DATE..: Thu, 30 Jun 2011, 18:06 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 226 (http://askmonty.org/worklog/?tid=226) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 120 (hours remain) ORIG. ESTIMATE.: 120 PROGRESS NOTES: DESCRIPTION: Replace the MYISAM mysql.* tables with Aria / XtraDB We need to get these files crash safe. There is some unknown (for me) reasons why it's hard to use XtraDB for these tables. Using crash safe Aria tables should however be possible. To use Aria, we should first fix that one can use aria_chk to change page size for Aria tables, as otherwise there will be problems with the system tables if one want's to change the aria page size. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#225 New (by Monty): Cache VIEW's
by worklog-noreply＠askmonty.org 30 Jun '11

30 Jun '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Cache VIEW's CREATION DATE..: Thu, 30 Jun 2011, 17:58 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 225 (http://askmonty.org/worklog/?tid=225) VERSION........: Server-5.7 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 40 (hours remain) ORIG. ESTIMATE.: 40 PROGRESS NOTES: DESCRIPTION: Views are read from file and parsed for every access. We should fix this in two stages: This task: Cache the VIEW file in memory (easy task) Later: Store a pre-parsed view in memory (hard task) ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#224 New (by Monty): Handle milions of users & roles.
by worklog-noreply＠askmonty.org 30 Jun '11

30 Jun '11

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Handle milions of users & roles. CREATION DATE..: Thu, 30 Jun 2011, 17:54 SUPERVISOR.....: Sergei IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-BackLog TASK ID........: 224 (http://askmonty.org/worklog/?tid=224) VERSION........: Server-5.7 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: The database should be able to work fine with one million users, one million roles. Do not keep all data in memory, load the required info on login into a cache (size changeable without restart). On login, load the requested data, if it is not in cache already. Example: SET GLOBAL USERNAME_CACHE = 1000000; SET GLOBAL ROLE_CACHE = 1000000; ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0