Keyword

k2_items innodb

  • Павел Абакумов
  • Павел Абакумов's Avatar Topic Author
  • Offline
  • New Member
More
2 weeks 3 days ago #173457 by Павел Абакумов
Павел Абакумов created the topic: k2_items innodb
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.

  • Fotis
  • Fotis's Avatar
  • Offline
  • Administrator
  • JoomlaWorks Support Team
More
2 weeks 3 days ago #173458 by Fotis
Fotis replied the 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...

If you use & love K2, please take a moment to add your review and rate it
at the Joomla Extensions Directory: extensions.joomla.org/extension/k2/


IMPORTANT: Please search the forum before posting a question!

JoomlaWorks Support Team Member

Please Log in or Create an account to join the conversation.

  • Павел Абакумов
  • Павел Абакумов's Avatar Topic Author
  • Offline
  • New Member
More
1 week 5 days ago - 1 week 5 days ago #173497 by Павел Абакумов
Павел Абакумов replied the 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?
Last edit: 1 week 5 days ago by Павел Абакумов.

Please Log in or Create an account to join the conversation.

  • Fotis
  • Fotis's Avatar
  • Offline
  • Administrator
  • JoomlaWorks Support Team
More
1 week 5 days ago #173503 by Fotis
Fotis replied the 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:
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;

If you use & love K2, please take a moment to add your review and rate it
at the Joomla Extensions Directory: extensions.joomla.org/extension/k2/


IMPORTANT: Please search the forum before posting a question!

JoomlaWorks Support Team Member

Please Log in or Create an account to join the conversation.

  • Павел Абакумов
  • Павел Абакумов's Avatar Topic Author
  • Offline
  • New Member
More
1 week 2 days ago #173547 by Павел Абакумов
Павел Абакумов replied the 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.

Please Log in or Create an account to join the conversation.

  • Fotis
  • Fotis's Avatar
  • Offline
  • Administrator
  • JoomlaWorks Support Team
More
1 week 2 days ago #173548 by Fotis
Fotis replied the 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.

If you use & love K2, please take a moment to add your review and rate it
at the Joomla Extensions Directory: extensions.joomla.org/extension/k2/


IMPORTANT: Please search the forum before posting a question!

JoomlaWorks Support Team Member

Please Log in or Create an account to join the conversation.

  • Павел Абакумов
  • Павел Абакумов's Avatar Topic Author
  • Offline
  • New Member
More
1 week 2 days ago #173549 by Павел Абакумов
Павел Абакумов replied the 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

Please Log in or Create an account to join the conversation.

  • Fotis
  • Fotis's Avatar
  • Offline
  • Administrator
  • JoomlaWorks Support Team
More
1 week 1 day ago #173552 by Fotis
Fotis replied the topic: k2_items innodb
On a production server, InnoDB is much faster for practical reasons.

If you use & love K2, please take a moment to add your review and rate it
at the Joomla Extensions Directory: extensions.joomla.org/extension/k2/


IMPORTANT: Please search the forum before posting a question!

JoomlaWorks Support Team Member

Please Log in or Create an account to join the conversation.


Powered by Kunena Forum