- discuss - lists.mariadb.org

[Maria-discuss] MariaDB crashes because of "long semaphore wait" after migrating from 10.1 to 10.3
by TD 22 Jun '18

22 Jun '18

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

4 4

[Maria-discuss] MariaDB Developers Unconference next week
by Otto Kekäläinen 22 Jun '18

22 Jun '18

Hello everybody! If you plan to attend the MariaDB Developers Unconference and related activities next week,* make sure you have signed up <https://www.meetup.com/MariaDB-Developers/events/249024232/> to the meetup* . We will soon be posting important information to attendees and only people who have signed up will receive it. Info: - www.meetup.com/MariaDB-Developers/events/249024232/ <https://www.meetup.com/MariaDB-Developers/events/249024232/> - mariadb.org/2018-2-developers-unconference-in-finland/ - mariadb.org/travel-tips-to-tampere-finland/

1 0

[Maria-discuss] MariaDB files wiped out after reboot
by Allan Mendoza 20 Jun '18

20 Jun '18

Hi, Good Day! I have a weird experience on one of our mysql servers. After a reboot all the database files have been wiped out. May I know if someone had expereienced the same, My OS is Centos 6.9 and MariaDB version is 5.5.35 Attaching is the error log that we have salvaged after the reboot. Thank you lanz_s99zx :)

1 0

Re: [Maria-discuss] Chewing Through Swap - Swappiness = 0
by Michael Caplan 15 Jun '18

15 Jun '18

Just found this interesting article on the "The MySQL 'swap insanity' problem": https://blog.jcole.us/2010/09/28/mysql-swap-insanity-and-the-numa-architect… Has anyone else tried the mentioned solutions? - Forcing interleaved allocation with numactl --interleave=all. - Flushing Linux’s buffer caches just before mysqld startup with sysctl -q -w vm.drop_caches=3. - Forcing the OS to allocate InnoDB’s buffer pool immediately upon startup, using MAP_POPULATE where supported (Linux 2.6.23+), and falling back to memset otherwise. One of the solutions seems dependent on the Twitter fork that was absorbed by the now removed XtraDB. Thanks, Mike On 2018-06-15 08:24 AM, Michael Caplan wrote: > Hi, > > I'm trying to figure out why my recently put into production MariaDB > is so swap hungry. > > I'm running 10.2.14, with roughly 300GB data (1000K +/- tables). 95% > tables are innodb. I have 64GB RAM, with INNODB buffer pool size set > to 50GB (full my.cnf below). The OS is Ubuntu 16.04.4. This is a > dedicated MariaDB server. > > I have swappiness set to 0. > > I started with available swap set to just shy of 1GB. When swap got > to 85% used, I bumped swap to 3GB. A day later, swap again was 85% > used, and I bumped it to 5GB. A day later, swap again was 85% used, > so I bumped it to 15GB. 2 days later 50% of the available swap was > used. I restarted the DB moments ago, freeing up all 15GB but 128MB > of swap: > > total used free shared buff/cache > available > Mem: 62G 20G 4.7G 266M 37G 41G > Swap: 14G 128M 14G > > My understanding is that the OS should never allocate swap when > swappiness is 0, so that has me confused. > > > I've had similar issues with MySQL 5.6 (what I upgraded from) dipping > into swap, but that instance had swappiness set to 1, and never > consumed more than 5GB of swap. > > > Questions: > > 1) How much of this is an OS issue vs MariaDB issue? > > 2) What MariaDB config vars should I be looking at to fix this issue? > > 3) What linux config vars should I be looking at to fix this issue? > > > Thanks, > > Mike > > > > [mysqld] > > # GENERAL # > user = mysql > default-storage-engine = InnoDB > character-set-server = utf8 > collation_server = utf8_general_ci > performance_schema = 0 > max_allowed_packet = 16777216 > sql_mode = > "NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" > > # MyISAM # > key-buffer-size = 32M > myisam-recover = FORCE,BACKUP > > # SAFETY # > max-allowed-packet = 16777216 > max-connect-errors = 1000000 > skip-name-resolve > sysdate-is-now = 1 > innodb = FORCE > local_infile = 0 > secure_auth = 1 > safe_user_create = 1 > skip_symbolic_links = 1 > wait_timeout = 28800 > > # DATA STORAGE # > datadir = /var/lib/mysql/ > > # BINARY LOGGING # > log-bin = /var/lib/mysql/mysql-bin > binlog_format = MIXED > server-id = 3 > expire-logs-days = 7 > sync-binlog = 1 > > # REPLICATION # > read-only = 1 > skip-slave-start = 1 > log-slave-updates = 1 > relay-log = /var/lib/mysql/relay-bin > slave-net-timeout = 60 > sync-master-info = 1 > sync-relay-log = 1 > sync-relay-log-info = 1 > > # CACHES AND LIMITS # > tmp-table-size = 32M > max-heap-table-size = 32M > query-cache-type = 0 > query-cache-size = 0 > max-connections = 500 > thread-cache-size = 50 > open-files-limit = 65535 > table-definition-cache = 4096 > table-open-cache = 10240 > > # INNODB # > innodb-flush-method = O_DIRECT > innodb-log-files-in-group = 2 > innodb-log-file-size = 512M > innodb-flush-log-at-trx-commit = 2 > innodb-file-per-table = 1 > innodb-buffer-pool-size = 50G > >

