maria-discuss team mailing list archive

[Vicențiu Ciorbaru <vicentiu@xxxxxxxxxxx>]

Hi!

I have cc'ed the maria-developers mailing list as this is potentially a dev
issue and also the devs responsible for Windows and InnoDB development.
There might be some people on vacation right now, but this should get
looked into in a week or two at most.

Let's find a solution for your problem. :)

Vicentiu


On Sun, 10 Jun 2018, 19:11 TD, <tdcf@xxxxxxx> wrote:

> Hi,
>
> I have a Windows server that has been running MariaDB 10.1 successfully
> for over a year. The server remains mostly idle for long times with some
> read access, but occasionally there are transactions that add data (about
> 500k rows per commit). There can be up to 10 such transactions (one per
> database) at the same time and during those times the server is under quite
> some load (the code processing the data resides on the same server as the
> database). We're using an 8 core AMD Ryzen, 32 GB RAM and a Samsung SSD.
>
> Like I said, this worked fine with MariaDB 10.1 all the time.
>
> We're currently in the process of setting up a new server (specs similar
> to the ones above) and because MariaDB 10.3 has been released recently, I
> was curious and installed it. The database now constantly shuts down
> because of "[FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We
> intentionally crash the server because it appears to be hung." whenever
> there is one of the load situations described above. If I install MariaDB
> 10.1 on the new server, it runs as fine as the old one. The crashes also
> happen when I start with a clean database (no data migrated from 10.1
> databases).
>
> I'm not a huge MariaDB config pro, as it's only a tool to store the data,
> it just needs to run with reasonable performance and has always worked
> until now. As far as I know and can judge from comments in the file, the
> my.ini in use is (mostly?) based on my-innodb-heavy-4G.cnf, but coworkers
> might have made modifications. I've attached the contents below.
>
> I don't know how I can debug this issue further. Windows event viewer
> contains several log warnings with the text "InnoDB: A long semaphore
> wait:" but no further information why this wait happens. I couldn't find
> any other error logs. I guess it is some configuration error (I read about
> MariaDB switching from XtraDB to InnoDB, so I guess that the issue might be
> related).
>
> Migrating to 10.3 is not an absolute must, but it would be nice to be able
> to fix the problem.
>
> Any help would be greatly appreciated and if you need more information,
> I'll gladly try to provide it.
>
> Thanks,
> Tom.
>
> my.ini:
> [mysqld]
> max_connections = 100
> max_allowed_packet = 16M
> binlog_cache_size = 1M
> max_heap_table_size = 64M
> read_buffer_size = 2M
> read_rnd_buffer_size = 16M
> sort_buffer_size = 8M
> join_buffer_size = 8M
> thread_cache_size = 10
> query_cache_size = 64M
> query_cache_limit = 2M
> thread_stack = 240K
> tmp_table_size = 64M
> slow_query_log
> expire_logs_days = 14
> log-bin = mysql-bin
> character-set-server=utf8
> collation-server=utf8_general_ci
>
> #*** MyISAM Specific options
> key_buffer_size = 32M
> bulk_insert_buffer_size = 64M
> myisam_sort_buffer_size = 128M
> myisam_max_sort_file_size = 10G
> myisam_repair_threads = 1
> myisam_recover
>
> # *** INNODB Specific options ***
> #innodb_fatal_semaphore_wait_threshold = 6000 #tried that, didn't help
> #innodb_adaptive_hash_index = 0 #tried that (suggested in some stack
> overflow post, didn't help)
> innodb_buffer_pool_size = 2G
> innodb_data_file_path = ibdata1:10M:autoextend
> innodb_write_io_threads = 8
> innodb_read_io_threads = 8
> innodb_thread_concurrency = 16
> innodb_log_buffer_size = 8M
> innodb_log_file_size = 256M
> innodb_log_files_in_group = 3
> innodb_max_dirty_pages_pct = 10
> innodb_io_capacity = 2000
> innodb_lock_wait_timeout = 120
>
> [mysql]
> no-auto-rehash
>
> [myisamchk]
> key_buffer_size = 512M
> sort_buffer_size = 512M
> read_buffer = 8M
> write_buffer = 8M
>
> [mysqld_safe]
> open-files-limit = 8192
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
>