- developers - lists.mariadb.org

[Maria-developers] Doubts about index and optimization
by Roberto Spadim 13 Jul '13

13 Jul '13

Hi guys i have two doubts... First the r-tree index Could i index two integer or float or decimal columns using rtree And queries like X>=1 and x<=2 and y>=1 and y<=2 Be optimized using rtree? Another doubt... i dont have a mariadb now to test, i'm in cellphone... this kind of query is optimized if i have a x,y btree index? Btree will use x only, or x and y? Second doubt I'm thinking about enum rewrite optimzation and i didn't write anything yet, just studying mariadb code... When using queries like this Where x<=5 Or where x>=5 Or without = part Does mysql know what's the max and min value of x, and rewrite it to something X<=5. To. X in (0,1,2,3,4,5) when x is a integer for example and min(x)=0 I'm thinking about this, in conditions like the rtree optimization, example... we could optimize x and y with something like X>1 and x<5 and y>1 and y<5 to X in (2,3,4) and y in (2,3,4) It's something like enum optimization but using integers and max min informations Well i don't know the full code but i didn't found this information (i don't remember at least) Anyone know something about this in source? If not i will consider this optimization when doing the enum optimization (i don't remember the mdev number now, but anyone interested in this optimization can search in jira and find it, i reported and done any job yet just the raw and high implementation idea) Thanks guys

1 0

[Maria-developers] OLAP
by Roberto Spadim 12 Jul '13

12 Jul '13

Hi guys, there's any good OLAP tool for mariadb? maybe one that we could package with mariadb as default? -- Roberto Spadim

2 5

[Maria-developers] Status on MDEV-4506, parallel replication
by Kristian Nielsen 12 Jul '13

12 Jul '13

So I have been working for some weeks now on implementing MDEV-4506. This task is about making the slave apply (some) events in parallel threads, to speed up replication and reduce the risk of slave not being able to keep up with a busy master. Events are applied in parallel on the slave if they were group-committed together on the master. This is an easy way to detect transactions that are independent. Note that this is transparent to applications; while transactions are executed in parallel on the slave, they are still committed in the same order as on the master. I also added parallel execution of events with different GTID domain id. This makes testing a lot easier (no need to carefully arrange timing to get a specific group commit on the master), and also really is the whole point of much of my hard work on GTID. So if we have multi-source M1->S1, M2->S1, and S1->S2, then S2 will be able to execute events from M1 in parallel with those from M2, just like S1 can. And the user can explicitly set the domain_id different for eg. a long-running ALTER or UPDATE, and this way get it to run in parallel and not cause a huge replication delay. ---- On the master, I added to each GTID event a commit_id. If two transactions group-commit together, they are binlogged with the same commit_id; if not, they get different commit_ids. Thus, the slave can detect the possibility of executing two transactions in parallel by checking if the commit_ids are equal. On the master, I implemented --binlog-commit-wait-count=N and --binlog-commit-wait-usec=T. A transaction will wait at most T microseconds for at least N transactions to queue up and be ready for group commit. This allows to deliberately delay transactions on the master in order to get bigger group commits and thus better opportunity for parallel execution (and again it makes testing easier). On the slave, I implemented --slave-parallel-threads=N. If N>0, that many threads will be spawned, and events will be executed in parallel (if possible) by those threads. ---- The current code is pushed here (it is based on 10.0-base): lp:~maria-captains/maria/10.0-knielsen It is still far from finished, but it now works sufficiently that I could do some basic benchmarking (just on my laptop, completely unscientifically). First, I prepared a binlog on the master with plenty of opportunity for parallel replication. I started the master with --binlog-commit-wait-count=20 --binlog-commit-wait-usec=1000000. I then ran this Gypsy script: ----------------------------------------------------------------------- i|1|DROP TABLE IF EXISTS t1 i|1|CREATE TABLE t1 (a INT PRIMARY KEY, b VARCHAR(100)) ENGINE=InnoDB p|1|REPLACE INTO t1 (a,b) VALUES (? MOD 10000, ?)|int,varchar /home/knielsen/my/gypsy/words ----------------------------------------------------------------------- gypsy --queryfile=simple_replace_load.gypsy --duration=20 --threads=40 This results in a binlog with about 65k updates to the table, group-committed in batches of 20 transactions. I then started a fresh slave and let it replicate everything with START SLAVE UNTIL; the time to replicate all the events is then easy to see in the slave error log. The time to replicate everything with unmodified 10.0-base was 99 seconds. With --slave-parallel-threads=25, it was just 22 seconds. So that is a 4.5 times speedup, which is quite promising. Also note that at 22 seconds, the slave is within 10% of the speed of the master. ---- But as I said, there is still significant work left to do. I put a ToDo list at the top of sql/rpl_parallel.cc. Some of the big remaining issues: 1. The existing code is not thread-safe for class Relay_log_info. This class contains a bunch of stuff that is specific to executed transactions, not related to relay-log at all. This needs to be moved to the new struct rpl_group_info I introduced, and all code updated to pass around a pointer to that struct instead. There may also be a need to add additional locking on Relay_log_info, existing code needs review for this. 2. Error handling needs to be implemented, it is rather more complex in the parallel case. If one transaction fails (and retry also fails), then we need to somehow get hold of all later transactions that are in the process of parallel execution, and abort them + roll them back. Otherwise we get inconsistent binlog position for the next slave restart. 3. In the old code, when the SQL thread is stopped, it has logic to let the current event group (=transaction) replicate to completeness first, with a timeout to force a stop in the middle of the event group if eg. the master has disappeared. This logic needs to be re-implemented to work when having any number of event groups executing in parallel. (It is important to let the groups complete execution when doing non-transactional stuff that cannot be rolled back, otherwise again the slave position becomes inconsistent for next slave restart). So as you see, there is quite a bit of work left on this (as well as on GTID). So I would very much welcome any help on this to avoid causing delays for 10.0-GA ... - Kristian.

