Need help with MySQL 5.7 crashing on Django's Jenkins

[Tim Graham]

Sometimes the MySQL 5.7.13 builds on Ubuntu 16.04 are failing with "Lost connection to MySQL server during query" because the MySQL server restarts during the tests. I wonder if anyone has an idea about how to solve this. Looking through the MySQL error log, I think this is the root cause:

2016-08-01T23:02:56.636617Z 0 [ERROR] [FATAL] InnoDB: Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung.
2016-08-01 23:02:56 0x7f5fb75d8700  InnoDB: Assertion failure in thread 140049074980608 in file ut0ut.cc line 920
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
23:02:56 UTC - 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.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=536870912
read_buffer_size=131072
max_used_connections=4
max_threads=151
thread_count=4
connection_count=4
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 584285 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 = 0 thread_stack 0x200000
/usr/sbin/mysqld(my_print_stacktrace+0x3b)[0xe7bdab]
/usr/sbin/mysqld(handle_fatal_signal+0x489)[0x783759]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x113d0)[0x7f60131dc3d0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38)[0x7f6012596418]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a)[0x7f601259801a]
/usr/sbin/mysqld[0x759764]
/usr/sbin/mysqld(_ZN2ib5fatalD1Ev+0x145)[0x110c905]
/usr/sbin/mysqld(srv_error_monitor_thread+0xe2d)[0x10aa34d]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76fa)[0x7f60131d26fa]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f6012667b5d]
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.

[gilberto dos santos alves]

hi. please post your /etc/my.ini  (or your equiv. mysql ini config file).

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/3f787f25-2e3f-49b1-b6a3-7a3411e70a9b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

gilberto dos santos alves
+55(11)9-8646-5049
sao paulo - sp - brasil

[Tim Graham]

Here you are:

[client]
port        = 3306
socket        = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket        = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket        = /var/run/mysqld/mysqld.sock
port        = 3306
basedir        = /usr
datadir        = /mnt/mysql_tmpfs/mysql
tmpdir        = /tmp
lc-messages-dir    = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size         = 512M
max_allowed_packet      = 32M
thread_stack            = 2M
thread_cache_size       = 8

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_cache            = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit = 10M
query_cache_size        = 512M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id        = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
#binlog_do_db        = include_database_name
#binlog_ignore_db    = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!

# don't fsync the logs (for speed)
innodb_flush_log_at_trx_commit = 0

# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet    = 16M

[mysql]
#no-auto-rehash    # faster start of mysql but no tab completition

[isamchk]
key_buffer        = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/

[gilberto dos santos alves]

hi. IMO vars for django may uses this values inside [MYSQLD], cause max_connections default is 100

innodb_buffer_pool_instances=8
max_connections=255

you could verify your environment using console command

mysql -u your-user -p[your-password-whit-nospace]

show variables like '%connec%';

show variables like '%buffer%';

for example my env show:
mysql> show variables like '%buffer%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| bulk_insert_buffer_size      | 8388608  |
| innodb_buffer_pool_instances | 1        |
| innodb_buffer_pool_size      | 19922944 |
| innodb_change_buffering      | all      |
| innodb_log_buffer_size       | 1048576  |
| join_buffer_size             | 131072   |
| key_buffer_size              | 8388608  |
| myisam_sort_buffer_size      | 11534336 |
| net_buffer_length            | 16384    |
| preload_buffer_size          | 32768    |
| read_buffer_size             | 20480    |
| read_rnd_buffer_size         | 262144   |
| sort_buffer_size             | 262144   |
| sql_buffer_result            | OFF      |
+------------------------------+----------+
14 rows in set (0.00 sec)

mysql> show variables like '%connec%';
+--------------------------+------------------+
| Variable_name            | Value            |
+--------------------------+------------------+
| character_set_connection | cp850            |
| collation_connection     | cp850_general_ci |
| connect_timeout          | 10               |
| init_connect             |                  |
| max_connect_errors       | 10               |
| max_connections          | 100              |
| max_user_connections     | 0                |
+--------------------------+------------------+
7 rows in set (0.04 sec)

2016-08-01 21:05 GMT-03:00 Tim Graham <timog...@gmail.com>:

--

