July 2018 - discuss - lists.mariadb.org

Re: [Maria-discuss] Replication Problem
by Kristian Nielsen 05 Jul '18

05 Jul '18

Thomas Plant <thomas(a)plant.systems> writes: > Your tip with 'MASTER_USE_GTID=slave_pos' fixed it.Will have to look > better in the documentation next time, never found the 'slave_pos' > mentioned or 'SET sql_log_bin=0'. > > Thank you very much for your help. Welcome, glad that you solved your problem. The slave_pos/current_pos is confusing a lot of users. It would probably have been better if current_pos had never been introduced. > so you mean that disabling the binary log on the slave would be > indicated? Can I do it while it is online? SET sql_log_bin=0 disables binlogging only for the following queries done in that connection (not in general). So yes, it can be done online. You would do it for queries that you will not want replicated to other servers. For example, if you later make this slave the master, and put the old master to replicate from the old slave (now new master), you probably do not want to replicate your earlier slave-fixup-query. Hence the suggestion to SET sql_log_bin=0 to avoid having this query in the slave binlog. But if using MASTER_USE_GTID=slave_pos, in most cases it won't matter one way or the other. - Kristian.

1 0

[Maria-discuss] Replication Problem
by Thomas Plant 04 Jul '18

04 Jul '18

Hello, I'd have a question about MariaDB 10.1 replication, which has been interrupted by a power outage in our datacenter. We started the master server and had a duplicate id in a table on the slave, so replication stopped. We did not have time to adjust this at the moment, there were a lot of other systems to start and check if all was alright. Today I had time to look at the error, removed the duplicate ID from the table and started the slave thread again using 'start slave;'. But now I get another error: Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Error: connecting slave requested to start from GTID 0-2-2948175468, which is not in the master's binlog. Since the master's binlog contains GTIDs with higher sequence numbers, it probably means that the slave has diverged due to executing extra erroneous transactions' Can this be resolved? Or will I have to start replication from scratch? Greetings, Thomas

5 4

[Maria-discuss] MariaDB embedded, prepared statements
by Julien Muchembled 02 Jul '18

02 Jul '18

Hello, In our application (NEO - https://neo.nexedi.com/ ), we have 2 backends: SQLite and MySQL/MariaDB We also have a benchmarking tool working with a quite small dataset and showing that SQLite is ~30% than MariaDB. For years, we supposed that SQLite performed better in some cases for 2 reasons: - everything processed in same thread (i.e. no socket or context switches) - precompiled queries And by combining both, skip any kind of string serialization (i.e. do not format a query on one side, and parse on the other side). Now, we are trying to optimize our MariaDB backend by trying to do like SQLite. = MariaDB embedded I could make it work, but only with InnoDB for the moment. I kept using the same binding, MySQLdb, which I rebuilt against libmariadbd-dev_10.1.29-6+b1_amd64.deb Currently, we only use TokuDB in production and I have the same issue as: https://forums.mysql.com/read.php?94,629158,629158 ("embedded server cannot accept a plugin engine") This user solved it by embedding his engine inside libmysqld. I wonder if it will work with TokuDB. Anyway, I haven't seen any speed improvement. = Prepared statements I couldn't find anything else that's closer to SQLite precompiled queries. Which means that there's still some formatting/parsing. | Com_stmt_execute | 96528 | | Com_stmt_prepare | 40 | But again, not faster. Well, I do my best to have stable results, like having the DB in tmpfs or disabling C-states, but it's hard to measure small changes of performance. With 10.1.29, prepared statements look slightly slower. I also tried the new EXECUTE syntax with 10.2.16 to avoid SET queries, and it may be 1% faster (< error margin). I only turned the most often used queries into prepared statements. Here is one of the most complex queries we have: SELECT tid, compression, data.hash, value, value_tid FROM obj FORCE INDEX(PRIMARY) LEFT JOIN data ON (obj.data_id = data.id) WHERE `partition`=? AND oid=? AND tid < ? ORDER BY tid DESC LIMIT 1 https://mariadb.com/kb/en/library/prepared-statements/ should explain the purpose of prepared statements. The "In addition to using prepared statements from the libmysqld" clause seems to tell there's a specific kind of prepared statements within MariaDB embedded but I haven't found anything about that. We're disappointed by all these results. Did I miss something ? If not, I'll have to do some profiling of MariaDB. Julien

1 0