2

Long story short: I have the buffer pool size = 40G. I have 64GB RAM on my server. But MySQL actually uses 73G of memory (53G physical and the rest swapped).

MySQL version: 8.0.22-0ubuntu0.20.04.2

htop

How is that possible? And how to deal with it? I'd like MySQL to not swap, I think this degrages it's performance significantly.

Just a note: I'm using MySQL in a non-standard way, there are no "users", there are just couple of scripts running which perform very heavy calculations, most of queries run several hours.

My mysqld.cnf:

sync_binlog=0
innodb_buffer_pool_size=40G
innodb_buffer_pool_instances=4
innodb_buffer_pool_chunk_size=10G
innodb_flush_log_at_trx_commit=0
innodb_log_file_size=10G
innodb_lock_wait_timeout=18000 #5 hours
innodb_deadlock_detect=0
innodb_online_alter_log_max_size=512M
max_heap_table_size=2G
tmp_table_size=2G
max_allowed_packet=1G

ft_min_word_len=1
ft_stopword_file=''
innodb_ft_min_token_size=1
innodb_ft_enable_stopword='OFF'
innodb_ft_result_cache_limit=4000000000

sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION,NO_UNSIGNED_SUBTRACTION'

binlog_expire_logs_seconds=115000 #1.5 days
wait_timeout=86400 #24 hours
event_scheduler='OFF'
transaction_isolation='READ-UNCOMMITTED'
range_optimizer_max_mem_size=0

SHOW ENGINE INNODB STATUS:

=====================================
2023-01-06 09:03:14 0x7f3b5454f700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 42 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 5817581 srv_active, 0 srv_shutdown, 1176515 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2481543293
OS WAIT ARRAY INFO: signal count 2225015700
RW-shared spins 2256999997, rounds 3189111343, OS waits 930003374
RW-excl spins 2051400118, rounds 48471954436, OS waits 1345519963
RW-sx spins 2536334, rounds 3679029, OS waits 15196
Spin rounds per wait: 1.41 RW-shared, 23.63 RW-excl, 1.45 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2022-12-21 14:40:46 0x7f45e015e700 Transaction:
TRANSACTION 7045075024, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
11 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2964075, OS thread handle 139938089002752, query id 350171612 10.0.0.4 root updating
delete from `users` where `id` = 12
Foreign key constraint fails for table `jokii`.`matching_actions`:
,
  CONSTRAINT `matching_actions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
Trying to delete in parent table, in index PRIMARY tuple:
DATA TUPLE: 11 fields;
 0: len 8; hex 000000000000000c; asc         ;;
 1: len 6; hex 0001a3eb5050; asc     PP;;
 2: len 7; hex 0200001a561e07; asc     V  ;;
 3: len 10; hex 4d617463686572363636; asc Matcher666;;
 4: SQL NULL;
 5: len 60; hex 2432792431302445326e484c77516a312f70742f504651786463676a2e303054734b35495a337a326a316b50576939312f756f72393167766842742e; asc $2y$10$E2nHLwQj1/pt/PFQxdcgj.00TsK5IZ3z2j1kPWi91/uor91gvhBt.;;
 6: len 60; hex 473877353072535a363456334755694e486663763156355570756244525a774d46417a66344b516c63654c774c7049453730444552414f4f65474a6a; asc G8w50rSZ64V3GUiNHfcv1V5UpubDRZwMFAzf4KQlceLwLpIE70DERAOOeGJj;;
 7: len 1; hex 02; asc  ;;
 8: len 1; hex 81; asc  ;;
 9: len 4; hex 63a31ad0; asc c   ;;
 10: len 4; hex 63a31ad0; asc c   ;;

But in child table `jokii`.`matching_actions`, in index matching_actions_user_id_foreign, there is a record:
PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 000000000000000c; asc         ;;
 1: len 8; hex 0000000000010f66; asc        f;;

------------
TRANSACTIONS
------------
Trx id counter 7136759163
Purge done for trx's n:o < 7136654789 undo n:o < 0 state: running but idle
History list length 45729
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421413211932576, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421413211931720, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421413211930008, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421413211934288, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421413211945416, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421413211941136, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421413211929152, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421413211928296, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 7136654785, ACTIVE 6456 sec starting index read
mysql tables in use 11, locked 1
11069 lock struct(s), heap size 1892560, 81312 row lock(s), undo log entries 160218
MySQL thread id 3096489, OS thread handle 139938092279552, query id 431225829 localhost root executing
INSERT IGNORE INTO rb_group_item (`type`, item_id, group_id)
                SELECT 'brand_sub_category2_01010', i.id, g.id
                FROM rb_items i
                JOIN rb_item_tag it2 ON it2.item_id = i.id JOIN rb_tags t2 ON it2.tag_id = t2.id AND t2.list = "Color" JOIN rb_item_tag it4 ON it4.item_id = i.id JOIN rb_tags t4 ON it4.tag_id = t4.id AND t4.list = "Material" JOIN rb_item_tag it5 ON it5.item_id = i.id JOIN rb_tags t5 ON it5.tag_id = t5.id AND t5.list = "Categories" JOIN rb_item_tag it6 ON it6.item_id = i.id JOIN rb_tags t6 ON it6.tag_id = t6.id AND t6.list IN("Accessory Type
Trx read view will not see trx with id >= 7136654785, sees < 7136654785
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 85252
22562121354 OS file reads, 1883479287 OS file writes, 354203769 OS fsyncs
1 pending preads, 0 pending pwrites
10835.12 reads/s, 16384 avg bytes/read, 21.00 writes/s, 9.48 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 15702, free list len 939386, seg size 955089, 152372692 merges
merged operations:
 insert 494772961, delete mark 1167112219, delete 295481119
discarded operations:
 insert 19938, delete mark 1, delete 1
Hash table size 10624987, node heap has 51992 buffer(s)
Hash table size 10624987, node heap has 1 buffer(s)
Hash table size 10624987, node heap has 1 buffer(s)
Hash table size 10624987, node heap has 11 buffer(s)
Hash table size 10624987, node heap has 15 buffer(s)
Hash table size 10624987, node heap has 1 buffer(s)
Hash table size 10624987, node heap has 2 buffer(s)
Hash table size 10624987, node heap has 13876 buffer(s)
607081.07 hash searches/s, 161045.98 non-hash searches/s
---
LOG
---
Log sequence number          143119571682666
Log buffer assigned up to    143119571682666
Log buffer completed up to   143119571682666
Log written up to            143119571682666
Log flushed up to            143119571682278
Added dirty pages up to      143119571682666
Pages flushed up to          143117988865165
Last checkpoint at           143117988865165
1177812297 log i/o's done, 6.95 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 43956305920
Dictionary memory allocated 1494388
Buffer pool size   2621440
Free buffers       28
Database pages     2555398
Old database pages 943378
Modified db pages  4050
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 184186084759, not young 878129585715
0.00 youngs/s, 0.01 non-youngs/s
Pages read 22562341496, created 30581445, written 547935573
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 995 / 1000, young-making rate 1 / 1000 not 109 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2555398, unzip_LRU len: 0
I/O sum[1072668]:cur[2308], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   655360
Free buffers       27
Database pages     638847
Old database pages 235844
Modified db pages  837
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 46030684170, not young 220509855311
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5453016045, created 7695472, written 136263915
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 995 / 1000, young-making rate 1 / 1000 not 120 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 638847, unzip_LRU len: 0
I/O sum[268167]:cur[577], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   655360
Free buffers       1
Database pages     638884
Old database pages 235857
Modified db pages  966
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 46060288135, not young 222302083098
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5787215064, created 7628619, written 134915374
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 994 / 1000, young-making rate 1 / 1000 not 126 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 638884, unzip_LRU len: 0
I/O sum[268167]:cur[577], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   655360
Free buffers       0
Database pages     638767
Old database pages 235814
Modified db pages  1065
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 45912035006, not young 218035144334
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5544539793, created 7640673, written 134899528
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 996 / 1000, young-making rate 0 / 1000 not 94 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 638767, unzip_LRU len: 0
I/O sum[268167]:cur[577], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   655360
Free buffers       0
Database pages     638900
Old database pages 235863
Modified db pages  1182
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 46183077448, not young 217282502972
0.00 youngs/s, 0.00 non-youngs/s
Pages read 5777570594, created 7616681, written 141856756
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 996 / 1000, young-making rate 1 / 1000 not 101 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 638900, unzip_LRU len: 0
I/O sum[268167]:cur[577], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
4 read views open inside InnoDB
Process ID=795, Main thread ID=139890893248256 , state=sleeping
Number of rows inserted 630429294, updated 2109116770, deleted 293525232, read 16421762044116
3.17 inserts/s, 0.07 updates/s, 0.00 deletes/s, 3431194.61 reads/s
Number of system rows inserted 197979, updated 1398, deleted 197627, read 592382
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

SHOW GLOBAL STATUS: https://pastebin.com/raw/HWRKtpgC

Improve this question
4
  • Can you include a 'show global status'?
    – danblack
    Jan 6 at 9:46
  • @danblack added
    – Stalinko
    Jan 6 at 10:01
  • Please provide SHOW CREATE TABLE matching_actions and the CREATEs for any tables that have a FK to that id.
    – Rick James
    Jan 6 at 16:43
  • sorry @RickJames this problem is absolutely not related to this particular table. This is a new table and the problem has been existing for months.
    – Stalinko
    Jan 6 at 16:47

1 Answer 1

Reset to default
0

These are too large. They are used for complex queries; if a lot of such are running simultaneously, a lot of RAM may be consumed. Swapping is terrible for performance. Lower these to 1% of RAM or less.

max_heap_table_size=2G
tmp_table_size=2G

A common mistake in transactions looks like this:

BEGIN;
SELECT FROM x WHERE ...; -- see below
...
Insert/Update/Delete INTO/FROM x
COMMIT;

The SELECT probably needs FOR UPDATE on the end.

If you use autocommit, please explain.

Improve this answer
7
  • What you mentioned is exactly my case: I have very few simulatneous queries (like 2 or 4 at a time) and they're very heavy, that's why I inscreased these variables. Do you think it's a mistake in my case?
    – Stalinko
    Jan 6 at 16:49
  • @Stalinko - Those variables rarely come into play, but when they do, they can chew up a lot of RAM. There is no "too low" -- the fallback is to use disk instead of RAM. This slows down some queries -- but that is better than the slowdown due to RAM.
    – Rick James
    Jan 6 at 17:13
  • @Stalinko - And I suggest you find the slowest queries (suggest using the SlowLog ) and try to optimize them in some way (if needed, ask for help in a new Question).
    – Rick James
    Jan 6 at 17:20
  • 1
    Hey @RickJames the problem is not in the queries. I know they are heavy, there are tens of such queries runnig on that server. My problem is that the server consumes much more RAM than it is configured to consume. I'd like to limit the consumption with proper settings. Query optimisation isn't the topic of this thread :)
    – Stalinko
    Jan 9 at 8:02
  • 1
    However, the two are related. Shrinking tmp_table_size saves RAM but sometimes costs in speed. However, reformulation may eliminate the need for the tmp_table -- win-win. (I'm thinking of making a GROUP BY match the ORDER BY)
    – Rick James
    Jan 9 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.