- discuss - lists.mariadb.org

[Maria-discuss] Large query performance degrade after converting table from MyISAM to InnoDB
by Conor Murphy 17 Dec '18

17 Dec '18

Hi, I'm trying to covert an existing database from MyISAM to InnoDB. The conversion completed okay, but I'm seeing one particular query causing problems, with MyISAM it was taking ~ 6 minutes to complete, with InnoDB it's taking nearly 14 hours The query is as follows SELECT procid, UNIX_TIMESTAMP(MAX(time)), COUNT(*) AS num FROM proc_stats GROUP BY procid HAVING num < 100 EXPLAIN SELECT procid, UNIX_TIMESTAMP(MAX(time)), COUNT(*) AS num FROM proc_stats GROUP BY procid HAVING num < 100 +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | 1 | SIMPLE | proc_stats | index | NULL | pidIdx | 2 | NULL | 956928549 | | +------+-------------+------------+-------+---------------+--------+---------+------+-----------+-------+ | proc_stats | CREATE TABLE `proc_stats` ( `time` datetime NOT NULL, `siteid` smallint(5) unsigned DEFAULT NULL, `serverid` smallint(5) unsigned NOT NULL, `procid` smallint(5) unsigned NOT NULL, `cpu` smallint(5) unsigned DEFAULT NULL, `mem` mediumint(8) unsigned NOT NULL, `thr` smallint(5) unsigned NOT NULL, `fd` smallint(5) unsigned DEFAULT NULL, `rss` mediumint(8) unsigned DEFAULT NULL, `nproc` tinyint(3) unsigned NOT NULL DEFAULT '0', `sample_interval` smallint(5) unsigned DEFAULT NULL, KEY `pidIdx` (`procid`), KEY `serverTimeIdx` (`serverid`,`time`), KEY `siteIdIdx` (`siteid`,`time`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 /*!50100 PARTITION BY RANGE ( TO_DAYS(time)) (PARTITION P20120101 VALUES LESS THAN (734868) ENGINE = InnoDB, PARTITION P20151201 VALUES LESS THAN (736298) ENGINE = InnoDB, PARTITION P20160101 VALUES LESS THAN (736329) ENGINE = InnoDB, PARTITION P20160201 VALUES LESS THAN (736360) ENGINE = InnoDB, PARTITION P20160301 VALUES LESS THAN (736389) ENGINE = InnoDB, PARTITION P20160401 VALUES LESS THAN (736420) ENGINE = InnoDB, PARTITION P20160501 VALUES LESS THAN (736450) ENGINE = InnoDB, PARTITION P20160601 VALUES LESS THAN (736481) ENGINE = InnoDB, PARTITION P20160701 VALUES LESS THAN (736511) ENGINE = InnoDB, PARTITION P20160801 VALUES LESS THAN (736542) ENGINE = InnoDB, PARTITION P20160901 VALUES LESS THAN (736573) ENGINE = InnoDB, PARTITION P20161001 VALUES LESS THAN (736603) ENGINE = InnoDB, PARTITION P20161101 VALUES LESS THAN (736634) ENGINE = InnoDB, PARTITION P20161201 VALUES LESS THAN (736664) ENGINE = InnoDB, PARTITION P20170101 VALUES LESS THAN (736695) ENGINE = InnoDB, PARTITION P20170201 VALUES LESS THAN (736726) ENGINE = InnoDB, PARTITION P20170301 VALUES LESS THAN (736754) ENGINE = InnoDB, PARTITION P20170401 VALUES LESS THAN (736785) ENGINE = InnoDB, PARTITION P20170501 VALUES LESS THAN (736815) ENGINE = InnoDB, PARTITION P20170601 VALUES LESS THAN (736846) ENGINE = InnoDB, PARTITION P20170701 VALUES LESS THAN (736876) ENGINE = InnoDB, PARTITION P20170801 VALUES LESS THAN (736907) ENGINE = InnoDB, PARTITION P20170901 VALUES LESS THAN (736938) ENGINE = InnoDB, PARTITION P20171001 VALUES LESS THAN (736968) ENGINE = InnoDB, PARTITION P20171101 VALUES LESS THAN (736999) ENGINE = InnoDB, PARTITION P20171201 VALUES LESS THAN (737029) ENGINE = InnoDB, PARTITION P20180101 VALUES LESS THAN (737060) ENGINE = InnoDB, PARTITION Q201804 VALUES LESS THAN (737150) ENGINE = InnoDB, PARTITION Q201807 VALUES LESS THAN (737241) ENGINE = InnoDB, PARTITION Q201810 VALUES LESS THAN (737333) ENGINE = InnoDB, PARTITION Q201901 VALUES LESS THAN (737425) ENGINE = InnoDB, PARTITION QMAXVALUE VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ | While the query is running, all I can see in the processlist is | 64313 | statsadm| 64313 | statsadm | priv:41254 | statsdb | Query | 13001 | Sending data | SELECT procid, MAX(time), COUNT(*) AS num FROM proc_stats GROUP BY procid HAVING num < 100 | 0.000 | Version is 5.5.60-MariaDB Any suggestions on how to localize why the query is taking so long with InnoDB? Thanks, Conor

