- developers - lists.mariadb.org

[Maria-developers] Phone home
by Sergei Golubchik 19 Sep '10

19 Sep '10

Hi. So, "Phone Home" or "MySQL feedback daemon" or "better name wanted" feature. It is something that can be installed together with MariaDB, it will gather different statistic about how MariaDB is used and will send this information anonymously to mariadb.org. Not unlike the Uptimes Project or Debian Popularity Contest. The complete specs will be here: http://askmonty.org/worklog/Server-Sprint/?tid=12 There are basically four questions I'm thinking on. 1. Should that be a MariaDB plugin or a separate executable ? I tend to prefer a separate executable. There is no need to keep it in memory constantly - cron job can do. Being separate its bugs won't affect the server. Being separate one instance can monitor many MariaDB servers. It can be upgraded separately - and it's not tied to the server release schedule. The drawback - it won't be able to grab MariaDB internals easily, which means it may not report some data that are worth reporting. But to solve this we can add an I_S table that provides this information. This way there's no "hidden" data to report, everything is available from the SQL. Which is good :) 2. How to send the data. We'll use HTTP. Seems to be the most universally working transport. That's what other projects are using too - Uptimes Project uses UDP or HTTP, Debian Popularity Contest - SMTP or HTTP. We *may* want to add SMTP later, if needed. 3. Auditing. How can we prove to paranoid users that we only send what we are saying we send, and none of potentially private information. Possible solution: http sending should support a proxy (to work behind firewalls), so one can install a logging proxy and record all the data sent. On the other hand, we'd like to use SSL too. We can support, besides direct http, a "wget mode" where the data are sent by invoking wget (which supports proxies, SSL and --post-file) and one could easily replace wget with a simple script that logs all the data. 4. What to report. That's the most interesting part :) note that not everything from below is collected in MariaDB now, but I describe the ideal case, what would be useful to know to steer MariaDB development in the right direction. The principle I used was not "let's grab as much as we can" but "on a need-to-know basis". For example, we may need to decide whether to optimize huge IN (...) lists or GIS first. Knowing what is used more often would help to make a correct decision. hardware: CPU, RAM OS (linux distribution, kernel) mariadb version, memory usage parts of config (e.g. buffer sizes) list of installed plugins number of databases, max/avg number of tables in a database, max/avg db/table size uptime something that indicates the load, e.g. average qps how much a particular feature is used: Com_ counters from SHOW STATUS plugin usage counters per feature, like GIS, replication, etc. per query parts, like ORDER BY, subquery in the FROM, IN subquery ... how useful is query cache (hit ratio?) What else ? Regards, Sergei

2 5

Re: [Maria-developers] Seeking command syntax feedback (Still Re: Per-db binlogging)
by Weldon Whipple 16 Sep '10

16 Sep '10