1 0

[Maria-discuss] Timezones Table
by Kurniadi Asrigo 12 Jun '18

12 Jun '18

Hi, I am following the instruction to update the timezone table from MySQL: https://mariadb.com/kb/en/library/time-zones/ And the file is downloaded from MySQL page. I got MariaDB from XAMPP, running on Window10. I downloaded the "timezone_2018e_posix_sql.zip<https://downloads.mysql.com/general/timezone_2018e_posix_sql.zip> - POSIX standard" https://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html Once downloaded, I extracted the package into a directory. From the instruction, I executed the command: .\mysql_tzinfo_to_sql.exe C:\dev\xampp_download\timezone\timezone_posix.sql | .\mysql.exe -D mysql -u root -p Now, the timezone Table is all empty. I am wondering if I am missing a step or an option. Thanks, Ti.

2 1

[Maria-discuss] MariaDB Developers Unconference in a few weeks!
by Otto Kekäläinen 09 Jun '18

09 Jun '18

Hello everybody! The next developers unconference will be in a few weeks: https://mariadb.org/2018-2-developers-unconference-in-finland/ There are still open slots so we encourage you to sign up now. https://www.meetup.com/MariaDB-Developers/events/249024232/ We have plenty of break-out rooms this time and there is space, so we also encourage you to suggest sessions/discussions you would like to have to ian(a)mariadb.org. Around mid-summer is a great time to visit Finland. The nights that don't get dark are worth experiencing! Getting to the venue in Tampere., Finlnd is also very easy. https://mariadb.org/travel-tips-to-tampere-finland/#why-visit Hope to see you there! - Otto

1 0

[Maria-discuss] Upgrade paths through more major versions
by Michal Schorm 06 Jun '18

06 Jun '18

Hello, MariaDB changed its release cycle to 1 major version / year. However the upgrades remained supported only from one major version to the very next. I see a raise of users (of RH products - RHEL and RHSCL mainly) that would like to jump several versions. They had an application build on, let's say, 5.5 (RHEL7 default) and would like to get the features from 10.2 (available in RH software collections - we provide plenty of versions this way). So far, they have to upgrade one by one 5.5->10.0->10.1->10.2; and solve the conflicts at each stage. Lately, we discontinued support fo 10.0 in RHSCL, which means, the upgrade path metioned above won't be as easy anymore. Also mysqldump can't be used for upgrade to 10.3, which is IMHO frequently used. For other RH products, there is an ongoing discussions, whether support all major releases or if to skip some. Which implies the questions about the upgrades through versions we wouldn't pack. However skipping several versions is IMHO quite normal for products with slow release cycle, like RHEL, so I'd expect other companies providing similar systems / products with slow release cycle to face the same problems. Are there any plans to extend the upgrade paths through more versions? Have you encountered simmilar needs of the users? -- Michal Schorm Associate Software Engineer Core Services - Databases Team Red Hat

4 6

[Maria-discuss] Why MyISAM as default engine for system tables ?
by Julien Muchembled 06 Jun '18

06 Jun '18

Hello, A few weeks ago, we got the following fatal error when starting mariadb: [Note] Starting crash recovery... [Note] Crash recovery finished. [ERROR] mysqld: Table './mysql/db' is marked as crashed and should be repaired [Warning] Checking table: './mysql/db' [ERROR] mysql.db: 1 client is using or hasn't closed the table properly tch value count at row 1 [ERROR] Aborting Unfortunately, what led to this is unclear. We only rebooted the machine. Maybe a hw raid issue. We did a copy at that time. I tried recently if it could be repaired but I don't have the a fatal error anymore. We also don't know why system tables were touched during the reboot. Anyway, given the description of Aria ("Crash-safe tables with MyISAM heritage"), I understand that MyISAM is not crash-safe. Then, why keep using MyISAM as default engine for system tables, when MariaDB has Aria or InnoDB ? Not sure a crash-safe engine would have helped in the above case (there's no miracle for data corruption). But we'd like at least that all our mariadb setups are crash-safe (e.g. power cuts) and we use InnoDB partly for that. And I don't know if a InnoDB DB could be always recoverable if the 'mysql' DB is lost but we don't want to do manually (and learn how to) what the machine can do automatically. Julien

6 8

[Maria-discuss] Python 3 Handler Socket
by Roberto Spadim 28 May '18

28 May '18

Hi guys! Any link to a working handler socket python module? pyhs is old and working only with python2, we need to update the C lib or use plain python with python3 (i did some changes, don't know if there's a running job on this) -- Roberto Spadim

2 4

[Maria-discuss] MariaDB Compile Size
by Tom Jay 21 May '18

21 May '18

Hello, Has anyone got any idea how to reduce the MariaDB install size, when compiling from source? I've had multiple attempts, all with different options such as 'WITHOUT_EXAMPLE_STORAGE_ENGINE=1' and 'FEATURE_SET:STRING=large', for example, but with no effect. The installation size is usually around 2.1 GB, whereas the official Arch package, which also compiles from source, is about 231.78 MB. The official Arch build script is here [https://git.archlinux.org/svntogit/packages.git/tree/trunk/PKGBUILD?h=packa…] and the only difference I can notice is that they delete a lot of stuff afterwards. Is there any reference for stuff that can be deleted after MariaDB has been installed? Thanks.

4 5