4 9

[Maria-discuss] Missing rights on 'CREATE ALGORITHM=UNDEFINED DEFINER...'
by Thomas Plant 13 Dec '18

13 Dec '18

Hello all, I have a request from a user who can not create a view in his database. He has the following rights granted: +----------------------------------------------------------------------------------------------------------------+ | Grants for user@% | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '***************' | | GRANT ALL PRIVILEGES ON `dbname`.* TO 'user'@'%' | +----------------------------------------------------------------------------------------------------------------+ The view he tries to create looks like these: CREATE ALGORITHM=UNDEFINED DEFINER=`xxxxxxxxxxx`(a)`xx.xxx.xxx.x/xxx.xxx.xxx.x` SQL SECURITY DEFINER VIEW `jos_docman_file_counts` AS select `jos_docman_documents`.`storage_path` AS `storage_path`,count(0) AS `count` from `jos_docman_documents` where (`jos_docman_documents`.`storage_type` = 'file') group by `jos_docman_documents`.`storage_path`; And gets as error: #1227 - Access denied; you need (at least one of) the SUPER privilege(s) for this operation What SUPER privilege is needed for this view? The Database is MariaDB 10.1 on Centos 7. Thanks for any hint about his. Greetings, Thomas

2 3

[Maria-discuss] Updating user's max_connections without restart
by Staffan 28 Nov '18

28 Nov '18

Hi, I've tried to change a user's max_connections after the limit has been reached (connection attempts return "has exceeded the 'max_connections_per_hour' resource"). With MariaDB 10.2.18, running using the official docker image, we have "select @@max_connections;" returning 100. We have a user created "WITH MAX_CONNECTIONS_PER_HOUR 50". Now if we bump in to this limit I fail to allow more connections by increasing it. I've tried the following methods (using both 0 and a high value): ALTER USER 'site_devkit_live_index'@'%' WITH MAX_CONNECTIONS_PER_HOUR 0; UPDATE mysql.user SET max_connections = 0 WHERE user='site_devkit_live_index'; FLUSH PRIVILEGES; GRANT USAGE ON *.* TO 'site_devkit_live_index'@'%' WITH MAX_CONNECTIONS_PER_HOUR 0; The following indicates that the updates work: USE mysql; SELECT host, user, max_user_connections, max_connections FROM user; I've also tried deleting and re-creating the user. What would it take to make these updates affect the current instance? We'd like to avoid restarts. regards /Staffan

2 2

[Maria-discuss] Rocksdb_no_file_closes in MariaDB MyRocks
by Jonas Krauss 19 Nov '18

19 Nov '18

