Description:
Issue 1: When materializing performance_schema.data_locks when there are a lot of locks held on the server can lead to a server exit due to OOM. This is quite simple to reproduce using ro queries on a server.
Issue 2: The memory allocated here during materialization does not seem to be tracked in the performance schema memory instrumentation, making it difficult to troubleshoot where memory is being allocated on a live server.
Here I'm using a 10000000 row table to demonstrate on a r5.large ec2 instance (2 vcpu, 16GiB memory ), to repro you can adjust the number of rows/locks taken.
Steps:
# generate sysbench data and enable P_S instruments
# Based on my above specs I'm using 10 tables with 10000000 rows.
sysbench /usr/local/share/sysbench/oltp_write_only.lua --db-driver=mysql --mysql-user=msandbox --mysql-password=msandbox --mysql-db=test --mysql-socket=/tmp/mysql_sandbox8034.sock --mysql-port=8034 --threads=10 --rand-type=uniform --db-ps-mode=disable --tables=10 --forced-shutdown --table-size=10000000 --create_secondary=0 prepare
update performance_schema.setup_consumers set enabled=1;
update performance_schema.setup_instruments set enabled=1;
Open 3 sessions:
# Execute 1st and leave open
Session 1:
PAGER tail
begin;
select * from sbtest1 for update;
<do the same for sbtest2,3 etc for more rows>
# Run after session 1
Session 2:
SELECT * FROM performance_schema.data_locks WHERE OBJECT_SCHEMA = 'test' LIMIT 100;
# Observe system memory usage while session 2 is running
Session 3:
while sleep 5; do sandboxes/msb_8_0_34/use -e "select * from sys.memory_global_by_current_bytes limit 10";sudo dmesg | grep "Out of memory"; free -m;sleep 3;done
How to repeat:
Session 1: Run a query which generates a lot of row locks. Here I'm demonstrating using sysbench with select for update
PAGER tail
begin;
select * from sbtest1 for update;
<do the same for sbtest2,3 etc for more rows>
Session 2: Once session 1 is finished gathering the locks, run a query against performance_schema.data_locks in session 2 to get info on locks against the test schema. This will fail and cause server to OOM.
mysql [localhost:8034] {msandbox} (test) > SELECT * FROM performance_schema.data_locks WHERE OBJECT_SCHEMA = 'test' LIMIT 100;
ERROR 2013 (HY000): Lost connection to MySQL server during query
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql_sandbox8034.sock' (111)
ERROR:
Can't connect to the server
Session 3: Here, once session 2 starts executing, I'm checking OS level stats and performance schema. Note how free -m is reporting the drop in mem until we OOM, performance schema is not changing at all.
while sleep 5; do sandboxes/msb_8_0_34/use -e "select * from sys.memory_global_by_current_bytes limit 10";sudo dmesg | grep "Out of memory"; free -m;sleep 3;done
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| memory/innodb/buf_buf_pool | 80 | 10.22 GiB | 130.88 MiB | 80 | 10.22 GiB | 130.88 MiB |
| memory/performance_schema/events_statements_summary_by_digest | 1 | 40.28 MiB | 40.28 MiB | 1 | 40.28 MiB | 40.28 MiB |
| memory/innodb/ut0link_buf | 2 | 24.00 MiB | 12.00 MiB | 2 | 24.00 MiB | 12.00 MiB |
| memory/innodb/log_buffer_memory | 1 | 16.00 MiB | 16.00 MiB | 1 | 16.00 MiB | 16.00 MiB |
| memory/performance_schema/events_statements_history_long | 1 | 14.34 MiB | 14.34 MiB | 1 | 14.34 MiB | 14.34 MiB |
| memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 12.81 MiB | 51.04 KiB | 257 | 12.81 MiB | 51.04 KiB |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 11.55 MiB | 11.55 MiB | 1 | 11.55 MiB | 11.55 MiB |
| memory/performance_schema/events_statements_summary_by_digest.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
| memory/performance_schema/events_statements_history_long.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
| memory/performance_schema/events_statements_history_long.sql_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
total used free shared buff/cache available
Mem: 15779 5445 8019 0 2314 10074
Swap: 0 0 0
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| memory/innodb/buf_buf_pool | 80 | 10.22 GiB | 130.88 MiB | 80 | 10.22 GiB | 130.88 MiB |
| memory/performance_schema/events_statements_summary_by_digest | 1 | 40.28 MiB | 40.28 MiB | 1 | 40.28 MiB | 40.28 MiB |
| memory/innodb/ut0link_buf | 2 | 24.00 MiB | 12.00 MiB | 2 | 24.00 MiB | 12.00 MiB |
| memory/innodb/log_buffer_memory | 1 | 16.00 MiB | 16.00 MiB | 1 | 16.00 MiB | 16.00 MiB |
| memory/performance_schema/events_statements_history_long | 1 | 14.34 MiB | 14.34 MiB | 1 | 14.34 MiB | 14.34 MiB |
| memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 12.81 MiB | 51.04 KiB | 257 | 12.81 MiB | 51.04 KiB |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 11.55 MiB | 11.55 MiB | 1 | 11.55 MiB | 11.55 MiB |
| memory/performance_schema/events_statements_summary_by_digest.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
| memory/performance_schema/events_statements_history_long.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
| memory/performance_schema/events_statements_history_long.sql_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
total used free shared buff/cache available
Mem: 15779 7267 6198 0 2314 8253
Swap: 0 0 0
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc |
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
| memory/innodb/buf_buf_pool | 80 | 10.22 GiB | 130.88 MiB | 80 | 10.22 GiB | 130.88 MiB |
| memory/performance_schema/events_statements_summary_by_digest | 1 | 40.28 MiB | 40.28 MiB | 1 | 40.28 MiB | 40.28 MiB |
| memory/innodb/ut0link_buf | 2 | 24.00 MiB | 12.00 MiB | 2 | 24.00 MiB | 12.00 MiB |
| memory/innodb/log_buffer_memory | 1 | 16.00 MiB | 16.00 MiB | 1 | 16.00 MiB | 16.00 MiB |
| memory/performance_schema/events_statements_history_long | 1 | 14.34 MiB | 14.34 MiB | 1 | 14.34 MiB | 14.34 MiB |
| memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 12.81 MiB | 51.04 KiB | 257 | 12.81 MiB | 51.04 KiB |
| memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 11.55 MiB | 11.55 MiB | 1 | 11.55 MiB | 11.55 MiB |
| memory/performance_schema/events_statements_summary_by_digest.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
| memory/performance_schema/events_statements_history_long.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
| memory/performance_schema/events_statements_history_long.sql_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB |
+-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+
total used free shared buff/cache available
Mem: 15779 10261 3203 0 2314 5258
Swap: 0 0 0
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql_sandbox8034.sock' (111)
[ 954.783966] Out of memory: Killed process 4993 (mysqld) total-vm:34598780kB, anon-rss:15830440kB, file-rss:0kB, shmem-rss:0kB, UID:1000 pgtables:31688kB oom_score_adj:0
total used free shared buff/cache available
Mem: 15779 192 15521 0 66 15390
Swap: 0 0 0
Suggested fix:
restrict the amount of memory the data_locks table can use during materialization. The query should fail or memory be reused/spill when it passes a certain threshold - it should not use unlimited memory by default which can lead to crashing the server.
https://github.com/mysql/mysql-server/blob/8.0/storage/perfschema/pfs_data_lock.cc#L261
Description: Issue 1: When materializing performance_schema.data_locks when there are a lot of locks held on the server can lead to a server exit due to OOM. This is quite simple to reproduce using ro queries on a server. Issue 2: The memory allocated here during materialization does not seem to be tracked in the performance schema memory instrumentation, making it difficult to troubleshoot where memory is being allocated on a live server. Here I'm using a 10000000 row table to demonstrate on a r5.large ec2 instance (2 vcpu, 16GiB memory ), to repro you can adjust the number of rows/locks taken. Steps: # generate sysbench data and enable P_S instruments # Based on my above specs I'm using 10 tables with 10000000 rows. sysbench /usr/local/share/sysbench/oltp_write_only.lua --db-driver=mysql --mysql-user=msandbox --mysql-password=msandbox --mysql-db=test --mysql-socket=/tmp/mysql_sandbox8034.sock --mysql-port=8034 --threads=10 --rand-type=uniform --db-ps-mode=disable --tables=10 --forced-shutdown --table-size=10000000 --create_secondary=0 prepare update performance_schema.setup_consumers set enabled=1; update performance_schema.setup_instruments set enabled=1; Open 3 sessions: # Execute 1st and leave open Session 1: PAGER tail begin; select * from sbtest1 for update; <do the same for sbtest2,3 etc for more rows> # Run after session 1 Session 2: SELECT * FROM performance_schema.data_locks WHERE OBJECT_SCHEMA = 'test' LIMIT 100; # Observe system memory usage while session 2 is running Session 3: while sleep 5; do sandboxes/msb_8_0_34/use -e "select * from sys.memory_global_by_current_bytes limit 10";sudo dmesg | grep "Out of memory"; free -m;sleep 3;done How to repeat: Session 1: Run a query which generates a lot of row locks. Here I'm demonstrating using sysbench with select for update PAGER tail begin; select * from sbtest1 for update; <do the same for sbtest2,3 etc for more rows> Session 2: Once session 1 is finished gathering the locks, run a query against performance_schema.data_locks in session 2 to get info on locks against the test schema. This will fail and cause server to OOM. mysql [localhost:8034] {msandbox} (test) > SELECT * FROM performance_schema.data_locks WHERE OBJECT_SCHEMA = 'test' LIMIT 100; ERROR 2013 (HY000): Lost connection to MySQL server during query No connection. Trying to reconnect... ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql_sandbox8034.sock' (111) ERROR: Can't connect to the server Session 3: Here, once session 2 starts executing, I'm checking OS level stats and performance schema. Note how free -m is reporting the drop in mem until we OOM, performance schema is not changing at all. while sleep 5; do sandboxes/msb_8_0_34/use -e "select * from sys.memory_global_by_current_bytes limit 10";sudo dmesg | grep "Out of memory"; free -m;sleep 3;done +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/innodb/buf_buf_pool | 80 | 10.22 GiB | 130.88 MiB | 80 | 10.22 GiB | 130.88 MiB | | memory/performance_schema/events_statements_summary_by_digest | 1 | 40.28 MiB | 40.28 MiB | 1 | 40.28 MiB | 40.28 MiB | | memory/innodb/ut0link_buf | 2 | 24.00 MiB | 12.00 MiB | 2 | 24.00 MiB | 12.00 MiB | | memory/innodb/log_buffer_memory | 1 | 16.00 MiB | 16.00 MiB | 1 | 16.00 MiB | 16.00 MiB | | memory/performance_schema/events_statements_history_long | 1 | 14.34 MiB | 14.34 MiB | 1 | 14.34 MiB | 14.34 MiB | | memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 12.81 MiB | 51.04 KiB | 257 | 12.81 MiB | 51.04 KiB | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 11.55 MiB | 11.55 MiB | 1 | 11.55 MiB | 11.55 MiB | | memory/performance_schema/events_statements_summary_by_digest.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.sql_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ total used free shared buff/cache available Mem: 15779 5445 8019 0 2314 10074 Swap: 0 0 0 +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/innodb/buf_buf_pool | 80 | 10.22 GiB | 130.88 MiB | 80 | 10.22 GiB | 130.88 MiB | | memory/performance_schema/events_statements_summary_by_digest | 1 | 40.28 MiB | 40.28 MiB | 1 | 40.28 MiB | 40.28 MiB | | memory/innodb/ut0link_buf | 2 | 24.00 MiB | 12.00 MiB | 2 | 24.00 MiB | 12.00 MiB | | memory/innodb/log_buffer_memory | 1 | 16.00 MiB | 16.00 MiB | 1 | 16.00 MiB | 16.00 MiB | | memory/performance_schema/events_statements_history_long | 1 | 14.34 MiB | 14.34 MiB | 1 | 14.34 MiB | 14.34 MiB | | memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 12.81 MiB | 51.04 KiB | 257 | 12.81 MiB | 51.04 KiB | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 11.55 MiB | 11.55 MiB | 1 | 11.55 MiB | 11.55 MiB | | memory/performance_schema/events_statements_summary_by_digest.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.sql_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ total used free shared buff/cache available Mem: 15779 7267 6198 0 2314 8253 Swap: 0 0 0 +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | event_name | current_count | current_alloc | current_avg_alloc | high_count | high_alloc | high_avg_alloc | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ | memory/innodb/buf_buf_pool | 80 | 10.22 GiB | 130.88 MiB | 80 | 10.22 GiB | 130.88 MiB | | memory/performance_schema/events_statements_summary_by_digest | 1 | 40.28 MiB | 40.28 MiB | 1 | 40.28 MiB | 40.28 MiB | | memory/innodb/ut0link_buf | 2 | 24.00 MiB | 12.00 MiB | 2 | 24.00 MiB | 12.00 MiB | | memory/innodb/log_buffer_memory | 1 | 16.00 MiB | 16.00 MiB | 1 | 16.00 MiB | 16.00 MiB | | memory/performance_schema/events_statements_history_long | 1 | 14.34 MiB | 14.34 MiB | 1 | 14.34 MiB | 14.34 MiB | | memory/performance_schema/events_errors_summary_by_thread_by_error | 257 | 12.81 MiB | 51.04 KiB | 257 | 12.81 MiB | 51.04 KiB | | memory/performance_schema/events_statements_summary_by_thread_by_event_name | 1 | 11.55 MiB | 11.55 MiB | 1 | 11.55 MiB | 11.55 MiB | | memory/performance_schema/events_statements_summary_by_digest.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.digest_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | | memory/performance_schema/events_statements_history_long.sql_text | 1 | 9.77 MiB | 9.77 MiB | 1 | 9.77 MiB | 9.77 MiB | +-----------------------------------------------------------------------------+---------------+---------------+-------------------+------------+------------+----------------+ total used free shared buff/cache available Mem: 15779 10261 3203 0 2314 5258 Swap: 0 0 0 ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql_sandbox8034.sock' (111) [ 954.783966] Out of memory: Killed process 4993 (mysqld) total-vm:34598780kB, anon-rss:15830440kB, file-rss:0kB, shmem-rss:0kB, UID:1000 pgtables:31688kB oom_score_adj:0 total used free shared buff/cache available Mem: 15779 192 15521 0 66 15390 Swap: 0 0 0 Suggested fix: restrict the amount of memory the data_locks table can use during materialization. The query should fail or memory be reused/spill when it passes a certain threshold - it should not use unlimited memory by default which can lead to crashing the server. https://github.com/mysql/mysql-server/blob/8.0/storage/perfschema/pfs_data_lock.cc#L261