Backend is slower after converting tables from MyISAM to InnoDB

[Orkun 81]

Today I have changed the DB Engine from MyISAM to InnoDB with the search and replace technique which is stated here at the end https://processwire.com/blog/posts/using-innodb-with-processwire/.

But somehow some pages where I have a pagefield (which holds a lot of pages) loads in 1 Minute when I try to edit the page in the backend.
Before the InnoDB change it loaded in 6-7 seconds when I edited.

What else do I need to consider when converting to InnoDB?

As far as I know the "innodb_buffer_pool_size" is set to 11.75 GB.

KR
Orkun

[flydev 👊🏻 1,836]

Hi,

you should consider adjusting  innodb_buffer_pool_instances  and  innodb_buffer_pool_size   :  

 

 

How much memory is available on the server ?

 

[Orkun 81]

Hi @flydev 👊🏻

I had contact my host that they should set the innodb_buffer_pool_size to 70% of the available RAM
based on this article here https://mariadb.com/kb/en/converting-tables-from-myisam-to-innodb/.
I didn't heard anything from them yet.

Server RAM is 64GB

I also have done some code refactoring for the specific field which helped a little bit.

On the live site/live server I had this code inside a pagefield in the custom php code setting:

// Didn't use the has_parent selector here because it was buggy (not collecting all childrens)

$roots = wire('pages')->find("template=domain_root, id!=64098");
$pagearray = new PageArray();
foreach($roots as $root){
	$pagearray->import($root->find("template!=pikettdienst-overview|pikettdienst-category|pikett-date|calendar-item"));
}
$event->return = $pagearray;

The TTFB is 3.28s. PW Version is 2.7.3 and MyISAM Engine
DOMContentLoaded: 4.37 and Load: 4.40s

-----------------------

On the dev site/dev server I had refactored this code to this and placed it inside the /site/ready.php:

$wire->addHookAfter('InputfieldPage::getSelectablePages', function($event) {
    if($event->object->hasField == 'shareContent') {

            $rootIds = wire('pages')->findIDs("template=domain_root, id!=64098");
            $pagearray = new PageArray();
            if(wireCount($rootIds) > 0) {
                $rootIds = implode("|", $rootIds);
                $pagearray = wire('pages')->find("has_parent=$rootIds, template!=pikettdienst-overview|pikettdienst-category|pikett-date|calendar-item");
            }
            $event->return = $pagearray;

    }
});

The TTFB is 6.28s. PW Version is 3.0.165 and InnoDB Engine. Before refactoring the TTFB was 10s.
DOMContentLoaded: 11.85s and Load: 12.90s

When I deactive the AdminThemeUikit and use the classic one it loads faster.
DOMContentLoaded: 7.15s and Load: 7.20s but TTFB stays same.

Why is the AdminThemeUikit so much slower?

[flydev 👊🏻 1,836]

How much page contains the system ? 

You could try to spot the issue following these blog post : 

• https://www.chriscalender.com/using-show-processlist-and-mysqladmin-debug-output-in-conjunction-with-show-innodb-status/
• https://www.chriscalender.com/advanced-innodb-deadlock-troubleshooting-what-show-innodb-status-doesnt-tell-you-and-what-diagnostics-you-should-be-looking-at/ 

 

About AdminThemeUikit slowing down the system, i see no reason here.

[Orkun 81]

Hi @flydev 👊🏻

When I execute SHOW FULL PROCESSLIST on phpmyadmin while opening the corresponding page with the sharecontent field the following Query is executed for the shareContent pagefield.