Hi all, I am trying to put a MariaDB MyRocks database into production and right now I am facing problems regarding the memory usage of MyRocks. I haven't been able to identify one specific problem but the following links contained some useful information: https://github.com/facebook/rocksdb/issues/3216 https://github.com/facebook/rocksdb/wiki/Memory-usage-in-RocksDB The current behavior of the mysqld process is that when putting the database into production (an OLTPish process) on a machine with 64G RAM and a block cache configured with 16G it starts to slowly accumulate more and more memory until swap is used. This is obviously not suitable for production. Currently I am wondering about a particular statistic, the number of open and closed files. *Particularly I am wondering why Rocksdb_no_file_closes is staying a zero at all time while Rocksdb_no_file_opens slowly increases over time.* Does anybody know why that is? Could it be responsible for the steady memory increase? I experimented with the parameter rocksdb_max_open_files, it was set to -1, I tried setting it to 1536, 512 and 128. The lower the value the faster Rocksdb_no_file_opens increases (which seems right). However, at no setting do I see any change in Rocksdb_no_file_closes. Running MariaDB 10.2.18. Here are the settings regarding rocksdb in my.cnf: # MyRocks plugin-load-add = ha_rocksdb.so default-storage-engine = rocksdb default-tmp-storage-engine = MyISAM transaction-isolation = READ-COMMITTED rocksdb_unsafe_for_binlog = 1 # enables statement based replication rocksdb_datadir = /var/local/mysql/rocksdb rocksdb_wal_dir = /var/mysql_logs/rocksdb rocksdb_tmpdir = /var/mysql_logs/rocksdb rocksdb_flush_log_at_trx_commit = 0 rocksdb_use_direct_io_for_flush_and_compaction = 0 rocksdb_use_direct_reads = 0 rocksdb_max_open_files = 512 rocksdb_max_background_jobs = 8 rocksdb_max_total_wal_size = 4G rocksdb_block_size = 64K rocksdb_block_cache_size = 4G rocksdb_table_cache_numshardbits = 6 rocksdb_new_table_reader_for_compaction_inputs = 1 rocksdb_compaction_readahead_size = 4M #rocksdb_db_write_buffer_size = 0 # max write buffer across all column families, zero = disabled # rate limiter rocksdb_bytes_per_sync = 4M rocksdb_wal_bytes_per_sync = 4M rocksdb_rate_limiter_bytes_per_sec = 80M # MB/s. Increase if you're running on higher spec machines # triggering compaction if there are many sequential deletes rocksdb_compaction_sequential_deletes_count_sd = 1 rocksdb_compaction_sequential_deletes = 199999 rocksdb_compaction_sequential_deletes_window = 200000 # read free replication #rocksdb_rpl_lookup_rows=0 rocksdb_default_cf_options=write_buffer_size=128m;max_write_buffer_number=4;target_file_size_base=256m;max_bytes_for_level_base=2560m;target_file_size_multiplier=2;level0_file_num_compaction_trigger=4;level0_slowdown_writes_trigger=10;level0_stop_writes_trigger=15;compression=kSnappyCompression;bottommost_compression=kZlibCompression;compression_opts=-14:1:0;block_based_table_factory={cache_index_and_filter_blocks=1;filter_policy=bloomfilter:10:false;whole_key_filtering=1};level_compaction_dynamic_level_bytes=true;optimize_filters_for_hits=true;compaction_pri=kMinOverlappingRatio

1 0

[Maria-discuss] SHOW CREATE TABLE returns binary data
by Andy Salnikov 19 Nov '18

19 Nov '18

