March 2014 - developers - lists.mariadb.org

[Maria-developers] [GSOC 2014] CREATE OR REPLACE, CREATE IF NOT EXISTS, and DROP IF EXISTS
by Kesha Shah 03 Apr '14

03 Apr '14

Dear Sergei Golubchik, Hope this mail finds you well. I am Kesha Shah, a third year undergraduate from DA-IICT, Gandhinagar, India. I was a Google Summer of Code student previous year as well as Google Code-In mentor with BRL-CAD open source solid modeling software. The activities related to my contributions at BRL-CAD at logged by me at - http://brlcad.org/wiki/User:KeshaSShah/GSoC13/Reports. My other projects can be found at - https://github.com/keshashah?tab=repositories. I have good programming hands on C and C++ and would like to take up and contribute at the Mariadb task of 'CREATE OR REPLACE, CREATE IF NOT EXISTS, and DROP IF EXISTS' [MDEV-5359] as part of GSOC 2014. I have studied database systems and compiler construction during my academics. Your project would help to apply my knowledge by working at deeper level of coding database internals, thereby contributing to open source software. I had checked out the mariadb master branch from bazaar and started looking into the source code. I found the task more related to update of the existing mariadb SQL parser. The parser seems to be implemented as ascendant parser using bison parser generator (GNU version of YACC). From the current findings I could see the YACC grammar file sql_yacc.yy under maria/sql/ where I can see the rules defined for CREATE PROCEDURE_SYM, CREATE FUNCTION_SYM, CREATE TRIGGER_SYM etc. Alternatively I can see the *_SYM lexical tokens defined under the /maria/sql/lex.h file. Similarly for DROP TABLESPACE_SYM, DROP TRIGGER_SYM etc could be found. So when sql text input stream is encountered from SQL interface, it is read and categorized into lexical tokens by the lexer /maria/sql/sql_lex.cc in the lexical analysis step. I tried to figure out that we will have to define additional token 'IF' in the lex.h and 'NOT' and 'EXISTS' are already defined. I am also looking at the existing user defined actions that builds the AST nodes (sp_head being the root node for most types) when relevant grammar rule is satisfied in case of DDL statements. After semantic analysis (create of stored procs/functions), the next step would be to amend/define actions for the newly introduced grammar of handling check of existence of db artefact at the time of DDL execution. Also at this step the compiler will have to query the relevant storage engine on DB level or repository level to check if the DB object exists. This would require me to spend more time on understanding design pattern followed for the implementation of classes and behaviours of each of the underlying db artefacts. It would be interesting for me to know if the steps proposed by me above are in the right direction or not ? Also I am aware that I am late in discussing my proposal with you. But I can assure that I will be able to deliver the requirements in timely manner under your guidance. I have formulated initial draft of my proposal and would like to submit it at the earliest. Looking forward to you reply. Have a nice day! Thanks and Regards, Kesha Shah

2 2

[Maria-developers] DBT-3 benchmarks: MariaDB 10 vs MySQL 5.6
by Colin Charles 31 Mar '14

31 Mar '14

Hi XL, Nice work on posting new benchmarks in replication. Are you planning on running DBT-3 benchmarks with 10.0.10 and latest 5.6? Maybe even 5.7 DMR? Thanks -- Colin Charles, Chief Evangelist, SkySQL - The MariaDB Company blog: http://bytebot.net/blog/| t: +6-012-204-3201 | Skype: colincharles

1 0

[Maria-developers] maria-developers@lists.launchpad.net
by Sergey Vojtovich 31 Mar '14

31 Mar '14

Hi! This is second list of questions I came up while merging test cases: - --ignore-builtin-innodb is deprecated in 5.6, do we want to deprecate it too? - LOW_PRIORITY WRITE is deprecate in 5.6, do we want to deprecate it too? The following items seem to be not merged to MariaDB. Do we want to merge them? WL#5331 Support Unicode for Windows command line client Bug#12688860 : SECURITY RECOMMENDATION: PASSWORDS ON CLI WL#5855 (EXPLAIN FORMAT=JSON) partition test WL#5980 - Allow remote tablespaces (need to check) WL5602 Providing password hashing with appropriate strength Bug#12316645: Wrong cost calculation with optimizer_join_cache_level settings WL#5602 - SHA256 password authentication Bugs#12635232: VALGRIND WARNINGS: IS_IPV6, IS_IPV4, INET6_ATON, INET6_NTOA + MULTIBYTE CHARSET Bug#14040277 UNINITIALIZED VALUE REFERENCED IN STR_TO_IPV6 And a lot of other IPV6 related stuff. Regards, Sergey

