Lori Patrick added a comment - 2018-06-28 18:11 - edited my.cnf for the host: # # This started with MariaDB example config file for systems with 4GB of memory # running mostly MariaDB using InnoDB only tables and performing complex # queries with few connections. #   # # The following options will be read by MariaDB client applications. [client] port = 3306 socket = /srv/db_trn/run/mysql.sock     [mysqld]   # generic configuration options port = 3306 socket = /srv/db_trn/run/mysql.sock pid-file = /srv/db_trn/run/ch4pxdbsilo04.pid   datadir = /srv/dbdata_fast/mysql back_log = 50 max_connections = 1250 table_open_cache = 1000 table_definition_cache = 1000 skip-external-locking max_allowed_packet = 16M optimizer_switch = default #thread_cache_size = 16 #thread_concurrency = 8 query_cache_type = 0 query_cache_size = 0 query_cache_limit = 16M default_storage_engine = InnoDB #general_log = 1 #general_log_file = /srv/db_trn/log/mysql/ch4pxdbsilo04-general.log userstat = 1 plugin-load = auth_pam.so wait_timeout = 86400 innodb_strict_mode = 0 sql_mode = ''   # charset variables init-connect='SET NAMES latin1' character_set_client = latin1 character_set_server = latin1 skip-character-set-client-handshake   # logging variables slow_query_log = 1 long_query_time = 10 slow_query_log_file = /srv/db_trn/log/mysql/ch4pxdbsilo04-slow.log log-warnings = 2 log_error = /srv/db_trn/log/mysql/ch4pxdbsilo04-error.log sql-error-log-filename = /srv/db_trn/log/mysql/ch4pxdbsilo04_sql_errors.log   # binlog specific settings binlog_cache_size = 1M log_bin = 1 log-bin = /srv/db_trn/log/binlog/ch4pxdbsilo04-bin binlog_format = mixed expire_logs_days = 7 max_binlog_size = 1000M   # session variables read_buffer_size = 265k #sort_buffer_size = 4M read_rnd_buffer_size = 8M #join_buffer_size = 4M   # tmp variables tmp_table_size = 250M tmpdir = /srv/dbtmp   # Replication related settings server-id = 4 #master-host = master #master-user = replication #master-password = Gelber@Repl!master #master-port = 3306 read_only log_slave_updates = 1 slave-load-tmpdir = /srv/dbtmp #skip-slave-start log-slow-slave-statements     # MyISAM Specific options key_buffer_size = 5G max_heap_table_size = 1G bulk_insert_buffer_size = 32M myisam_sort_buffer_size = 64M myisam_max_sort_file_size = 25G myisam_repair_threads = 4 myisam_recover_option = backup   # INNODB Specific options innodb_file_per_table = on innodb_additional_mem_pool_size = 8M innodb_buffer_pool_size = 25G innodb_data_file_path = ibdata1:10M:autoextend innodb_data_home_dir = /srv/dbdata_fast/mysql #innodb_thread_concurrency = 8 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_group_home_dir = /srv/db_trn/log/mysql innodb_read_io_threads = 64 innodb_write_io_threads = 64 innodb_max_dirty_pages_pct = 80 innodb_flush_method=O_DIRECT innodb_stats_on_metadata = 0 innodb_print_all_deadlocks = 1 innodb_file_format = barracuda innodb_print_all_deadlocks = 1 innodb_open_files = 1000   [mysqldump] quick max_allowed_packet = 64M   [mysql] no-auto-rehash   [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M   [mysqlhotcopy] interactive-timeout   [safe_mysqld] log_error = /srv/db_trn/log/mysql/ch4pxdbsilo04-error.log socket = /srv/db_trn/run/mysql.sock pid-file = /srv/db_trn/run/chi0dbcore01.pid numa-interleave

Elena Stepanova added a comment - 2018-06-28 18:18 10.1.24 is over a year old, if you upgraded just recently, why did you choose this version? Is it something that CentOS provides? If you are installing from MariaDB repo, there have been 10 releases in 10.1 line since then, and lots of bugs fixed in this area. I recommend to upgrade to the recent release and see if it helps.

Lori Patrick added a comment - 2018-06-28 18:28 - edited I went with 10.1.24 because it was the most recent version I have tested in our environment. I am not in a position to just take the most recent version as it needs to go thru a test cycle. I had been upgrading to 10.2.8, but we ran into another bug and pulled back to 10.1.24 as it was tested in our environment. I am trying to get rid of all my 5x servers, and I was then going to move forward with 10.2.13 or above as it has a fix for the issues I tripped into in 10.2.8. Is this a known bug I have tripped into? Is there a setting or config fix I can do to not have to upgrade?