On Wed, Sep 15, 2010 at 2:00 PM, Sergei Golubchik <serg(a)askmonty.org> wrote: > Hi, Weldon! Hi, Sergei! > > On Sep 15, Weldon Whipple wrote: <snip/> >> 2. FLUSH TABLES WITH READ LOCK FOR DATABASE dbname; >> >> (I just added an optional 'FOR DATABASE dbname' to the already >> existing command--which still works, BTW. Is this a misuse of the word >> "FOR"?) >> This is the command that locks the tables before someone (some >> program) dumps/copies the tables that will be moved to another server. >> It flushes and locks only one database. > > This would be difficult to implement. > > FLUSH TABLES WITH READ LOCK is not implemented in a way that can be > generalized to support per-database locks :( I had guessed as much. (The method in my DB_BINLOG_MGR method that implements that functionality consists of a mutex lock, a comment about some sort of miracle happening, then a mutex unlock.) I wonder if there might be a way of inhibiting some user database (temporarily) from changing ... Alternately, I suppose we could do a global FLUSH TABLE WITH READ LOCK/ UNLOCK TABLES. Or ... (???) > Besides, I personally wouldn't worry much about the syntax. It's > something you can easily change anytime - e.g. replace BINLOG with > BINLOGGING or FOR with something else. Yeah, every time I write a new prototype I tweak the syntax a bit. At the moment I'm just trying to settle on an implementation that would work for our company (and still be of some sort of general use). > > Regards, > Sergei > Thanks, Weldon

2 1

Re: [Maria-developers] [Maria-discuss] Per-db binlogging
by Henrik Ingo 15 Sep '10

15 Sep '10

On Tue, Sep 14, 2010 at 3:02 AM, MARK CALLAGHAN <mdcallag(a)gmail.com> wrote: > +maria-discuss as they are working in replication too To be exact, maria-developers@ is the counterpart to internals@mysql, although everyone should be reading maria-discuss@ too. I'm now leaving both in CC. > On Mon, Sep 13, 2010 at 1:52 PM, Weldon Whipple <weldon(a)whipple.org> wrote: >> Greetings! >> >> My employer has asked me to implement per-DB binlogging for >> MySQL. I've been working on prototypes for 3-4 weeks now, and decided >> it's time to post to this list to ask for comments, suggestions, >> condolences, etc. (I've been a "lurker" for quite awhile. I hope this >> post isn't too far off the mark) The assignment is *only* for master-side >> binlogging. (It doesn't require a slave/replication-side implementation.) >> >> >> Why I Want to Do It. >> ------------------- >> >> I work for an ISP that hosts (last I heard--I HOPE I'm not lying) over >> 2 million domains. Customers are allowed to have (almost) unlimited >> MySQL databases. Migrating an account from one physical server to >> another is a daunting task. (A typical server has easily 2000+ >> [sometimes far more] databases.) Global mysqld binlogging isn't >> feasible when we want to migrate (for example) a single account to >> another server. >> >> Our proposed scenario goes something like this: <cut> Do I understand correctly, that in the normal case you do not run with binlog on at all? You would only turn on this kind of per-database binlog when needed? If that would be the case, then I don't see why --binlog-do-db wouldn't already do exactly what you want, except that you'd require a restart to change it. So all you need to implement would be the ability to set it as a system variable and not just in the conf file. (http://dev.mysql.com/doc/refman/5.1/en/replication-options-binary-log.html#…) Alternatively, if you are also running a "global" binlog, then as Mark suggested, don't write additional per database binlogs, rather get your information from the "global" binlog and filter out what should be replicated instead by using --replicate-do-db. Again, it is not currently possible to set this on/off without restart. (http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#optio…) Finally, for anyone wishing to implement their own binlogging and replication, I should advertise our work in MariaDB on a generic "replication plugin API". Using this you could implement your own replication plugin that would do precisely what you want, but you could still leave the original replication code untouched. (You could of course "fork" it to make your own plugin if you wanted.) Kristian Nielsen in CC is working on that, and you can read about it here: http://askmonty.org/wiki/ReplicationProject http://askmonty.org/worklog/?tid=107 http://askmonty.org/worklog/?tid=120 henrik PS: It is not exactly the same use case, but also see mine and Igor's presentation from last years MySQL user conference. http://assets.en.oreilly.com/1/event/36/Valuable%20MariaDB%20Features%20Tha… ...from slide 10 onwards. Summary: You can use mysqlbinlog instead of replication and this allows all kinds of scriptable hacks to occur between master and slave. henrik -- henrik.ingo(a)avoinelama.fi +358-40-5697354 www.openlife.cc

2 1

[Maria-developers] Why delete stuff from MWL#39???
by Henrik Ingo 15 Sep '10

15 Sep '10

Regarding http://askmonty.org/worklog/Client-RawIdeaBin/?tid=39 ...user "Guest" in June has deleted all the dependencies on a group of mysqlbinlog tasks. Why? MWL#39 existed to act as an entry point to some interrelated mysqlbinlog tasks. Now there is no simple way for me to look the up all at once. *grumble* henrik -- henrik.ingo(a)avoinelama.fi +358-40-5697354 www.openlife.cc

1 0

[Maria-developers] ./configure report
by Sergei Golubchik 15 Sep '10

15 Sep '10

Hi. For a while, I had a small personal gripe that it is not easy to see what plugins are configured to be built and how. That is, I know where to look (configure prints all that when working, but not alphabetically, so a particular plugin name isn't easy to spot) but I had to explain this to other often enough to realize that it's not at all obvious. The configure script prints at the end the small configuration report. I've extended it from --< cut >------------------------------------ config.status: executing my_config.h commands You can find information about MariaDB at http://askmonty.org/wiki/index.php/MariaDB Remember to check the platform specific part of the reference manual for hints about installing MariaDB on your platform. Also have a look at the files in the Docs directory. --- Configuration summary for MariaDB Server version 5.1.50-MariaDB * Installation prefix: /usr/local * System type: unknown-linux-gnu * Host CPU: x86_64 * C Compiler: gcc (Gentoo 4.4.3-r2 p1.2) 4.4.3 * C++ Compiler: g++ (Gentoo 4.4.3-r2 p1.2) 4.4.3 * Debug enabled: no * Community Features: yes --- Thank you for choosing MariaDB! --< cut >------------------------------------ to include all plugins in alphabetical order (and moved informational text down to improve its visibility, as the plugin list is long): --< cut >------------------------------------ config.status: executing my_config.h commands --- Configuration summary for MariaDB Server version 5.2.2-MariaDB-gamma * Archive Storage Engine: plugin * Aria Storage Engine: yes * Blackhole Storage Engine: plugin * CSV Storage Engine: yes * Cluster Storage Engine: no * Collection of Authentication Plugins: plugin * Daemon Example Plugin: plugin * Example Storage Engine: plugin * Federated Storage Engine: plugin * FederatedX Storage Engine: plugin * Graph Storage Engine: plugin * IBM DB2 for i Storage Engine: no * InnoDB Storage Engine: plugin * Memory Storage Engine: yes * MyISAM MERGE Engine: yes * MyISAM Storage Engine: yes * PBXT Storage Engine: no * Partition Support: no * Simple Parser: plugin * Sphinx Storage Engine: plugin * XtraDB Storage Engine: plugin * Installation prefix: /usr/local * System type: unknown-linux-gnu * Host CPU: x86_64 * C Compiler: gcc (Gentoo 4.4.3-r2 p1.2) 4.4.3 * C++ Compiler: g++ (Gentoo 4.4.3-r2 p1.2) 4.4.3 * Debug enabled: no * Community Features: yes --- You can find information about MariaDB at http://askmonty.org/wiki/index.php/MariaDB Remember to check the platform specific part of the reference manual for hints about installing MariaDB on your platform. Also have a look at the files in the Docs directory. Thank you for choosing MariaDB! --< cut >------------------------------------ So, what do you think? Too long to be useful? Or shall I push ? Need to reorder sections? (yes, it's a bikeshed color question, I know) Regards, Sergei

2 1

[Maria-developers] really annoying bug in mysql 5.0/5.1 optimiser, opened for more than 2 years, and still present in MariaDB
by Jocelyn Fournier 15 Sep '10

15 Sep '10

Hi, There's a really annoying bug in the mysql optimiser existing for a really long time now, and which has a direct impact on performances : http://bugs.mysql.com/bug.php?id=36817 Since MySQL definitly doesn't seem to want to fix it, I hope MariaDB could do something to help the poor developpers who need to heavily hack there apps with USE INDEX ;) Note I've tested with mariadb 5.2.1-beta, and the bug is still present. Thanks a lot ! Jocelyn Fournier

2 3

[Maria-developers] WL#148 New (by Sanja): Move the transformations for inequality predicates with ALL/ANY subqueries from the prepare phase into the optimization phase.
by worklog-noreply＠askmonty.org 15 Sep '10

15 Sep '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Move the transformations for inequality predicates with ALL/ANY subqueries from the prepare phase into the optimization phase. CREATION DATE..: Wed, 15 Sep 2010, 07:25 SUPERVISOR.....: Igor IMPLEMENTOR....: Sanja COPIES TO......: Monty CATEGORY.......: Server-Sprint TASK ID........: 148 (http://askmonty.org/worklog/?tid=148) VERSION........: Benchmarks-3.0 STATUS.........: Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 64 (hours remain) ORIG. ESTIMATE.: 64 PROGRESS NOTES: DESCRIPTION: Move the transformations for inequality predicates with ALL/ANY subqueries from the prepare phase into the optimization phase from preparation phase. It could be done in any place of the optimisation. Now there is 2 kind of the MAX/MIN optimisation for non-correlated subqueries: If it is possible (there is no aggregate functions in the subquery) ALL/ANY converted to <LEFT> <comp> (SELECT <min/max>(<select_list>) ...) Otherwise: <LEFT> <comp> (MAX/MIN_SELECT <select_list> ...) Where MAX/MIN_SELECT is special subquery which returns max or min result of all found by the subquery. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] WL#147 New (by Dreas): Support native IPv6
by worklog-noreply＠askmonty.org 13 Sep '10

13 Sep '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: Support native IPv6 CREATION DATE..: Mon, 13 Sep 2010, 11:10 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 147 (http://askmonty.org/worklog/?tid=147) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 60 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: IPv6 is quickly gaining popularity. Unfortunately MySQL 5.0/5.1 do not support IPv6. It appears that for MySQL 6 they work on IPv6 support (http://forge.mysql.com/worklog/task.php?id=798) however since that release will still be far away it may be good to backport the IPv6 changes to MariaDB 5.1 already. It would definitely add another clear advantage of MariaDB over MySQL (5.1). ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0

[Maria-developers] MariaDB 5.1.50
by Daniel Bartholomew 09 Sep '10

09 Sep '10

This release should be uploaded to the OSUOSL mirror within a few hours. I'll check when I get up and if things look good I'll activate the download, release notes, and changelog pages. One thing that hasn't been done (that I can see) is for a "mariadb-5.1.50" tag to be added to the 5.1 tree on launchpad as per the release process ( http://kb.askmonty.org/v/release-process ). Monty: can you or one of the other devs add the tag? Looking at the release process just now I see several steps that need to be updated. I'll do so after this release is out. One last question: What are the highlights of this release (other than the merge with MySQL 5.1.50)? Thanks. -- Daniel Bartholomew Monty Program - http://askmonty.org

2 1

[Maria-developers] WL#145 New (by Sergei): user defined data types
by worklog-noreply＠askmonty.org 09 Sep '10

09 Sep '10

----------------------------------------------------------------------- WORKLOG TASK -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- TASK...........: user defined data types CREATION DATE..: Thu, 09 Sep 2010, 08:46 SUPERVISOR.....: IMPLEMENTOR....: COPIES TO......: CATEGORY.......: Server-RawIdeaBin TASK ID........: 145 (https://askmonty.org/worklog/?tid=145) VERSION........: WorkLog-4.0 STATUS.........: Un-Assigned PRIORITY.......: 40 WORKED HOURS...: 0 ESTIMATE.......: 0 (hours remain) ORIG. ESTIMATE.: 0 PROGRESS NOTES: DESCRIPTION: soften we get requests for new data types like timestamps with microsecond precision or ipv4/ipv6. This could be solved with user defined types - implemented via plugins or special SQL syntax, whatever is appropriate. ESTIMATED WORK TIME ESTIMATED COMPLETION DATE ----------------------------------------------------------------------- WorkLog (v4.0.0)

1 0