You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To post to this group, send email to django-d...@googlegroups.com.
Visit this group at https://groups.google.com/group/django-developers.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/3f787f25-2e3f-49b1-b6a3-7a3411e70a9b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Tim Graham]

I'm not sure exceeding max_connections is the issue. We have up to 8 executors on each machine and the tests aren't running in parallel, so I think we wouldn't have more than 8 connections unless running the tests can open more than 1 connection? Also, we don't expect 8 all executors to all be running MySQL builds at the same time.

In looking at innodb_buffer_pool_instances, I see that it only takes effect if innodb_buffer_pool_size is greater than 1GB (defaults to 128MB). Is the buffer pool stored on disk? We moved the datadir from /var/lib/mysql to /mnt/mysql_tempfs which is a 2GB tmpfs, so I'm wondering if we need to increase that tmpfs accordingly.

MySQL docs say, "For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1GB." We have 14GB of RAM on the Jenkins machine -- if we have to dedicate 8 GB to MySQL, that seems too much.

https://dev.mysql.com/doc/refman/5.6/en/innodb-multiple-buffer-pools.html

Here's the result of the queries you mentioned:

mysql> show variables like '%buffer%';

+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| bulk_insert_buffer_size             | 8388608        |
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
| innodb_change_buffer_max_size       | 25             |
| innodb_change_buffering             | all            |
| innodb_log_buffer_size              | 16777216       |
| innodb_sort_buffer_size             | 1048576        |
| join_buffer_size                    | 262144         |
| key_buffer_size                     | 536870912      |
| myisam_sort_buffer_size             | 8388608        |
| net_buffer_length                   | 16384          |
| preload_buffer_size                 | 32768          |
| read_buffer_size                    | 131072         |

| read_rnd_buffer_size                | 262144         |
| sort_buffer_size                    | 262144         |
| sql_buffer_result                   | OFF            |

+-------------------------------------+----------------+

mysql> show variables like '%connec%';

+-----------------------------------------------+-----------------+
| Variable_name                                 | Value           |
+-----------------------------------------------+-----------------+
| character_set_connection                      | utf8            |
| collation_connection                          | utf8_general_ci |
| connect_timeout                               | 10              |
| disconnect_on_expired_password                | ON              |
| init_connect                                  |                 |
| max_connect_errors                            | 100             |
| max_connections                               | 151             |
| max_user_connections                          | 0               |
| performance_schema_session_connect_attrs_size | 512             |
+-----------------------------------------------+-----------------+

[Erik Cederstrand]

I think this is better directed at a MySQL list. MySQL shouldn't crash, nothing I see indicates that this is a Django issue.

Of course, it's best if you can reproduce the error. Barring that, you'll get a much more useful stack trace if you build MySQL with debugging symbols. A quick look at the stack trace does indicate that your problem is in either libpthread or libc, which is unlikely - is it possible that you have hardware issues?

Erik

[George-Cristian Bîrzan]

Aside the obvious (hardware issues), which is I think unlikely if your datadir is on tmpfs, can you check the error logs for what queries were being executed when it crashes?

[gilberto dos santos alves]

hummm! thanks. i will continue search for more precise aswers and solutions. regards.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/05980ef8-bfc9-4ed7-b58c-fb5d9a85c81d%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Tim Graham]

I spun up a new machine and haven't seen this issue on it, so the problem may have been either hardware related or something that was fixed with a fresher install of Ubuntu 16.04.

[gilberto dos santos alves]

great!

To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscribe@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.

Visit this group at https://groups.google.com/group/django-developers.

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/eccb2ce4-d569-45dd-a397-5219b1091198%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Tim Graham]

The issue still happens. I may have found the cause. The MySQL data dir is stored on a tmpfs to speed things up and the disk usage there grows over time. It's possible that a 3GB tmpfs (it's 2GB on Ubuntu 14.04 machines) is too small and that if enough MySQL builds are running concurrently, it fills up. If MySQL doesn't have any room in the data directory, it crashes as described earlier in this thread. An unclean shutdown means innodb_index_stats.ibd isn't cleaned up properly. As that file (and possibly others) grow over time, the problem is more likely to occur. I've increased the tmpfs to 4GB -- we'll see if the problem reoccurs.

great!

To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/eccb2ce4-d569-45dd-a397-5219b1091198%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.