SELECT pages.id,pages.parent_id,pages.templates_id  
FROM `pages`  
WHERE ((pages.parent_id=27200 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=27200 OR pages_id=27200)) OR (pages.parent_id=33509 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=33509 OR pages_id=33509)) OR (pages.parent_id=36093 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=36093 OR pages_id=36093)) OR (pages.parent_id=47499 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=47499 OR pages_id=47499)) OR (pages.parent_id=50748 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=50748 OR pages_id=50748)) OR (pages.parent_id=51240 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=51240 OR pages_id=51240)) OR (pages.parent_id=51268 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=51268 OR pages_id=51268)) OR (pages.parent_id=51433 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=51433 OR pages_id=51433)) OR (pages.parent_id=51466 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=51466 OR pages_id=51466)) OR (pages.parent_id=51644 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=51644 OR pages_id=51644)) OR (pages.parent_id=53775 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=53775 OR pages_id=53775)) OR (pages.parent_id=56496 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=56496 OR pages_id=56496)) OR (pages.parent_id=56824 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=56824 OR pages_id=56824)) OR (pages.parent_id=57297 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=57297 OR pages_id=57297)) OR (pages.parent_id=57316 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=57316 OR pages_id=57316)) OR (pages.parent_id=64013 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=64013 OR pages_id=64013)) OR (pages.parent_id=64034 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=64034 OR pages_id=64034)) OR (pages.parent_id=64051 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=64051 OR pages_id=64051)) OR (pages.parent_id=64067 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=64067 OR pages_id=64067)) OR (pages.parent_id=64162 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=64162 OR pages_id=64162)) OR (pages.parent_id=64167 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=64167 OR pages_id=64167)) OR (pages.parent_id=64172 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=64172 OR pages_id=64172)) OR (pages.parent_id=64698 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=64698 OR pages_id=64698)) OR (pages.parent_id=65617 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=65617 OR pages_id=65617)) OR (pages.parent_id=68889 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=68889 OR pages_id=68889)) OR (pages.parent_id=79707 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=79707 OR pages_id=79707)) OR (pages.parent_id=80789 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=80789 OR pages_id=80789)) OR (pages.parent_id=106083 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=106083 OR pages_id=106083)) OR (pages.parent_id=156188 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=156188 OR pages_id=156188)) OR (pages.parent_id=157310 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=157310 OR pages_id=157310)) OR (pages.parent_id=157321 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=157321 OR pages_id=157321)) OR (pages.parent_id=157331 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=157331 OR pages_id=157331)) OR (pages.parent_id=163387 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=163387 OR pages_id=163387)) OR (pages.parent_id=172378 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=172378 OR pages_id=172378)) OR (pages.parent_id=172767 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=172767 OR pages_id=172767)) OR (pages.parent_id=174477 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=174477 OR pages_id=174477)) OR (pages.parent_id=174505 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=174505 OR pages_id=174505)) OR (pages.parent_id=175606 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=175606 OR pages_id=175606)) OR (pages.parent_id=177162 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=177162 OR pages_id=177162)) OR (pages.parent_id=190556 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=190556 OR pages_id=190556)) OR (pages.parent_id=196026 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=196026 OR pages_id=196026)) OR (pages.parent_id=197635 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=197635 OR pages_id=197635)) OR (pages.parent_id=198626 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=198626 OR pages_id=198626)) OR (pages.parent_id=201550 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=201550 OR pages_id=201550)) OR (pages.parent_id=201959 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=201959 OR pages_id=201959)) OR (pages.parent_id=201981 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=201981 OR pages_id=201981)) OR (pages.parent_id=202026 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=202026 OR pages_id=202026)) OR (pages.parent_id=202060 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=202060 OR pages_id=202060)) OR (pages.parent_id=202082 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=202082 OR pages_id=202082)) OR (pages.parent_id=202127 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=202127 OR pages_id=202127)) OR (pages.parent_id=203241 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=203241 OR pages_id=203241)) OR (pages.parent_id=203680 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=203680 OR pages_id=203680)) OR (pages.parent_id=207998 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=207998 OR pages_id=207998)) OR (pages.parent_id=208009 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=208009 OR pages_id=208009)) OR (pages.parent_id=208585 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=208585 OR pages_id=208585)) OR (pages.parent_id=221432 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=221432 OR pages_id=221432)) OR (pages.parent_id=230765 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=230765 OR pages_id=230765)) OR (pages.parent_id=236703 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=236703 OR pages_id=236703)) OR (pages.parent_id=248817 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=248817 OR pages_id=248817)) OR (pages.parent_id=270368 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=270368 OR pages_id=270368)) OR (pages.parent_id=278456 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=278456 OR pages_id=278456)) OR (pages.parent_id=278852 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=278852 OR pages_id=278852)) OR (pages.parent_id=288591 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=288591 OR pages_id=288591)) OR (pages.parent_id=294584 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=294584 OR pages_id=294584)) OR (pages.parent_id=294616 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=294616 OR pages_id=294616)) OR (pages.parent_id=295254 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=295254 OR pages_id=295254)) OR (pages.parent_id=295285 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=295285 OR pages_id=295285)) OR (pages.parent_id=295318 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=295318 OR pages_id=295318)) OR (pages.parent_id=295351 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=295351 OR pages_id=295351)) OR (pages.parent_id=295383 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=295383 OR pages_id=295383)) OR (pages.parent_id=298000 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=298000 OR pages_id=298000)) OR (pages.parent_id=298029 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=298029 OR pages_id=298029)) OR (pages.parent_id=306300 OR pages.parent_id IN (SELECT pages_id FROM pages_parents WHERE parents_id=306300 OR pages_id=306300)))
AND (pages.templates_id!=285 AND pages.templates_id!=286 AND pages.templates_id!=287 AND pages.templates_id!=259)
AND (pages.status<1024)
GROUP BY pages.id

