- discuss - lists.mariadb.org

[Maria-discuss] Benchmarking MariaDB/InnoDB using TATP - Writers slow down readers?
by Aarati K 07 Oct '19

07 Oct '19

I'm benchmarking MariaDB 10.4 with TATP benchmark. The InnoDB config file has the following parameters set: [mysqld] innodb_data_home_dir=/usr/local/mysql/data innodb_undo_directory=/usr/local/mysql/data innodb_undo_log_truncate=1 innodb_undo_tablespaces=3 innodb_flush_method=O_DIRECT innodb_page_size=65536 When I run just the read-mix of TATP (i.e, the read queries alone), I get a result as follows: I 2019-10-06 16:06:06 STATISTICS 90% response time (us), by transaction I 2019-10-06 16:06:06 STATISTICS GET_SUBSCRIBER_DATA: 744 I 2019-10-06 16:06:06 STATISTICS GET_NEW_DESTINATION: 1323 I 2019-10-06 16:06:06 STATISTICS GET_ACCESS_DATA: 738 I 2019-10-06 16:06:06 CONTROL MQTh for test run is 6772 Focusing on the first query, GET_SUBSCRIBER_DATA, it has a 90% response time of 744us. However, when I run the R90/W10 mix (i.e, 90% read requests, 10% write requests), the result is as follows: I 2019-10-07 01:23:27 STATISTICS 90% response time (us), by transaction I 2019-10-07 01:23:27 STATISTICS GET_SUBSCRIBER_DATA: 3771 I 2019-10-07 01:23:27 STATISTICS GET_NEW_DESTINATION: 4712 I 2019-10-07 01:23:27 STATISTICS GET_ACCESS_DATA: 3687 I 2019-10-07 01:23:27 STATISTICS UPDATE_SUBSCRIBER_DATA: 18801 I 2019-10-07 01:23:27 STATISTICS UPDATE_LOCATION: 18831 I 2019-10-07 01:23:27 STATISTICS INSERT_CALL_FORWARDING: 20017 I 2019-10-07 01:23:27 STATISTICS DELETE_CALL_FORWARDING: 19027 I 2019-10-07 01:23:27 CONTROL MQTh for test run is 1464 The response time of GET_SUBSCRIBER_DATA is 3771 us, which is much slower. Queries are generated probabilistically, and keys are chosen uniformly at random. However, what I don't understand is, how are the writers slowing down the readers by that much, especially when MariaDB/InnoDB has *row-level locking*, to avoid this issue of writers blocking readers. Are there any configuration parameters that need to be tweaked?

1 0

[Maria-discuss] MariaDB 10.4.8
by Thomas Plant 04 Oct '19

04 Oct '19