Lori Patrick added a comment - 2018-06-28 18:47 - edited stored procedure being called: DELIMITER $$   DROP PROCEDURE IF EXISTS `pnl_by_context`$$   CREATE DEFINER=`root`@`%` PROCEDURE `pnl_by_context`(ctxt VARCHAR (15)) BEGIN SET @ count = 0, @runningGross = 0, @runningCosts = 0, @runningNet = 0; SELECT * FROM (( SELECT r.trade_date, r.user_id, r.context, CAST (@runningNet := @runningNet + r.net_pnl AS DECIMAL (12,2)) AS running_net, r.net_pnl, CAST (@runningGross := @runningGross + r.gross_pnl AS DECIMAL (12,2)) AS running_gross, r.gross_pnl, CAST (@runningCosts := @runningCosts + r.costs AS DECIMAL (12,2)) AS running_costs, r.costs, CAST (r.round_turns AS SIGNED) AS round_turns, CAST ((@runningGross - @runningCosts) / (@ count := @ count + 1) AS DECIMAL (12,2)) AS avg_net, CAST (@runningCosts * 100 / @runningGross AS DECIMAL (6,2)) AS cost_ratio FROM (( SELECT trade_date, user_id, context, SUM (net_pnl) AS net_pnl, SUM (gross_pnl) AS gross_pnl, SUM (costs) AS costs, (( SUM (buys) + SUM (sells)) / 2) AS round_turns FROM daily WHERE context = ctxt GROUP BY trade_date ORDER BY trade_date ASC ) AS r)) AS a) ORDER BY trade_date DESC ; END $$   DELIMITER ;

Elena Stepanova added a comment - 2018-06-28 20:45 > I went with 10.1.24 because it was the most recent version I have tested in our environment It makes total sense, except that since it crashes , obviously it is not good enough for your environment. I quite understand the tendency to stay with a release, however old, which works for you, but this one doesn't, so there is no point sticking to it. I don't suggest switching to 10.2 right away, it's too radical and might be uncalled for if you do not need any particular features only available in 10.2; but it's more than reasonable to consider taking advantage of 10 bugfixing releases in the same 10.1 release line. While yes, regressions are always possible and do happen, on average bugfixing releases do much more good than harm, and 20th post-GA release is much more stable than first GA. So, please test one of the new ones in your environment (you might want to extend your tests of course, if they didn't catch the current problem). > Is this a known bug I have tripped into? As I said, there had been many bugs fixed in this area over the past year. You can search JIRA and see it yourself. It is also possible that it's one of still open known bugs, or an unknown one. If you can provide a complete test case with all structures, data and query ("run this SQL on a clean server => get the crash"), then I can check it against different versions to see where the problem exists and where it doesn't, and will let you know. Otherwise, without any indication that it is still reproducible on any reasonably recent version, we cannot launch a full-blown investigation on the off-chance that it's a still existing and unknown to us bug. > Is there a setting or config fix I can do to not have to upgrade? Possibly, but again, we cannot know it without a complete analysis, and we cannot do it for a year-old version without a good reason. If you provide the complete test case, we might be able to offer some advice, otherwise you can dig JIRA to see if you find anything similar and if there are any workarounds in there. If you need support for old versions, you might consider acquiring a support contract. If you already have one, please file a ticket through it.

Lori Patrick added a comment - 2018-06-28 21:03 Thanks Elena! I hear what you're saying. I have 30+ servers and this was one of the last two that would get me off 5x. It was my understanding that 10.1.24 was the last version that didn't require me to compile glibc for CentOS 6.5 and that is why I stuck with it as I still have some CentOS 6.5 hosts.. Part of my test cycle was to get to these hosts last as they are the highest risk - so a new version even 10.1.x would set me back quite a bit. Unfortunate I tripped into this problem as I was nearly done getting everything to 10 and I am pretty surprised I found such a serious bug this late in the upgrade cycle. I will pull together all the structures involved and add this to the ticket. Thanks!

Lori Patrick added a comment - 2018-08-31 18:01 I have not been able to reproduce it consistently. I have tried bringing down the size of innodb_buffer_pool to 100MB and open tables down to 100. Running several restores of mysql dumps files concurrently and looping thru the stored procedures that cause the crash at the same time to try to reproduce. It seems like it happens once something need to recycle (as in a dirty but unused cache that is now needed and needs to recycle), but I can't seem to configure the server to be able to reproduce at will. Until I can do that - testing a new version fixes the problem is a bit difficult. Any ideas what config I can run to be a minimal as possible and potentially fill what ever cache or buffer needs to recycle that trips me into this crash?

Lori Patrick added a comment - 2018-09-17 21:20 - edited Hi. I have been trying to reproduce this error to no avail. I have tried: looping thru stored procedures involved from both windows and linux 10,000 times -> no crash running above concurrently -> no crash brought down buffer pool and was able to get innodb to throw the following: InnoDB: Warning: difficult to find free blocks in InnoDB: the buffer pool (339 search iterations)! InnoDB: 0 failed attempts to flush a page! Consider InnoDB: increasing the buffer pool size. InnoDB: It is also possible that in your Unix version InnoDB: fsync is very slow, or completely frozen inside InnoDB: the OS kernel. Then upgrading to a newer version InnoDB: of your operating system may help. Look at the InnoDB: number of fsyncs in diagnostic info below. InnoDB: Pending flushes (fsync) log: 0; buffer pool: 1 InnoDB: 523993 OS file reads, 2186607 OS file writes, 266724 OS fsyncs InnoDB: Starting InnoDB Monitor to print further InnoDB: diagnostics to the standard output. but not crash have used dumps that came from 5.5.34 and restored and it wouldn't crash run tests against 10.1.24 replicated copy and wouldn't crash If I can't reproduce the crash, I can't confirm a newer version fixes and upgrade accordingly. Based on the error on crash - can someone provide any guidance as to what area of the server I need to adjust or stress to reproduce?

Lori Patrick added a comment - 2018-09-19 21:09 This is issue fixed by MDEV-13936 . It looks like I tripped into a known bug. I can reproduce my crash on demand at this point. This issue can be closed.

Elena Stepanova added a comment - 2018-09-20 13:08 Thanks for checking.