Hi, one puzzling feature that we have observed with mariadb is the encoding of the result returned from "SHOW CREATE TABLE" query. In our Python-based service we issue that query and we expect the result to be returned as Python string, that worked OK except that we discovered that it breaks for one large/wide table. The query returns Python bytes object for that particular table for some reason. I looked at the result returned from query and it looks like the data is returned using different data type, here is a Python example for two different tables: This is a regular (narrow) table and it works as expected: >>> c.execute("SHOW CREATE TABLE `database`.`table1`") >>> c.description (('Table', 253, 13, 64, 64, 39, 0), ('Create Table', 253, 375, 1024, 1024, 39, 0)) 253 in second tuple means that this is a MYSQL_TYPE_VAR_STRING type, which is converted into Python string. this is for very wide table: >>> c.execute("SHOW CREATE TABLE `qservTest_case130_qserv`.`deepCoadd_meas`") >>> c.description (('Table', 253, 14, 64, 64, 39, 0), ('Create Table', 250, 26235, 78705, 78705, 39, 0)) in this case type code is 250 which means MYSQL_TYPE_MEDIUM_BLOB, and Python (correctly) refuses to understand that this is really a string and keeps it as bytes. It looks like this is triggered by the size of the string, but I could not find any document that mentions this unexpected behavior. Is there any documentation that explains what to expect in this case? Can this behavior be changed with some session or server variable? To convert that BLOB into string I'd also need to know the encoding for its contents, is it supposed to be the same as encoding used to send VAR_STRING type or is it some mariadb-internal encoding used by information schema? Thanks, Andy

1 0

[Maria-discuss] 100% CPU on simple queries when join_buffer_size is large
by marki 17 Nov '18

17 Nov '18