1 0

[Maria-developers] a bug of ding-qing parallel replication
by nanyi607rao 11 Jul '13

11 Jul '13

hi guys, I find a bug of ding-qing parallel replication. let's consider a table like this: create table t1( a int(11) NOT NULL DEFAULT '0', b varchar(10), PRIMARY KEY (a) )ENGINE=InnoDB if we do transactions on master(binlog_format=ROW) like this: trans 1: begin; insert into t1 values(1, "tt"); update t1 set a=2 where a=1; commit; trans 2; begin; update t1 set b="kk" where a=2; commit; then this two transactions binlog transmit to the slave, because trans 1 and trnas 2 has different primary keys, it would disaptch in different threads like this: thread 1 do trans 1 thread 2 do trans 2 thread 2 may update t1 before thread 1, then it will fail. maybe thread 2 can retry sever times ,but it don't solve this problem compeletly.. 2013-07-11 nanyi607rao

3 2

[Maria-developers] deferred_event
by nanyi607rao 10 Jul '13

10 Jul '13

Hi guys, as I know, when replication filter used, the defered events is a container hold on events(such as Intvar-, Rand-, User_var- log_event)that be applied delay. why those events need be applied delay? and what is parent Query mentioned in codes? Thanks! 2013-07-10 nanyi607rao

2 1

[Maria-developers] buildbot config
by Leif Walsh 09 Jul '13

09 Jul '13

Hi, I'm learning buildbot and I was wondering if I could take a look at the master.cfg for buildbot.askmonty.org as a reference for learning common practices and things like that. Is it available somewhere? -- Cheers, Leif

2 2

[Maria-developers] merge for MySQL56 FSP data types
by Alexander Barkov 09 Jul '13

09 Jul '13

Hi Serg, Please review the patch merging MySQL-5.6 fractional second precision enabled data types. Thanks.

3 7

[Maria-developers] Connect: compilation warnings
by Alexander Barkov 08 Jul '13

08 Jul '13

Hi Oliver, I fixed almost all compilation warnings that happened in Connect on my Fedora 17 laptop with gcc-4.7.2. Most of them were "variable is initialized but not used" and "no previous declaration". There were not serious, but very unpleasant for developers. (Note, we try to keep the tree in a shape that it compiles without warnings. Connect must follow this rule as well!!!) I think there was a bug in one place: how SQLSetStmtAttr() was called. See "bzr diff -c 3827" for details. The value of m_RowsetSize was erroneously passed, but SQLSetStmtAttr() expects the address of a SQLUINT variable (not a value!). See the ODBC docs for the details: http://msdn.microsoft.com/en-us/library/windows/desktop/ms712631%28v=vs.85%… One compilation warning still remains: maria-10.0-connect/storage/connect/tabsys.cpp:279:26: warning: cast to pointer from integer of different size [-Wint-to-pointer-cast] It looks like a bug. Cast from 4-byte integer to 8-byte pointer is obviously wrong. Please address the problem. Thanks.

1 0

[Maria-developers] Warning in dynamic columns code
by Sergei Petrunia 08 Jul '13

08 Jul '13

Hi Sanja, I have been seeing this warning on a number of platforms: 10.0-monty/sql/item_strfunc.cc: In member function ‘virtual bool Item_func_dyncol_create::fix_fields(THD*, Item**)’: 10.0-monty/sql/item_strfunc.cc:3887: warning: suggest a space before ‘;’ or explicit braces around empty body in ‘for’ statement I assume it is harmless, but just in case, passing it over to you. Could you please check it and get rid of it? Thanks Sergei -- Sergei Petrunia, Software Developer Monty Program AB, http://askmonty.org Blog: http://s.petrunia.net/blog

2 1

[Maria-developers] SO_REUSEPORT and mariadb
by Roberto Spadim 08 Jul '13

08 Jul '13

hi guys, i was reading the SO_REUSEPORT of kernel 3.9, and i didn't found it in mariadb maybe we could use it? here are the SO_REUSEPORT topics: https://lwn.net/Articles/542629/ http://linux.dell.com/files/presentations/Linux_Plumbers_Conf_2010/Scaling_… here the important parts: ------------------ 1)The first of the traditional approaches is to have a single listener thread that accepts all incoming connections and then passes these off to other threads for processing. The problem with this approach is that the listening thread can become a bottleneck in extreme cases. In early discussions<http://thread.gmane.org/gmane.linux.network/102140/focus=102150> on SO_REUSEPORT, Tom noted that he was dealing with applications that accepted 40,000 connections per second. Given that sort of number, it's unsurprising to learn that Tom works at Google. 2)The second of the traditional approaches used by multithreaded servers operating on a single port is to have all of the threads (or processes) perform an accept() call on a single listening socket in a simple event loop of the form: while (1) { new_fd = accept(...); process_connection(new_fd); } ------------------ i didn -- Roberto Spadim

1 1