2 2

[Maria-developers] Intermediate status for test cases merge
by Sergey Vojtovich 31 Mar '14

31 Mar '14

Hi! So far I processed 431 test cases from the main suite. Most of them are up to date, a few merged without problems, a few tests fail (bugs reported), a few need extra analysis. There are a few items which I'd like to discuss: - INSERT DELAYED is deprecated in 5.6, do we want to deprecate it too? - SHOW PROFILE is deprecated in 5.6, do we want to deprecate it too? - YEAR(2) is deprecated in 5.6, do we want to deprecate it too? Monty suggests that we shouldn't deprecate it. I find it Ok too. Reasons for YEAR(2) deprecation are not obvious, relevant worklog is private. Relevant revision comment says: "YEAR(2) is a subject to deprecation since it has ill design." - EXPLAIN gives NULL in Extra column in 5.6 (empty string in 10.0), do we want behave similarly? - Do we want to merge fix for BUG#60269 - mysql should reject attempts to create system tables in incorrect engine http://bugs.mysql.com/bug.php?id=60269 - NO_ENGINE_SUBSTITUTION is default mode in 5.6 now, do we want it be default too? - 5.6 has many updates to default values, do we like to merge them too? Thanks, Sergey

2 6

Re: [Maria-developers] [Commits] Rev 3995: MDEV-5138 Numerous test failures in "mtr --ps --embedded"
by Sergei Golubchik 31 Mar '14

31 Mar '14

Hi, Holyfoot! On Dec 09, holyfoot(a)askmonty.org wrote: > At file:///home/hf/wmar/mdev-5138/ > > ------------------------------------------------------------ > revno: 3995 > revision-id: holyfoot(a)askmonty.org-20131209020845-9o3l30ao90vzf600 > parent: holyfoot(a)askmonty.org-20131208143921-3veatgg9wngyzb47 > committer: Alexey Botchkov <holyfoot(a)askmonty.org> > branch nick: mdev-5138 > timestamp: Mon 2013-12-09 06:08:45 +0400 > message: > MDEV-5138 Numerous test failures in "mtr --ps --embedded". > If a prepared statement calls an stored procedure, > the thd->server_status out of the SP goes up > to the PS and then to the client. So that the > client gets the SERVER_STATUS_CURSOR_EXISTS status > if the SP uses a cursor. Which makes the embedded > server fail. Why does embedded fail while a normal client-server protocol is ok with that? > Fixed by saving/restoring the upper-level server_status > in sp_head::execute(). Regards, Sergei

2 3

[Maria-developers] Engine independent statistics - naming issues?
by Sergey Petrunia 31 Mar '14

31 Mar '14

Hello, I was looking at whether column names used by EIS feature are consistent with themselves and with ther parts of the server. == Database / table / column names == There are a few naming conventions arready in use: 1. Old school: mysql.columns_priv, tables_priv Db Table_name Column_name 2. SQL STANDARD ALL CAPS HELLO FROM 1970S: information_schema.* (tables, columns, indexes, index_statistics, etc) TABLE_CATALOG (always 'def') TABLE_SCHEMA TABLE_NAME COLUMN_NAME INDEX_NAME 3. InnoDB has its own convention: mysql.innodb_table_stats, innodb_index_stats database_name table_name index_name 4. EITS feature will add: mysql.column_stats, index_stats, table_stats db_name table_name column_name index_name == Other == There are variables @@histogram_size, @@histogram_type. mysql.column_stats has columns named hist_size, hist_type. What's your opinion, should we make some renames to make things more consistent? BR Sergei -- Sergei Petrunia, Software Developer MariaDB | Skype: sergefp | Blog: http://s.petrunia.net/blog