When I execute the SQL in the SQL Tab in phpmyadmin it has 0.5476s to show me the results.

I have also executed SHOW ENGINE INNODB STATUS in phpmyadmin while opening the corresponding page but I don't really understand the log output:

=====================================
2020-08-25 11:37:20 0x7f079867d700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 16026 srv_active, 0 srv_shutdown, 1200775 srv_idle
srv_master_thread log flush and writes: 1216801
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 104559
OS WAIT ARRAY INFO: signal count 114239
RW-shared spins 0, rounds 48936, OS waits 16344
RW-excl spins 0, rounds 3890013, OS waits 77782
RW-sx spins 8420, rounds 138928, OS waits 1539
Spin rounds per wait: 48936.00 RW-shared, 3890013.00 RW-excl, 16.50 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 1297101
Purge done for trx's n:o < 1297094 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421145577143032, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
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: 0
6188 OS file reads, 649778 OS file writes, 153914 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 3, seg size 5, 11 merges
merged operations:
insert 1, delete mark 237, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 9313853, node heap has 579 buffer(s)
Hash table size 9313853, node heap has 439 buffer(s)
Hash table size 9313853, node heap has 221 buffer(s)
Hash table size 9313853, node heap has 531 buffer(s)
Hash table size 9313853, node heap has 341 buffer(s)
Hash table size 9313853, node heap has 389 buffer(s)
Hash table size 9313853, node heap has 362 buffer(s)
Hash table size 9313853, node heap has 352 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 2655708824
Log flushed up to   2655708824
Pages flushed up to 2655708824
Last checkpoint at  2655708815
0 pending log flushes, 0 pending chkp writes
50740 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 34632105984
Dictionary memory allocated 6517869
Buffer pool size   2064384
Free buffers       1874861
Database pages     186309
Old database pages 68733
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 48, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6067, created 180242, written 516273
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 186309, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   1032192
Free buffers       937631
Database pages     92955
Old database pages 34293
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 8, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3161, created 89794, written 363661
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 92955, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   1032192
Free buffers       937230
Database pages     93354
Old database pages 34440
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 40, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 2906, created 90448, written 152612
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 93354, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Process ID=840, Main thread ID=139635091044096, state: sleeping
Number of rows inserted 60809038, updated 3818, deleted 81140, read 840337245
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 3740958.04 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

 

[flydev 👊🏻 1,836]

Hi @Orkun

Quote

=====================================
2020-08-25 11:37:20 0x7f079867d700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds

Your data are sampled for 1 second, it's unusable. Try to visit your culprit page, wait its finish loading then run the SQL command. Post your result once you get sampled data for 20-40 seconds.