Hello, we have a client VM with MariaDB 10.4.8 which crashes always on the same query (it's in the log). Can someone help me to debug the problem, or is it already known problem? Server ist a CentOS 7 patched up to date and MariaDB 10.4.8 from the MariaDB repositories. Log contains the following: 191003 9:43:19 [ERROR] mysqld got signal 11 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 10.4.8-MariaDB key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=1 max_threads=302 thread_count=7 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 795622 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x55d12dc0ed28 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x7ff2fd21dcf0 thread_stack 0x49000 /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x55d12b70483e] /usr/sbin/mysqld(handle_fatal_signal+0x30f)[0x55d12b19ae0f] sigaction.c:0(__restore_rt)[0x7ff3153325f0] /usr/sbin/mysqld(handler_index_cond_check+0xbf)[0x55d12b1a56af] /usr/sbin/mysqld(+0x59ee80)[0x55d12ae94e80] /usr/sbin/mysqld(+0xb340cd)[0x55d12b42a0cd] /usr/sbin/mysqld(+0xa52c87)[0x55d12b348c87] /usr/sbin/mysqld(+0xa52edf)[0x55d12b348edf] /usr/sbin/mysqld(_ZN7handler11ha_rnd_nextEPh+0x10f)[0x55d12b19f8ff] /usr/sbin/mysqld(_Z13rr_sequentialP11READ_RECORD+0x1c)[0x55d12b2cde9c] /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x1a9)[0x55d12afc7dd9] /usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0xb9c)[0x55d12afea4cc] /usr/sbin/mysqld(_ZN4JOIN4execEv+0x33)[0x55d12afea723] /usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x186)[0x55d12afe8a26] /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x1d7)[0x55d12afe9597] /usr/sbin/mysqld(+0x58dd2a)[0x55d12ae83d2a] /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x4591)[0x55d12af90641] /usr/sbin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x4b6)[0x55d12afa8d06] /usr/sbin/mysqld(_ZN18Prepared_statement12execute_loopEP6StringbPhS2_+0x92)[0x55d12afa8ec2] /usr/sbin/mysqld(+0x6b3827)[0x55d12afa9827] /usr/sbin/mysqld(_Z19mysqld_stmt_executeP3THDPcj+0x27)[0x55d12afa98b7] /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1625)[0x55d12af97d85] /usr/sbin/mysqld(_Z10do_commandP3THD+0x11c)[0x55d12af99aac] /usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x1fa)[0x55d12b07718a] /usr/sbin/mysqld(handle_one_connection+0x3d)[0x55d12b07726d] pthread_create.c:0(start_thread)[0x7ff31532ae65] /lib64/libc.so.6(clone+0x6d)[0x7ff3136cb88d] Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (0x55d12dc24b38): select * from `users` where (`role` = ? or `role` = ?) and exists (select * from `email_templates` inner join `users_email` on `email_template s`.`id` = `users_email`.`email_template_id` where `users`.`id` = `users_email`.`user_id` and `email_template_id` = ?) and `users`.`deleted_at` is null Connection ID (thread ID): 9 Status: NOT_KILLED Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdow n=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_ merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_increme ntal=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition _pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. Writing a core file... Working directory at /var/lib/mysql Resource Limits: Limit Soft Limit Hard Limit Units Max cpu time unlimited unlimited seconds Max file size unlimited unlimited bytes Max data size unlimited unlimited bytes Max stack size 8388608 unlimited bytes Max core file size 0 unlimited bytes Max resident set unlimited unlimited bytes Max processes 15028 15028 processes Max open files 16364 16364 files Max locked memory 65536 65536 bytes Max address space unlimited unlimited bytes Max file locks unlimited unlimited locks Max pending signals 15028 15028 signals Max msgqueue size 819200 819200 bytes Max nice priority 0 0 Max realtime priority 0 0 Max realtime timeout unlimited unlimited us Core pattern: core Thanks for any hint. Kind Regards, Thomas

3 6

[Maria-discuss] fsync alternative
by Jure Sah 27 Sep '19

27 Sep '19

Hi, It would appear that on a typical webserver, the majority of disk i/o belongs to the MariaDB database process. It would appear it's mostly waiting on fsync(), which to my understanding is executed at least once per commit. I understand that this is extensively researched in the documentation and it has to do with the recovery of data in case of an unexpected server reboot. The thing is, this has many performance issues because it caps database performance at whatever the speed of the underlying physical storage is, even if the changed data fits into the available RAM. It also results in the circumstance where if anything is impacting performance of the storage system on the server, this will break MariaDB and cause the service to go offline (time out), even if there is sufficient RAM in the machine to continue operating as normal from page cache. In a typical scenario you have a website which is writing things like session and page cache data which expires within an hour and would be no great loss if missing from a backup. Especially if the volume of the missing data would be 30 seconds (kernel default for committing dirty pages to disk) and mind you this setting is configurable. Temporary tables cannot be used in this case, because they are deleted as soon as the session ends, which is too soon. Is there a different solution that could be used here? I've also noticed in the documentation that the options to control fsync usage are even more limited than in the MySQL server. They are also very strongly argued against. Considering the point that InnoDB is considered to be in an inconsistent state in any event, so long as the server is not cleanly stopped, is there really justification for such strong opposition here? Disabling fsync boosts performance of a typical MySQL server by something like a factor of 3. LP, Jure

6 7

[Maria-discuss] Query failure after minor upgrade 10.1.38 to 10.1.41
by Benoit Plessis 20 Sep '19

20 Sep '19

Hi Guys, We are experiencing a break in backward compatibility with the lastest 10.1 release which got publied on the debian/stretch release. Queries that worked before suddenly spit errors about unknown column. It seems to be related to the use of "inner join" without on/using clause and left join and 'extra table': SELECT * FROM t1, t2 INNER JOIN t3 LEFT JOIN t4 ON t4.id = t3.id_t5 WHERE t3.id = t2.id_t3 AND t1.id = X will give: ERROR 1054 (42S22): Unknown column 't1.id' in 'where clause' Adding parenthesis around the join: SELECT * FROM t1, ( t2 INNER JOIN t3 LEFT JOIN t4 ON t4.id = t3.id_t5 ) WHERE t3.id = t2.id_t3 AND t1.id = X or adding an "on" clause on the inner join seem to fix the issue SELECT * FROM t1, t2 INNER JOIN ON t3.id = t2.id_t3 t3 LEFT JOIN t4 ON t4.id = t3.id_t5 WHERE t1.id = X Weirdly enough it's impossible to have a Left join without on/using clause, while the parser let us write an inner join without. I cannot find a related note in the changelogs ..

2 1

[Maria-discuss] Εxponentially growing Innodb_row_lock_time on “master” node of MariaDB 10.3 Galera cluster
by George Diamantopoulos 20 Sep '19

20 Sep '19

Hello all, I originally posted this on serverfault, but the question hasn't gained much traction there yet. I've been trying to investigate issues with an application failing, and I have reason to believe that the culprit lies somewhere in the database backend. To this end, I started collecting metrics from the backend MariaDB Galera cluster (currently running on MariaDB version 10.3.16), hoping that the failures would reflect on the metrics collected. Indeed, about 12 hours before the application started failing spectacularly, the values reported by the 'Master' node (i.e. the node to which the application directs the writes) for Innodb_row_lock_time started growing at a rate never recorded before. I'm not sure the failures are linked to this metric, but it's the only trend I've been able to notice which correlates to the failures. Here's a link to a graph demonstrating this fact over the week preceding the last failure: Innodb_row_lock_time change per minute <https://i.stack.imgur.com/H4xwA.png> Note that the graph displays change, not the current value of the metric. MariaDB servers are polled every 90 seconds and datapoints in the graph refer to change per minute. The big drop near the end of the graph indicates the time when the MariaDB service was restarted on the 'Master' node. My question is how to further investigate this symptom and possibly identify the culprit queries or operation. I also log the output of InnoDB Monitor and slow queries in logfiles, but I haven't been able to find anything out of the ordinary during the period when wait_time was growing rapidly (although I'm no DB expert). Is there any other logging functionality I can enable to provide more information on this? And if InnoDB Monitor Output should provide the information needed, what should I be looking for exactly? What kind of operation could lead to rows being locked for so long, considering the sudden manifestation of the issue? Is there any way of knowing which rows were locked and whether it was a read or write lock? Lastly, is there any reason to think this could be attributed to a MariaDB bug instead of the applications misbehaving in any way? Thank you in advance, George

1 0

[Maria-discuss] Query failure after minor upgrade 10.1.38 to 10.1.41
by Benoit Plessis 20 Sep '19

20 Sep '19

Hi Guys, We are experiencing a break in backward compatibility with the lastest 10.1 release which got publied on the debian/stretch release. Queries that worked before suddenly spit errors about unknown column. It seems to be related to the use of "inner join" without on/using clause and left join and 'extra table': SELECT * FROM t1, t2 INNER JOIN t3 LEFT JOIN t4 ON t4.id = t3.id_t5 WHERE t3.id = t2.id_t3 AND t1.id = X will give: ERROR 1054 (42S22): Unknown column 't1.id' in 'where clause' Adding parenthesis around the join: SELECT * FROM t1, ( t2 INNER JOIN t3 LEFT JOIN t4 ON t4.id = t3.id_t5 ) WHERE t3.id = t2.id_t3 AND t1.id = X or adding an "on" clause on the inner join seem to fix the issue SELECT * FROM t1, t2 INNER JOIN ON t3.id = t2.id_t3 t3 LEFT JOIN t4 ON t4.id = t3.id_t5 WHERE t1.id = X Weirdly enough it's impossible to have a Left join without on/using clause, while the parser let us write an inner join without. I cannot find a related note in the changelogs .. -- Benoit Plessis <benoit(a)plessis.info> +33 6 77 42 78 32 4096R/44E6FA9A 486D 2804 3EAB 3D74 5D05 826D 997F 26D9 44E6 FA9A

1 0

[Maria-discuss] Slave corruption on myrocks update
by Jonas Krauss 19 Sep '19

19 Sep '19

Hi all, I am trying to upgrade MariaDB to a more recent version which includes https://jira.mariadb.org/browse/MDEV-19795 and thus a newer rocksdb version. We are running a mixed database of innodb and rocksdb tables. We are currently using 10.2.21-MariaDB-10.2.21+maria~stretch-log / mariadb.org binary distribution (on the master). The reported rocksdb version is 5.14.0, compile date Dec 30 2018. I have set up a new slave with the database hard-copied from the master's filesystem (with mariabackup) and MariaDB versions 10.4.7 / 10.3.17 and 10.2.26. As a first thing before replication starts I of course run mysql_upgrade, which goes through just fine. Once the replication catches up (row-based), it eventually hits some kind of wall and rocksdb detects a checksum mismatch during compaction, e.g.: 2019/09/19-16:15:31.512013 7f7ae33ff700 [WARN] [db_impl/db_impl_compaction_flush.cc:2726] Compaction error: Corruption: block checksum mismatch: expected 1973625368, got 2324967102 in /data/mysql/rocksdb/180583.sst offset 153437494 size 6567907 MariaDB then crashes with the dreaded exit code 6 (see log below). The database is unusable afterwards and does not start up anymore. The same procedure for setting up a slave and replicating works fine when staying at 10.2.21. I suspect there might be an incompatibility between rocksdb 5.14.0 and 6.2.0 (version used in the newer MariaDBs). However, I have read through the changelog at https://github.com/facebook/rocksdb/releases and have found no clear candidate for that. Any help or ideas are greatly appreciated. I can also provide more logs on request. Thanks Jonas -------------- *RocksDB options from my.cnf:* 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 = /data/mysql/rocksdb rocksdb_wal_dir = /var/mysql/rocksdb rocksdb_tmpdir = /var/mysql/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 = -1 rocksdb_max_background_jobs = 1 rocksdb_max_total_wal_size = 5G rocksdb_block_size = 64K rocksdb_block_cache_size = 16G rocksdb_table_cache_numshardbits = 6 rocksdb_new_table_reader_for_compaction_inputs = 1 rocksdb_compaction_readahead_size = 4M # 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 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=kLZ4Compression;bottommost_compression=kZlibCompression;compression_opts=-14:6: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 --------------------------- *MariaDB error log:* 190919 16:15:31 [ERROR] mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see https://mariadb.com/kb/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 10.2.27-MariaDB-1:10.2.27+maria~bionic-log key_buffer_size=134217728 read_buffer_size=131072 max_used_connections=20 max_threads=3002 thread_count=37 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 6727453 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x0 thread_stack 0x30000 *** buffer overflow detected ***: /usr/sbin/mysqld terminated terminate called after throwing an instance of 'std::system_error' what(): Resource deadlock avoided 2019-09-19 16:15:39 140198954641408 [ERROR] RocksDB: There was a corruption detected in RockDB files. Check error log emitted earlier for more details. 2019-09-19 16:15:39 140198954641408 [ERROR] RocksDB: The server will exit normally and stop restart attempts. Remove /data/mysql/rocksdb/ROCKSDB_CORRUPTED file from data directory and start mysqld manually. -------------------- *RocksDB error log (last 10 lines):* 2019/09/19-16:15:22.696889 7f7ae33ff700 EVENT_LOG_v1 {"time_micros": 1568902522696836, "cf_name": "default", "job": 41, "event": "table_file_creation", "file_number": 181274, "file_size": 180342221, "table_properties": {"data_size": 170893376, "index_size": 333943, "index_partitions": 0, "top_level_index_size": 0, "index_key_is_user_key": 0, "index_value_is_delta_encoded": 0, "filter_size": 9317061, "raw_key_size": 388460606, "raw_average_key_size": 52, "raw_value_size": 191015485, "raw_average_value_size": 25, "num_data_blocks": 5571, "num_entries": 7455469, "num_deletions": 585078, "num_merge_operands": 0, "num_range_deletions": 0, "format_version": 0, "fixed_key_len": 0, "filter_policy": "rocksdb.BuiltinBloomFilter", "column_family_name": "default", "column_family_id": 0, "comparator": "RocksDB_SE_v3.10", "merge_operator": "nullptr", "prefix_extractor_name": "nullptr", "property_collectors": "[Rdb_tbl_prop_coll_factory]", "compression": "LZ4", "compression_options": "window_bits=-14; level=6; strategy=0; max_dict_bytes=0; zstd_max_train_bytes=0; enabled=0; ", "creation_time": 1568902340, "oldest_key_time": 0, "file_creation_time": 1568902510, "__indexstats__": "[...37 records...]"}} 2019/09/19-16:15:25.772863 7f7ae33ff700 [compaction/compaction_job.cc:1332] [default] [JOB 41] Generated table #181275: 770219 keys, 129144877 bytes 2019/09/19-16:15:25.772927 7f7ae33ff700 EVENT_LOG_v1 {"time_micros": 1568902525772887, "cf_name": "default", "job": 41, "event": "table_file_creation", "file_number": 181275, "file_size": 129144877, "table_properties": {"data_size": 128134168, "index_size": 86084, "index_partitions": 0, "top_level_index_size": 0, "index_key_is_user_key": 0, "index_value_is_delta_encoded": 0, "filter_size": 962885, "raw_key_size": 15404380, "raw_average_key_size": 20, "raw_value_size": 167199905, "raw_average_value_size": 217, "num_data_blocks": 2752, "num_entries": 770219, "num_deletions": 0, "num_merge_operands": 0, "num_range_deletions": 0, "format_version": 0, "fixed_key_len": 0, "filter_policy": "rocksdb.BuiltinBloomFilter", "column_family_name": "default", "column_family_id": 0, "comparator": "RocksDB_SE_v3.10", "merge_operator": "nullptr", "prefix_extractor_name": "nullptr", "property_collectors": "[Rdb_tbl_prop_coll_factory]", "compression": "LZ4", "compression_options": "window_bits=-14; level=6; strategy=0; max_dict_bytes=0; zstd_max_train_bytes=0; enabled=0; ", "creation_time": 1568902340, "oldest_key_time": 0, "file_creation_time": 1568902522, "__indexstats__": "[...1 records...]"}} 2019/09/19-16:15:25.883030 7f7ae33ff700 [compaction/compaction_job.cc:1395] [default] [JOB 41] Compacted 1@3 + 7@4 files to L4 => 1889443497 bytes 2019/09/19-16:15:26.573155 7f7ae33ff700 (Original Log Time 2019/09/19-16:15:26.571946) [compaction/compaction_job.cc:763] [default] compacted to: base level 3 level multiplier 10.00 max bytes base 2684354560 files[0 0 0 9 23 139 1185] max score 1.00 (2 files need compaction), MB/sec: 19.1 rd, 18.6 wr, level 4, files in(1, 7) out(9) MB in(278.5, 1579.4) out(1801.9), read-write-amplify(13.1) write-amplify(6.5) OK, records in: 67833049, records dropped: 256224 output_compression: LZ4 2019/09/19-16:15:26.573173 7f7ae33ff700 (Original Log Time 2019/09/19-16:15:26.572001) EVENT_LOG_v1 {"time_micros": 1568902526571979, "job": 41, "event": "compaction_finished", "compaction_time_micros": 101734971, "compaction_time_cpu_micros": 48728676, "output_level": 4, "num_output_files": 9, "total_output_size": 1889443497, "num_input_records": 67741208, "num_output_records": 67484984, "num_subcompactions": 1, "output_compression": "LZ4", "num_single_delete_mismatches": 0, "num_single_delete_fallthrough": 0, "lsm_state": [0, 0, 0, 9, 23, 139, 1185]} 2019/09/19-16:15:26.573342 7f7ae33ff700 [compaction/compaction_job.cc:1645] [default] [JOB 42] Compacting 1@4 + 15@5 files to L5, score 0.00 2019/09/19-16:15:26.573366 7f7ae33ff700 [compaction/compaction_job.cc:1649] [default] Compaction start summary: Base version 44 Base level 4, inputs: [181272(167MB)], [180583(535MB) 180788(537MB) 180963(12MB) 180964(513MB) 181094(21MB) 181095(567MB) 181096(556MB) 181097(353MB) 181098(367MB) 181099(465MB) 181101(559MB) 181102(390MB) 181103(579MB) 181104(353MB) 181105(576MB)] 2019/09/19-16:15:26.573462 7f7ae33ff700 EVENT_LOG_v1 {"time_micros": 1568902526573441, "job": 42, "event": "compaction_started", "compaction_reason": "ManualCompaction", "files_L4": [181272], "files_L5": [180583, 180788, 180963, 180964, 181094, 181095, 181096, 181097, 181098, 181099, 181101, 181102, 181103, 181104, 181105], "score": 0, "input_data_size": 6878343156} 2019/09/19-16:15:31.512013 7f7ae33ff700 [WARN] [db_impl/db_impl_compaction_flush.cc:2726] Compaction error: Corruption: block checksum mismatch: expected 1973625368, got 2324967102 in /data/mysql/rocksdb/180583.sst offset 153437494 size 6567907

1 1

Re: [Maria-discuss] Rocks, toku and some performance considerations.
by pslawek83 17 Sep '19

17 Sep '19

Hi Everyone, just some quick follow up on this topic about MyRocks i started. It seems that there are some issues with debian9 and cfq scheduler when using rocks. Tested this on VM, not sure how virtualization is affecting this. Rocks is easily able to saturate 3 cpu cores with I/O wait and I/O throughput is very low in this config, even on fast SSD. This gets much better after switching to noop/deadline and best is to switch to Debian10 with mq-deadline. Then I/O wait drops to 0% and performance improves a lot.

3 2

Re: [Maria-discuss] Rocks, toku and some performance considerations.
by pslawek83 17 Sep '19

17 Sep '19

Hey Jocelyn, very hard to tell... i run some test traffic on both [ mq-deadline and none ] and they're both giving 0% IO wait, haven't tested on NVME. Dnia 17 września 2019 16:16 jocelyn fournier <jocelyn.fournier(a)gmail.com> napisał(a): Hi! Le 17 sept. 2019 à 16:07, pslawek83 <pslawek83(a)o2.pl> a écrit : Hi Everyone, just some quick follow up on this topic about MyRocks i started. It seems that there are some issues with debian9 and cfq scheduler when using rocks. Tested this on VM, not sure how virtualization is affecting this. Rocks is easily able to saturate 3 cpu cores with I/O wait and I/O throughput is very low in this config, even on fast SSD. This gets much better after switching to noop/deadline and best is to switch to Debian10 with mq-deadline. Then I/O wait drops to 0% and performance improves a lot. Just curious, is mq-deadline faster than none, even on fast SSD / NVME? Thanks! Jocelyn

1 0

[Maria-discuss] Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71
by Reindl Harald 16 Sep '19

16 Sep '19

as far as i remember i was told 10.3 is able to fix that after a full dcade now but it don't 2019-09-11 18:18:12 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2019-09-11 18:18:12 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2019-09-11 18:18:12 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2019-09-11 18:18:12 0 [ERROR] InnoDB: Cannot open datafile for read-only: './dbmail/#sql2-704-271.ibd' OS error: 71 2019-09-11 18:18:12 0 [ERROR] InnoDB: Operating system error number 2 in a file operation. 2019-09-11 18:18:12 0 [ERROR] InnoDB: The error means the system cannot find the path specified. 2019-09-11 18:18:12 0 [ERROR] InnoDB: If you are installing InnoDB, remember that you must create directories yourself, InnoDB does not create them. 2019-09-11 18:18:12 0 [ERROR] InnoDB: Could not find a valid tablespace file for ``dbmail`.`#sql2-704-271``. Please refer to https://mariadb.com/kb/en/innodb-data-dictionary-troubleshooting/ for how to resolve the issue. 2019-09-11 18:18:12 0 [Warning] InnoDB: Ignoring tablespace for `dbmail`.`#sql2-704-271` because it could not be opened. MariaDB [(none)]> SELECT * FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE '%#sql%'; +----------+----------------------+------+--------+-------+------------+---------------+------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | +----------+----------------------+------+--------+-------+------------+---------------+------------+ | 672 | dbmail/#sql2-704-271 | 41 | 5 | 545 | Compressed | 8192 | Single | +----------+----------------------+------+--------+-------+------------+---------------+------------+ 1 row in set (0.002 sec) MariaDB [(none)]> use dbmail ERROR 1051 (42S02): Unknown table 'dbmail.dbmail/#sql2-704-271' MariaDB [dbmail]> DROP TABLE `dbmail#sql2-704-271`; ERROR 1051 (42S02): Unknown table 'dbmail.dbmail#sql2-704-271' MariaDB [dbmail]>

3 7