2 1

Re: [Maria-developers] [Commits] Rev 3991: MDEV-5138 Numerous test failures in "mtr --ps --embedded"
by Sergei Golubchik 31 Mar '14

31 Mar '14

Hi, Alexey! On Mar 26, Alexey Botchkov wrote: > Hi, Sergei. > > The Protocol::net_store_data is virtual in the embedded-server case > because we need it implemented differently for Protocol_binary. Ok, got it (finally), sorry. I'm almost always use ctags when looking for definitions, but in this case ctags didn't handle this correctly: #ifndef EMBEDDED_LIBRARY bool Protocol::net_store_data(const uchar *from, size_t length) #else bool Protocol_binary::net_store_data(const uchar *from, size_t length) #endif { ... so I never noticed that Protocol_binary actually has its own implementation of ::net_store_data() in the embedded case. Still, another question, why did you have to rename net_store_data() to net_store_data_cs() ? I've simply made it virtual and everything compiled just fine - it has a different signature so the compiler can distinguish them all right. Regards, Sergei

1 0

[Maria-developers] MariaDB 10.0.10 release prep
by Daniel Bartholomew 28 Mar '14

28 Mar '14

All, To get a jump on things, I've created the initial changelog page and a placeholder release notes page for the upcoming MariaDB 10.0.10 release. - https://mariadb.com/kb/en/mariadb-10010-changelog/ - https://mariadb.com/kb/en/mariadb-10010-release-notes/ Please let me know of things that need to be in the release notes for this release as soon as you can (or just add them yourself). The changelog page will also likely need to be updated as final pushes are made. As of right now it is up to revision 4135. FYI: I will be unresponsive on IRC or email until around mid-afternoon on Saturday due to some family events, but I will be around in the afternoon/evening on Saturday and Sunday to finish the various necessary tasks in preparation for the release. Thanks! -- Daniel Bartholomew MariaDB

1 0

[Maria-developers] A joint patch for MDEV-5969 and MDEV-5971
by Alexander Barkov 28 Mar '14

28 Mar '14

Hello, please review a joint patch fixing a crashing bug MDEV-5969, together with a non-crashing bug MDEV-5971. Thanks.

1 0

Re: [Maria-developers] parallel replication performance
by Kristian Nielsen 27 Mar '14

27 Mar '14