Hey, Server version: 10.0.35-MariaDB SLE 12 SP1 here I have a problem with a query/process hanging "Copying to tmp table" and CPU at 100% MariaDB [iplocator]> EXPLAIN SELECT *, l3l2.time AS time FROM l3l2 JOIN l2l1 USING (mac) JOIN l1human h ON l2l1.switch=h.switch AND l2l1.port=h.portid WHERE ip=INET_ATON('192.168.1.1') ORDER BY l3l2.time DESC -> ; +------+-------------+-------+--------+---------------+---------+---------+-------------------------------------------+--------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +------+-------------+-------+--------+---------------+---------+---------+-------------------------------------------+--------+---------------------------------+ | 1 | SIMPLE | l2l1 | ALL | NULL | NULL | NULL | NULL | 10379 | Using temporary; Using filesort | | 1 | SIMPLE | h | eq_ref | PRIMARY | PRIMARY | 51 | iplocator.l2l1.switch,iplocator.l2l1.port | 1 | Using where | | 1 | SIMPLE | l3l2 | ALL | NULL | NULL | NULL | NULL | 157794 | Using where | +------+-------------+-------+--------+---------------+---------+---------+-------------------------------------------+--------+---------------------------------+ 3 rows in set (0.00 sec) We have made some modifications to my.cnf for Cacti, namely: ; for cacti <3 [mysqld] max_heap_table_size = 92M max_allowed_packet = 16777216 tmp_table_size = 64M join_buffer_size = 64M ; problem with iplocator when this option is set innodb_buffer_pool_size = 462M ;ssd innodb_doublewrite = OFF innodb_additional_mem_pool_size = 80M innodb_flush_log_at_timeout = 3 ;ssd innodb_read_io_threads = 32 ;ssd innodb_write_io_threads = 16 We found out by trial and error that when we do not increase join_buffer_size (leave at default 128k I believe) then the query mentioned above runs ok. However cacti would require us to increase it, which is when the query above will fail with said error. ===================================== 2018-11-16 16:41:17 7fbf9c202700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 15 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 13 srv_active, 0 srv_shutdown, 1 srv_idle srv_master_thread log flush and writes: 5 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 14 OS WAIT ARRAY INFO: signal count 63 Mutex spin waits 52, rounds 366, OS waits 10 RW-shared spins 37, rounds 280, OS waits 4 RW-excl spins 14, rounds 125, OS waits 0 Spin rounds per wait: 7.04 mutex, 7.57 RW-shared, 8.93 RW-excl ------------ TRANSACTIONS ------------ Trx id counter 702870126 Purge done for trx's n:o < 702870081 undo n:o < 0 state: running but idle History list length 220 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0, not started MySQL thread id 6, OS thread handle 0x7fbf9c202700, query id 581 localhost root init show engine innodb status ---TRANSACTION 702870124, not started MySQL thread id 4, OS thread handle 0x7fbf9c24b700, query id 580 localhost icingaweb2 cleaning up -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (read thread) I/O thread 11 state: waiting for completed aio requests (read thread) I/O thread 12 state: waiting for completed aio requests (read thread) I/O thread 13 state: waiting for completed aio requests (read thread) I/O thread 14 state: waiting for completed aio requests (read thread) I/O thread 15 state: waiting for completed aio requests (read thread) I/O thread 16 state: waiting for completed aio requests (read thread) I/O thread 17 state: waiting for completed aio requests (read thread) I/O thread 18 state: waiting for completed aio requests (read thread) I/O thread 19 state: waiting for completed aio requests (read thread) I/O thread 20 state: waiting for completed aio requests (read thread) I/O thread 21 state: waiting for completed aio requests (read thread) I/O thread 22 state: waiting for completed aio requests (read thread) I/O thread 23 state: waiting for completed aio requests (read thread) I/O thread 24 state: waiting for completed aio requests (read thread) I/O thread 25 state: waiting for completed aio requests (read thread) I/O thread 26 state: waiting for completed aio requests (read thread) I/O thread 27 state: waiting for completed aio requests (read thread) I/O thread 28 state: waiting for completed aio requests (read thread) I/O thread 29 state: waiting for completed aio requests (read thread) I/O thread 30 state: waiting for completed aio requests (read thread) I/O thread 31 state: waiting for completed aio requests (read thread) I/O thread 32 state: waiting for completed aio requests (read thread) I/O thread 33 state: waiting for completed aio requests (read thread) I/O thread 34 state: waiting for completed aio requests (write thread) I/O thread 35 state: waiting for completed aio requests (write thread) I/O thread 36 state: waiting for completed aio requests (write thread) I/O thread 37 state: waiting for completed aio requests (write thread) I/O thread 38 state: waiting for completed aio requests (write thread) I/O thread 39 state: waiting for completed aio requests (write thread) I/O thread 40 state: waiting for completed aio requests (write thread) I/O thread 41 state: waiting for completed aio requests (write thread) I/O thread 42 state: waiting for completed aio requests (write thread) I/O thread 43 state: waiting for completed aio requests (write thread) I/O thread 44 state: waiting for completed aio requests (write thread) I/O thread 45 state: waiting for completed aio requests (write thread) I/O thread 46 state: waiting for completed aio requests (write thread) I/O thread 47 state: waiting for completed aio requests (write thread) I/O thread 48 state: waiting for completed aio requests (write thread) I/O thread 49 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 2867 OS file reads, 24 OS file writes, 24 OS fsyncs 191.12 reads/s, 17088 avg bytes/read, 1.60 writes/s, 1.60 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 11097, seg size 11099, 152 merges merged operations: insert 108, delete mark 70, delete 0 discarded operations: insert 0, delete mark 0, delete 0 13519.57 hash searches/s, 1502.97 non-hash searches/s --- LOG --- Log sequence number 1299475887198 Log flushed up to 1299475887198 Pages flushed up to 1299475527411 Last checkpoint at 1299475527411 Max checkpoint age 80826164 Checkpoint age target 78300347 Modified age 359787 Checkpoint age 359787 0 pending log writes, 0 pending chkp writes 29 log i/o's done, 1.93 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 504315904; in additional pool allocated 0 Total memory allocated by read views 368 Internal hash tables (constant factor + variable factor) Adaptive hash index 8657696 (7670456 + 987240) Page hash 480232 (buffer pool 0 only) Dictionary cache 2436172 (1919248 + 516924) File system 1027312 (812272 + 215040) Lock system 1200352 (1199272 + 1080) Recovery system 0 (0 + 0) Dictionary memory allocated 516924 Buffer pool size 29567 Buffer pool size, bytes 484425728 Free buffers 26668 Database pages 2839 Old database pages 1067 Modified db pages 582 Percent of dirty pages(LRU & free pages): 1.972 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 5, not young 0 0.33 youngs/s, 0.00 non-youngs/s Pages read 2823, created 16, written 0 188.19 reads/s, 1.07 creates/s, 0.00 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 38.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 2839, unzip_LRU len: 0 I/O sum[0]:cur[4], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB 0 RW transactions active inside InnoDB 0 RO transactions active inside InnoDB 0 out of 1000 descriptors used ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 702870080 Read view up limit trx id 702870080 Read view low limit trx id 702870080 Read view individually stored trx ids: ----------------- Main thread process no. 31341, id 140459967563520, state: sleeping Number of rows inserted 11, updated 453, deleted 8, read 12982600 0.73 inserts/s, 30.20 updates/s, 0.53 deletes/s, 865448.97 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ Can someone shed some light on this? It seems to me the appearance of the problem might be related to a recent upgrade of the server which included upgrading mariadb from 10.0.21 to 10.0.35 Thx

