- Posts: 6
COMMUNITY FORUM
k2_items innodb
- Павел Абакумов
-
Topic Author
- Offline
- New Member
Less
More
4 years 5 months ago #173457
by Павел Абакумов
k2_items innodb was created by Павел Абакумов
New K2 installations are used for innodb materials, but I have myisam. This is reflected in the speed of the site. How can I migrate correctly? Is there any manual on migration?
Please Log in or Create an account to join the conversation.
- JoomlaWorks
-
- Offline
- Admin
Less
More
- Posts: 6211
4 years 5 months ago #173458
by JoomlaWorks
Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)
Replied by JoomlaWorks on topic k2_items innodb
MyISAM is the default storage engine set in your MySQL/MariaDB server or just the storage engine used in the K2 tables? Your question is not clear...
Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)
Please Log in or Create an account to join the conversation.
- Павел Абакумов
-
- Offline
- New Member
Less
More
- Posts: 6
4 years 5 months ago - 4 years 5 months ago #173497
by Павел Абакумов
Replied by Павел Абакумов on topic k2_items innodb
I have a k2 component base in myisam. The installation was done a long time ago. The database is about 60,000 materials. How to migrate base to innodb?
How can I update tables in the database?
My database server uses mariadb 10.1
www.joomlaworks.net/forum/k2-en/35853-convert-tables-from-myisam-2-innodb-4-performance
Can the community help with mysql migration request?
How can I update tables in the database?
My database server uses mariadb 10.1
www.joomlaworks.net/forum/k2-en/35853-convert-tables-from-myisam-2-innodb-4-performance
Can the community help with mysql migration request?
Last edit: 4 years 5 months ago by Павел Абакумов.
Please Log in or Create an account to join the conversation.
- JoomlaWorks
-
- Offline
- Admin
Less
More
- Posts: 6211
4 years 5 months ago #173503
by JoomlaWorks
Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)
Replied by JoomlaWorks on topic k2_items innodb
First off all, run a MyISAM to InnoDB converter script like this: pantheon.io/docs/myisam-to-innodb
Such a script will change any MyISAM table that CAN be switched to InnoDB.
2 tables in K2 won't be switched and for the 2 tables you need to run some SQL commands in phpMyAdmin or a similar DB administration tool. Copy/paste the lines below into some code/text editor and then replace XYZ with your actual DB prefix, then copy/paste the changed lines into phpMyAdmin's SQL tab after you switch to your K2 database. Depending on your server specs, this may take a while. And make sure you grab both a full and also a partial backup of your database (just the 2 tables what will be affected). If sh*t happens, you'll be able to re-import them.
The SQL to execute is this:
Such a script will change any MyISAM table that CAN be switched to InnoDB.
2 tables in K2 won't be switched and for the 2 tables you need to run some SQL commands in phpMyAdmin or a similar DB administration tool. Copy/paste the lines below into some code/text editor and then replace XYZ with your actual DB prefix, then copy/paste the changed lines into phpMyAdmin's SQL tab after you switch to your K2 database. Depending on your server specs, this may take a while. And make sure you grab both a full and also a partial backup of your database (just the 2 tables what will be affected). If sh*t happens, you'll be able to re-import them.
The SQL to execute is this:
ALTER TABLE `XYZ_k2_items` DROP INDEX `search`;
ALTER TABLE `XYZ_k2_items` DROP INDEX `title`;
ALTER TABLE `XYZ_k2_items` ADD INDEX `access` (`access`);
ALTER TABLE `XYZ_k2_items` ADD INDEX `published` (`published`);
ALTER TABLE `XYZ_k2_items` ADD INDEX `publish_down` (`publish_down`);
ALTER TABLE `XYZ_k2_items` ADD INDEX `publish_up` (`publish_up`);
ALTER TABLE `XYZ_k2_items` ADD INDEX `trash` (`trash`);
ALTER TABLE `XYZ_k2_items` ENGINE=InnoDB;
ALTER TABLE `XYZ_k2_tags` DROP INDEX `name`;
ALTER TABLE `XYZ_k2_tags` ENGINE=InnoDB;Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)
Please Log in or Create an account to join the conversation.
- Павел Абакумов
-
- Offline
- New Member
Less
More
- Posts: 6
4 years 5 months ago #173547
by Павел Абакумов
Replied by Павел Абакумов on topic k2_items innodb
Better not. On myisam even faster. Here there is a terrible runtime request. Is there anything you can do about this? The patch applied www.joomlaworks.net/forum/k2-en/47858-solved-k2-query-cache-problem#163417.
Request time: 1852.93 ms After the last request: 1.00 ms Request memory: 0.026 MB Memory before the request: 15.592 MB Rows selected: 1
SELECT COUNT(*)
FROM xyz_k2_items as i RIGHT JOIN xyz_k2_categories c
ON c.id = i.catid
WHERE i.published = 1
AND i.access IN(1,1,5)
AND i.trash = 0
AND c.published = 1
AND c.access IN(1,1,5)
AND c.trash = 0
AND c.language IN ('ru-RU', '*')
AND i.language IN ('ru-RU', '*')
AND ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2019-11-08 12:17:57' )
AND ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2019-11-08 12:17:57' )
AND c.id IN (1,2,4,36,37,104,105,106,108,109,111,126)
This is on a localhost using mariadb (innodb). The working server is more powerful.
Request time: 1852.93 ms After the last request: 1.00 ms Request memory: 0.026 MB Memory before the request: 15.592 MB Rows selected: 1
SELECT COUNT(*)
FROM xyz_k2_items as i RIGHT JOIN xyz_k2_categories c
ON c.id = i.catid
WHERE i.published = 1
AND i.access IN(1,1,5)
AND i.trash = 0
AND c.published = 1
AND c.access IN(1,1,5)
AND c.trash = 0
AND c.language IN ('ru-RU', '*')
AND i.language IN ('ru-RU', '*')
AND ( i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2019-11-08 12:17:57' )
AND ( i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2019-11-08 12:17:57' )
AND c.id IN (1,2,4,36,37,104,105,106,108,109,111,126)
This is on a localhost using mariadb (innodb). The working server is more powerful.
Please Log in or Create an account to join the conversation.
- JoomlaWorks
-
- Offline
- Admin
Less
More
- Posts: 6211
4 years 5 months ago #173548
by JoomlaWorks
Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)
Replied by JoomlaWorks on topic k2_items innodb
There have been performance improvements in the upcoming K2 v2.10. Grab the dev version from github.com/getk2/k2/archive/master.zip and install on top of 2.9.0, then test again.
I haven't tested the FORCE INDEX (idx) option to be honest.
I haven't tested the FORCE INDEX (idx) option to be honest.
Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)
Please Log in or Create an account to join the conversation.
- Павел Абакумов
-
- Offline
- New Member
Less
More
- Posts: 6
4 years 5 months ago #173549
by Павел Абакумов
Replied by Павел Абакумов on topic k2_items innodb
K2 v2.10.0 [Dev Build 20191106] The patch applied www.joomlaworks.net/forum/k2-en/47858-solved-k2-query-cache-problem#163417 . Not much faster. This is the best localhost result with the patch and myisam tables applied.
Request time: 1228.11 ms After the last request: 0.20 ms Request memory: 0.359 MB Memory before the request: 16.917 MB Rows selected: 30
SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias, c.params AS categoryparams
FROM xyz_k2_items as i FORCE INDEX (item) RIGHT JOIN xyz_k2_categories AS c
ON c.id = i.catid
WHERE i.published = 1
AND i.access IN(1,5)
AND i.trash = 0
AND c.published = 1
AND c.access IN(1,5)
AND c.trash = 0
AND c.language IN('ru-RU', '*')
AND i.language IN('ru-RU', '*')
AND (i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2019-11-08 19:38:06')
AND (i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2019-11-08 19:38:06')
AND c.id IN(1,2,4,36,37,104,105,106,108,109,111,126)
ORDER BY i.publish_up DESC
LIMIT 0, 30
Database at innodb
Request time: 1996.04 ms After the last request: 0.20 ms Request memory: 0.359 MB Memory before the request: 16.918 MB Rows selected: 30
SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias, c.params AS categoryparams
FROM xyz_k2_items as i USE INDEX (item) RIGHT JOIN xyz_k2_categories AS c
ON c.id = i.catid
WHERE i.published = 1
AND i.access IN(1,5)
AND i.trash = 0
AND c.published = 1
AND c.access IN(1,5)
AND c.trash = 0
AND c.language IN('ru-RU', '*')
AND i.language IN('ru-RU', '*')
AND (i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2019-11-08 19:38:47')
AND (i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2019-11-08 19:38:47')
AND c.id IN(1,2,4,36,37,104,105,106,108,109,111,126)
ORDER BY i.publish_up DESC
LIMIT 0, 30
Request time: 1228.11 ms After the last request: 0.20 ms Request memory: 0.359 MB Memory before the request: 16.917 MB Rows selected: 30
SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias, c.params AS categoryparams
FROM xyz_k2_items as i FORCE INDEX (item) RIGHT JOIN xyz_k2_categories AS c
ON c.id = i.catid
WHERE i.published = 1
AND i.access IN(1,5)
AND i.trash = 0
AND c.published = 1
AND c.access IN(1,5)
AND c.trash = 0
AND c.language IN('ru-RU', '*')
AND i.language IN('ru-RU', '*')
AND (i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2019-11-08 19:38:06')
AND (i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2019-11-08 19:38:06')
AND c.id IN(1,2,4,36,37,104,105,106,108,109,111,126)
ORDER BY i.publish_up DESC
LIMIT 0, 30
Database at innodb
Request time: 1996.04 ms After the last request: 0.20 ms Request memory: 0.359 MB Memory before the request: 16.918 MB Rows selected: 30
SELECT SQL_CALC_FOUND_ROWS i.*, c.name AS categoryname, c.id AS categoryid, c.alias AS categoryalias, c.params AS categoryparams
FROM xyz_k2_items as i USE INDEX (item) RIGHT JOIN xyz_k2_categories AS c
ON c.id = i.catid
WHERE i.published = 1
AND i.access IN(1,5)
AND i.trash = 0
AND c.published = 1
AND c.access IN(1,5)
AND c.trash = 0
AND c.language IN('ru-RU', '*')
AND i.language IN('ru-RU', '*')
AND (i.publish_up = '0000-00-00 00:00:00' OR i.publish_up <= '2019-11-08 19:38:47')
AND (i.publish_down = '0000-00-00 00:00:00' OR i.publish_down >= '2019-11-08 19:38:47')
AND c.id IN(1,2,4,36,37,104,105,106,108,109,111,126)
ORDER BY i.publish_up DESC
LIMIT 0, 30
Please Log in or Create an account to join the conversation.
- JoomlaWorks
-
- Offline
- Admin
Less
More
- Posts: 6211
4 years 5 months ago #173552
by JoomlaWorks
Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)
Replied by JoomlaWorks on topic k2_items innodb
On a production server, InnoDB is much faster for practical reasons.
Fotis / JoomlaWorks Support Team
---
Please search the forum before posting a new topic :)
Please Log in or Create an account to join the conversation.