Axel Schwenke <axel(a)askmonty.org> writes: > The poor performance of MySQL with 1 database is expected. However MySQL is > much better with 32 databases. Even better than MariaDB :( Ok, so I looked closer at the numbers (of which there were quite a few, thanks for doing this work :-). I have some idea of what could be going on. The short story is that for this kind of configuration, it will be necessary to configure @@slave_domain_parallel_threads to be at most the value of @@slave_parallel_threads divided by the number of domain ids used. Eg. with 32 databases/domains, --slave-parallel-threads=64 --slave-domain-parallel-threads=2. Otherwise the domains will starve each other for threads, leading to poor parallelism. In fact, with such high domain parallelism @ 32 databases, I think just using --slave-domain-parallel-threads=1 could give much better performance. The longer story: MariaDB parallel replication uses a single pool of worker threads, of size @@slave_parallel_threads. GTIDs are read serially from the relay log in the SQL driver thread, and scheduled to worker threads. Each replication domain is scheduled independently from all other domains. Each GTID is allocated to a new worker thread if possible. If no worker thread is available (because all workers already have at least one GTID queued for them), then the SQL driver thread waits for one to become free; this stalls _all_ domains from getting more work queued. When your benchmark starts, the SQL driver thread will quickly read 64 events from the relay logs and queue them each to a separate worker thread. Then no more free worker threads are available, and the SQL thread will have to wait. With 64 worker threads and 32 domains, it is quite likely that some domains will not have any work to do. At the same time, the group commit size will most likely be much less than 64, so many workers will have to wait for the previous GTID in their domain without being able to do any work, especially at the "fast" configuration where commit (which can always run in parallel) does not take much time. The result will be a lot of threads waiting on each other and waking each other up, and little real work done. By setting @@slave_domain_parallel_threads, it is possible to limit how many worker threads a single domain is allowed to grab, thereby ensuring that worker threads will be available for all domains to be active doing work. Those limited number of threads will be used round-robin to queue work for; this also reduces thread sleep and wakeup, as with luck a worker thread that completes one commit will already have more work queued up for it at that point that it can process without first returning to the thread pool and going to sleep. In particular, with @@slave_domain_parallel_threads=1, we will just have 32 threads, one for each domain, each processing events independently in parallel at full speed, which is probably the best way to handle a workload as evenly distributed across domains as this one is. It would be very useful to see new benchmarks with those results. And if time permits, @@slave_domain_parallel_threads=2 could also be interesting; maybe there could be some extra performance due to more group commit in the "peer" configuration, or maybe the cost of repeatedly alternating between two worker threads within the domain will cause reduced performance. We can actually see some evidence of this theory in the numbers. Where number of worker threads is much larger than number of domains (so starvation is less of a problem), MariaDB does much better, even better than MySQL 5.6 in some cases. But performance drops as the number of domains increase, possibly because starvation kicks in. Still, even if this theory holds, we do see 5-10% slower performance of MariaDB at eg. slave@1 compared to MySQL. This suggests some higher overhead somewhere, which I need to look into at some point, though it will be a while yet before I have time for that, I am afraid. And in any case, this benchmark is highly useful as a test - as I said before, serious load testing of the domain_id parallelism has received very little testing, so it is quite possible that there is simply some bugs that cause things to work poorly. The extra testing with @@slave_domain_parallel_threads=1 (and possibly 2) will be very useful to help understand this better. > I think 32 databases is a bit too much, not only that MySQL then shines so > bright, it's also unrealistic in the real world. What do you think? I can > rerun the benchmark with 4 or 8 databases (will take 4-5 hours). Quite > certainly MySQL will have worse numbers then :) Well, I think 32 databases is not that unrealistic - shared hosting for example could have many more. The completely even spacing of the load among those databases however is probably unrealistic. But I think we should investigate and solve the issues for MariaDB @ 32 databases. More databases, and thus more parallelism, should be better for the parallel replication, if it is worse then that is a bug that should be fixed. Even if it is just the need to configure @@slave_domain_parallel_threads, maybe we could document that better. > I also notice that for MariaDB 10.0 the tuning of the commit-wait-* > variables is critical. Settings that give trx per commit group tend to slow > down the master a lot. The default settings give only few transactions per Right, this is something to be aware of. Note that for a typical web client application or similar, --commit-wait-* probably will not be much of an issue. An individual client will see slightly higher latency, which should not be much of an issue if it is in the order of a few milliseconds. Throughput is not likely to be affected, just the number of concurrent connections will increase (at least as long as system does not get choked). On the other hand, a load with fixed parallelism, like batch processing or your sysbench runs, can be hurt significantly on throughput by --commit-wait-*, as your tests show. Also, I have heard from several users that the ability to throttle a busy master in order to prevent it from running too far ahead of the slaves, is actually often seen as a benefit rather than a performance problem. Semi-sync replication also is sometimes used to achieve similar throttling. So yes, it is a tuning parameter that is unfortunately rather sensitive depending on the actual load and applications. > Please also check the "master" page of the comparison sheet. It shows the > cost of turning on the binlog. Both MySQL and MariaDB show significant > performance loss at high concurrency when the binlog is turned off. This is > completely counter-intuitive. Kristian, can this be related to MDEV-5802? It might be related. Another possibility is that this is related to InnoDB's known weaknesses at high concurrency. When the binlog is enabled, group commit will handle the commit of a lot of transactions serially, in a tight loop inside a single thread. When the binlog is disabled, all of those transactions will be committing each in their separate thread, all of those threads heavily contending each other for the hot mutexes inside InnoDB. It would need a lot of research to be sure that this is the cause, but it's a plausible guess, at least. Thanks for your efforts so far! Overall, I think the results look pretty good, actually. We see a good speedup from MariaDB parallel replication in many of the configurations. And your comparison shows that using group commit, a large percentage of the speedup can be obtained automatically from group commit, without any need to change applications and partition the data into separate databases. - Kristian.

2 2