2 2

[Maria-discuss] Pipeline-stype slave parallel applier
by andrei.elkin＠pp.inet.fi 16 Nov '18

16 Nov '18

Hello, everybody. Let me offer a bunch of ideas for further improvements in replication, and the parallel one specifically. Your comments, thoughts and critical notes are most welcome! Thank you for your time. Andrei Slave balanced parallel applier ================================ There are a few motivations for this project which include the following reflections: - the slower binlog group transactions applying rate by slave the more transaction sizes differ (must've been few bugs reported): the speed of a caravan is determined by the pace of the slowest camel, or more formally as specified by Amdahl's law (KrinstianN pointed that out to me) - while ordered committing of grouped transactions on slave presents some virtues (like guaranteed consistency with master, monotonic changes to slave execution status as measured by GTID) it still must be taxing on performance. Observe that co-prepared transactions of a master binlog group represent a sort of a transaction on its own. Call it epoch [which must revive memories of NDB cluster], and the original transactions as its sub-transactions or branches. Notice that there is no meaning to sub-transaction internal ordering within the epoch. They can be logged to the binary log in any mutual order, still without exposing any inconsistencies to the user. Specifically, say there are two prepared transactions BEGIN /* A */ BEGIN /* B */ INSERT... INSERT... UPDATE... UPDATE... DELETE... DELETE... COMMIT COMMIT entering the binlog group (ordered) commit module. They can end up into binlog as A,B or B,A, and regardless of that (Gtid actually) order also be acknowledged to the user in either way. The latter fact infers that slave does not have to commit the group sub-transaction/branches in the GTID order. It could be done in any order including no order at all like to do so as one epoch commit. Naturally the latter option is impractical to replication. The current conservative scheduler offers a decent parallelization method still suffering from the aforementioned uneven branch's sizes and inter-thread communications caused by ordering. The optimistic scheduler remedies to some level but not fully (an optimistically executed group may be unbalanced too) and even conceding some risk of performance degradation (rollback is costly). There is an idea reminiscent to pipelining to handle the epoch (the master binlog group) as a single transaction but in parallel. That is to execute its statements by multiple workers, this time scheduling them fairly, and "atomically" commit their works. To the fairness part, workers get assigned by a statement at a time from the epoch transaction(sub-transaction BEGIN, COMMIT removed). Say 'E' an epoch consists of 's' of sub-transaction E := { T_1, T_2, ... T_s } For each m from 1 to s sub-transactions T_m gets scheduled to some worker W_1 W_2 ... W_k | | | V V V T_m := { e_1, e_2, ... e_l } Hereinafter I use the TeX math notation, '^' - a superscript attached to the event to designate its parent transaction sequence number (Gtid seq_no), '_' - a subscript to enumerate an object within its compound object. e_i stands for T-ransation's statements, W_t:s are workers. Effectively the epoch breaks into modified branches executed by workers on one to one basic: E := { T'_1, T'_2, ... T'_k } here 'k' is the number of workers engaged, T' indicates the modified transaction. The branches are executed until they are ready to prepare which event is triggered by scheduling of the last statement of the last original sub-transaction. It's clear that sizes of the modified branches are even this time. The last statement worker coordinates 2pc. Thinking more technically workers can consume/pull from the current epoch presented as a queue which is pushed into by statement producer. Here is a possible state of the queue when m:th statement of T_n is about to be pushed: e^n_k -> [ e^n_m-1, ..., e^n_2, e^n_1; ...; e^1_l1, ... e^1_2, e^1_1 ] ...----- T_n ------------| T_n-1,...T_2 |---------- T_1 ---------| A pull by consumer at this point would return e^1_1. Very likely that by the epoch prepare event *all* the branches have been already ready for prepare(). This pipelining parallelization method can work for the single "large" transaction in the binlog group and also could drag into the parallelizable input transactions from later groups if we additionally create master side dependency tracking (think of mysql logical timestamp method). Also notice that the optimisitic scheduler is orthogonal to this method so the two can be combined. Consistency concern ------------------- The epoch parallel execution (scheduling) must respect intra-transaction statement dependencies (e.g FIFO execution order of operations over the same object [record, table]). There is no inter-transaction dependencies, at least in theory. The notion of a leader, the two-phase-commits leader, remains roughly the same as in the existing conservative scheduler. This time more than initiating commits of the epoch branches it also takes care to advance the slave gtid execution status accordingly. It could be implemented as the very last statement of its own T' branch. It's clear that causal dependencies of an user write and a read (to find the effect of that write) can be tracked by WAIT_FOR_GTID() as currently. A plain two-phase commit does admit possibility to catch on slave combinations of the old (pre-epoch) and new version (post-epoch) or some objects of the same transaction if the user hits with non-locking SELECT in a "tiny" time window between commit():s of two branches like in the following example. Let t1,t2 some tables and the binlog group consists of just one transaction T_1 := { update(t1), update(t2) } Let on slave it's modified into two parallel ones: T'_1 := { update(t1) } || T'2 := { update(t2) } After both respond OK to 2pc prepare() and then the leader initiates commit() where could be 4 possible result sets to the contemporary SELECT SET @@session.tx_isolation='REPEATABLE-READ'; SELECT * from t1 as t_1, t2 as t_2; => ... While this is perhaps more than imperfect, I believe it may be relevant only to remote corner cases, but more importantly, we still can refine that doing some *adjustments* in the Engine studied briefly myself of what would be a sub-transaction in Innodb, "ideologically" endorsed and guided by Marko Makela. The idea is to hold the branch's locks even after the branch commits, and delegate their release to the leader. Recoverability -------------- When slave is configured to log in the binary log we must make sure to rollback any prepared epoch branches at the server recovery unless the last branch (of the leader) is also there, prepared. When it does not binlog the problem is that the modified branch's binlog images can't be just flushed one after another especially if the user specifies to log on the slave uniformly with the master that is to preserve the original sub-transaction structures and their boundaries. In such case there are several multiple option, here are some: - use relay-logged image of original sub-transactions (may be limited to the master and slave version combination though;) - reconstruct original sub-transaction's binlog images. That is binlog events "belonging" to an original sub-transaction will be cached cooperatively by workers that took share of the original sub-transaction execution. Optionally at least the caching could be set to allow out of order while the statements are parallelizable, like in the above T1(t1,t2) example. Otherwise the ordering will be preserved through implementing the shared cache as as a queue with an interface to insert an item into the middle. - exotics like to create the slave side "merge" GTID which would embed the original "commit". (The quotes here to allude to the Git's way). While it's a serious subject I am leaving out mariabackup for this moment. I believe it will be feasible to adjust its logics to account as prepared only such transactions that that are prepared in all their branches. More about pipeline style optimization -------------------------------------- As mentioned workers can be made self-serving to assign events for execution at once when become available. That must turn to think of producer-consumer model, potentially lock-free queues, dynamical # of workers to stay in balance with the receiver's pace etc. I think *eventually* we will need to assign the producer role to the IO (receiver) thread, and convert the current SQL (driver) thread into (relay-) logger (we still may have to have such logger for the semisync replication, the relay logger can be opted out otherwise). Here is a picture IO_thread.push() | +---- worker.pull() V V e^s_i -> [ e^s_i-1, ... e^s_1 }, T_s-1, ... ] ^ | SQL.pull() where on the right hand side the two consumers handle a transaction and when both of them have done with it (a worker has read its last event and the logger written it to the relay-log) the query element becomes garbage-collectable.

1 0

[Maria-discuss] Fwd: [MariaDB Announce] MariaDB 10.2.19 now available
by Reindl Harald 14 Nov '18

14 Nov '18

hell, a new file again breaking rpm-build /usr/lib/pkgconfig/libmariadb.pc adjusted mariadb.spec error: File not found: /home/builduser/rpmbuild/BUILDROOT/mariadb-10.2.19-0.fc28.20181114.rh.sandybridge.x86_64/usr/lib64/pkgconfig/libmariadb.pc WTF - why is that crap on a x86_64 system in /usr/lib/pkgconfig/libmariadb.pc instead /usr/lib64/pkgconfig/libmariadb.pc sane people use rpm macros %{_libdir}/pkgconfig/mariadb.pc %{_libdir}/pkgconfig/libmariadb.pc well, another trial and error to fix the build... -------- Weitergeleitete Nachricht -------- Betreff: [MariaDB Announce] MariaDB 10.2.19 now available Datum: Tue, 13 Nov 2018 20:20:34 +0200 Von: MariaDB Announce List <announce(a)mariadb.org> An: announce(a)mariadb.org The MariaDB Foundation is pleased to announce the availability of MariaDB 10.2.19, the latest stable release in the MariaDB 10.2 series.

1 0

[Maria-discuss] Character set & BLOBS on MySQL
by Andrew Wood 07 Nov '18

07 Nov '18

I am in the process of moving some tables from MySQL 5.1 (Linux) MariaDB 10.1 also on Linux. One of the tables stores binary data (either a PDF or JPEG) as a LONGBLOB. The code that reads & writes to the table is PHP and hasnt changed other than having the url of the database server altered. No errors are thrown on reading or writing but JPEGs are coming out corrupted, where as PDFs show fine. I had a hunch it was due to the charset/collation settings even though both tables are identical (the new one was created with the output from mysqldump on the old server). Having run SHOW SESSION VARIABLES LIKE 'character\_set\_%'; on both the old and new server there is a difference but I want to understand why this might be causing the problem before I alter anything. New server: MariaDB [(none)]> SHOW SESSION VARIABLES LIKE 'character\_set\_%'; +------------------------------------+-------------+ | Variable_name | Value | +------------------------------------+-------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | +------------------------------------+-------------+ 7 rows in set (0.00 sec) Old server: mysql> SHOW SESSION VARIABLES LIKE 'character\_set\_%'; +------------------------------------+----------+ | Variable_name | Value | +------------------------------------+----------+ | character_set_client | latin1 | | character_set_connection | latin1| | character_set_database | utf8 | | character_set_filesystem | binary| | character_set_results | latin1 | | character_set_server | latin1 | | character_set_system | utf8 | +------------------------------------+---------+ 7 rows in set (0.00 sec) Also is there a way for the client to specify the correct settings for reading/writing BLOBS on a per connection basis as it might not always be possible to alter the server? Thanks

2 1

[Maria-discuss] account locking?
by Felipe Gasper 05 Nov '18

05 Nov '18

Hello, Does the MariaDB development team intend to add account locking? I don’t see it in the 10.4 roadmap. MySQL added this in 5.7.6. Previously we’d accomplished this by reversing the password hash; for our servers that run MariaDB it would be great to be able to use the same facility. Thank you! -Felipe Gasper

2 1