Details
-
Task
-
Status: Confirmed (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
Description
I load backup to mariadb and break it. In processlist still present one killed thread:
{code}
show processlist;
---------------------------------------------------------------------------------------------------------
| Id | User | Host | db | Command | Time | State | Info | Progress |
---------------------------------------------------------------------------------------------------------
| 85 | loader | 10.10.16.216:43971 | phonebook_storage | Killed | 1845 | Unlocking tables | NULL | 0.000 |
| 184 | root | localhost | NULL | Query | 0 | init | show processlist | 0.000 |
---------------------------------------------------------------------------------------------------------
{code}
In same time I run strace for mysqld and see many calls to madvise:
{code}
- strace -ff -p 33080
...
[pid 60412] madvise(0x7f36cb638000, 331776, MADV_DONTNEED) = 0
[pid 60412] madvise(0x7f36cb441000, 135168, MADV_DONTNEED) = 0
[pid 60412] madvise(0x7f36cb638000, 335872, MADV_DONTNEED) = 0
[pid 60412] madvise(0x7f36cb441000, 135168, MADV_DONTNEED) = 0
[pid 60412] madvise(0x7f36cb638000, 335872, MADV_DONTNEED) = 0
[pid 60412] madvise(0x7f36cb441000, 135168, MADV_DONTNEED) = 0
[pid 60412] madvise(0x7f36cb638000, 335872, MADV_DONTNEED) = 0
{code}
Table which been loading:
{code}
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `contact` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`id_phonebook` int(10) unsigned NOT NULL DEFAULT '0',
`id_user` int(10) unsigned NOT NULL DEFAULT '0',
`firstname` varchar(255) NOT NULL DEFAULT '',
`lastname` varchar(255) NOT NULL DEFAULT '',
`local_key` varchar(255) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `id_phonebook` (`id_phonebook`) USING BTREE,
KEY `id_user` (`id_user`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=133028725 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=2;
/*!40101 SET character_set_client = @saved_cs_client */;
{code}
Attachments
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
MDEV-32050 UNDO logs still growing for write-intensive workloads
-
- Closed
-
-
-
- Open
-
-
-
- Confirmed
-
Activity
I retitled the bug, because while I consider my implementation of ROW_FORMAT=COMPRESSED in InnoDB obsolete by now, something definitely needs to be done about the frequent memory heap operations in InnoDB.
In MDEV-32050, some copying of undo log records during transaction rollback, a special case of commit (MDEV-15250) and purge was removed. We will still copy undo log records from the buffer pool to heap during MVCC reads.
For the record, I just quickly checked the following test:
./mtr --rr --skip-ssl --mysqld=--innodb-stats-persistent=0 innodb.insert_into_empty,4k
|
A significant portion of heap memory allocation operations during this test, which is inserting data into empty tables is originating from the internal SQL interpreter in InnoDB, which is used for updating persistent statistics. The above command disables that, as well as the use of SSL, which is another notable source of malloc() calls. There is quite a bit of heap memory allocation going on also outside storage engines, for example related to acquiring metadata locks.
Furthermore, while working on MDEV-29445 I recently learned that MADV_DONTNEED actually requests the memory to be unmapped immediately. For workloads that frequently free and allocate memory, it would seem to make sense to defer the release of memory to the operating system kernel, or to request the operating system to defer the modification of the MMU mappings (MADV_FREE).
chernomorets, the madvise() system calls are apparently how jemalloc lets the operating system reclaim the memory in free(). I assume that malloc() would allocate private anonymous pages with mmap(). Maybe other memory allocation libraries use munmap() or something else; I have not checked lately.
That InnoDB uses an excessive amount of heap memory allocations is a known problem. Fixing it would require massive changes to the code, and it is not feasible to do in a GA release.
Furthermore, I think that the usefulness of ROW_FORMAT=COMPRESSED is limited. When I designed and implemented it in 2005, data file fragmentation was considered to be an issue, and also FALLOC_FL_PUNCH_HOLE did not exist. With flash-based storage, PAGE_COMPRESSED=YES ought to work work better.