Page 1 of 1

Large Database setup

Posted: Fri Jul 12, 2019 6:07 am
by christoforosKor
Hello,
I have a setup like this
Debian 8 VM 32 GB Ram, 6 cpu
10.4.6-MariaDB
nginx

My database has almost 165.000 articles, 58.000 tags and it is extreme slow on the backend with timeouts rising. The front end is cached so it is ok.

Is there a recommended configuration for db?
Currently I have make these modifications but does make it much better.

[mariadb-10.1]

innodb_log_buffer_size=2G
innodb_buffer_pool_size=25G
innodb_file_per_table= ON
sql_bin_log=0;
max_connections=80


The strange thing is when i run top on my vm to see perfomrance and usage the memory used is never more than 3.5 MB

something is wrong with my configuration but I dont know what.

Any help is appreciated.

Re: Large Database setup

Posted: Fri Jul 12, 2019 3:21 pm
by john-doe
Greetings. What is the Size in MB of the SQL file?
Normally when i have to deal with Heavy Databases i do it using akeeba backup but i'm not sure of the magnitude of the size of DB on your site.

I had once a case where DB was 2.1GB of size but the Largest portion of it was Spam Users in the registration, something can be easily done in a php admin operation.

I manage a site which has around 3000 articles and caching it helps a bit.

I don't see anything wrong in the settings so far.

Re: Large Database setup

Posted: Sat Jul 13, 2019 6:22 am
by christoforosKor
john-doe wrote:
Fri Jul 12, 2019 3:21 pm
Greetings. What is the Size in MB of the SQL file?
Normally when i have to deal with Heavy Databases i do it using akeeba backup but i'm not sure of the magnitude of the size of DB on your site.

I had once a case where DB was 2.1GB of size but the Largest portion of it was Spam Users in the registration, something can be easily done in a php admin operation.

I manage a site which has around 3000 articles and caching it helps a bit.

I don't see anything wrong in the settings so far.
The size of database is almost 5G
The largest tables are
bwn_ucm_history | 3170.98 Mb
bwn_content | 1409.66 Mb
bwn_ucm_content | 348.28 Mb

My problem is not on the front end but at the backend. Listing articles takes too long. Especially when two or more authors submitting simultaneously.

By debuging the system I see that 71 queries! are running at the articles listing pages and 2 of them are taking too long. Below the first one takes 7sec and the second 2 sec

This one takes 7 sec

Code: Select all

SELECT DISTINCT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.created_by_alias, a.modified, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.introtext,l.title AS language_title, l.image AS language_image,uc.name AS editor,ag.title AS access_level,c.title AS category_title,ua.name AS author_name,`wa`.`stage_id` AS `stage_id`,`ws`.`title` AS `stage_title`,`ws`.`condition` AS `stage_condition`,`ws`.`workflow_id` AS `workflow_id`,COUNT(asso2.id)>1 as association
FROM bwn_content AS a
LEFT JOIN `bwn_languages` AS l ON l.lang_code = a.language
LEFT JOIN bwn_users AS uc ON uc.id=a.checked_out
LEFT JOIN bwn_viewlevels AS ag ON ag.id = a.access
LEFT JOIN bwn_categories AS c ON c.id = a.catid
LEFT JOIN bwn_users AS ua ON ua.id = a.created_by
INNER JOIN `bwn_workflow_associations` AS `wa` ON `wa`.`item_id` = `a`.`id`
INNER JOIN `bwn_workflow_stages` AS `ws` ON `ws`.`id` = `wa`.`stage_id`
LEFT JOIN bwn_associations AS asso ON asso.id = a.id AND asso.context='com_content.item'
LEFT JOIN bwn_associations AS asso2 ON asso2.key = asso.key
WHERE `ws`.`condition` IN (1, 0) AND `wa`.`extension`='com_content'
GROUP BY `a`.`id`,`a`.`title`,`a`.`alias`,`a`.`checked_out`,`a`.`checked_out_time`,`a`.`state`,`a`.`access`,`a`.`created`,`a`.`created_by`,`a`.`created_by_alias`,`a`.`modified`,`a`.`ordering`,`a`.`featured`,`a`.`language`,`a`.`hits`,`a`.`publish_up`,`a`.`publish_down`,`a`.`catid`,`l`.`title`,`l`.`image`,`uc`.`name`,`ag`.`title`,`c`.`title`,`ua`.`name`,`ws`.`title`,`ws`.`workflow_id`,`ws`.`condition`,`wa`.`stage_id`
ORDER BY a.id DESC

This one takes 2.5 sec

Code: Select all

SELECT DISTINCT a.id, a.title, a.alias, a.checked_out, a.checked_out_time, a.catid, a.state, a.access, a.created, a.created_by, a.created_by_alias, a.modified, a.ordering, a.featured, a.language, a.hits, a.publish_up, a.publish_down, a.introtext,l.title AS language_title, l.image AS language_image,uc.name AS editor,ag.title AS access_level,c.title AS category_title,ua.name AS author_name,`wa`.`stage_id` AS `stage_id`,`ws`.`title` AS `stage_title`,`ws`.`condition` AS `stage_condition`,`ws`.`workflow_id` AS `workflow_id`,COUNT(asso2.id)>1 as association
FROM bwn_content AS a
LEFT JOIN `bwn_languages` AS l ON l.lang_code = a.language
LEFT JOIN bwn_users AS uc ON uc.id=a.checked_out
LEFT JOIN bwn_viewlevels AS ag ON ag.id = a.access
LEFT JOIN bwn_categories AS c ON c.id = a.catid
LEFT JOIN bwn_users AS ua ON ua.id = a.created_by
INNER JOIN `bwn_workflow_associations` AS `wa` ON `wa`.`item_id` = `a`.`id`
INNER JOIN `bwn_workflow_stages` AS `ws` ON `ws`.`id` = `wa`.`stage_id`
LEFT JOIN bwn_associations AS asso ON asso.id = a.id AND asso.context='com_content.item'
LEFT JOIN bwn_associations AS asso2 ON asso2.key = asso.key
WHERE `ws`.`condition` IN (1, 0) AND `wa`.`extension`='com_content'
GROUP BY `a`.`id`,`a`.`title`,`a`.`alias`,`a`.`checked_out`,`a`.`checked_out_time`,`a`.`state`,`a`.`access`,`a`.`created`,`a`.`created_by`,`a`.`created_by_alias`,`a`.`modified`,`a`.`ordering`,`a`.`featured`,`a`.`language`,`a`.`hits`,`a`.`publish_up`,`a`.`publish_down`,`a`.`catid`,`l`.`title`,`l`.`image`,`uc`.`name`,`ag`.`title`,`c`.`title`,`ua`.`name`,`ws`.`title`,`ws`.`workflow_id`,`ws`.`condition`,`wa`.`stage_id`
ORDER BY a.id DESC LIMIT 20

Re: Large Database setup

Posted: Wed Aug 07, 2019 12:48 pm
by ilputto
Hello Cris,

Did you get timeout because sqlquery performance? If it true you have to check the performance of your I/o subsystem and also I would advice you to collect statistics against tables involved in the sqlquery .
The collection of statistics are important because could improve performance of sqlquery because a better execution plan.
Please refer to the following kb

https://mariadb.com/kb/en/library/engin ... -statement

Ilputto

Re: Large Database setup

Posted: Thu Aug 08, 2019 7:00 am
by changlee
Have you tried to reduce the listing in Joomla admin? Eg try to set listing at 20 items by default in Config. Then the load time is the same?

Re: Large Database setup

Posted: Sat Aug 10, 2019 5:10 pm
by Slackervaara
Big database tables can be partitioned into smaller units and thus things are speeded up:
http://acmeextension.com/mysql-table-partitioning/

Re: Large Database setup

Posted: Sat Aug 10, 2019 6:16 pm
by Per Yngve Berg
What is "Workflow"? Is that an extension you have installed?

There is no join to workflow tables in core Joomla.

The second query is limited to 20 articles.

Re: Large Database setup

Posted: Sun Aug 11, 2019 6:46 am
by christoforosKor
Slackervaara wrote:
Sat Aug 10, 2019 5:10 pm
Big database tables can be partitioned into smaller units and thus things are speeded up:
http://acmeextension.com/mysql-table-partitioning/
This seems very interesting. I wil check it out

Re: Large Database setup

Posted: Sun Aug 11, 2019 6:53 am
by christoforosKor
Finally it seems that the problem was on how Joomla calculates count of total rows to construct the paging.

Becouse the query for listing articles uses count() and group, the Joomla machine to find out how many articles exists, load all the articles on big array ( 166.000 rows) and then gets the count of the array.

I think I have to increase the memory used by nginx and/or php fpm to make this happen quicly, but I dont know how.

So if anyoone know what are these settings or what are the best settings for nginx and php7.3-fpm to deal with such a big array, or some reference to look for help, would be more than happy to know.

thanks Christoforow

Re: Large Database setup

Posted: Sun Aug 11, 2019 6:54 am
by christoforosKor
changlee wrote:
Thu Aug 08, 2019 7:00 am
Have you tried to reduce the listing in Joomla admin? Eg try to set listing at 20 items by default in Config. Then the load time is the same?
Yes it is reduced. I discovered that the problem is how Joomla caclulates the total rows for paging. See my own answer on the topic.

Re: Large Database setup

Posted: Sun Aug 11, 2019 6:56 am
by christoforosKor
Per Yngve Berg wrote:
Sat Aug 10, 2019 6:16 pm
What is "Workflow"? Is that an extension you have installed?

There is no join to workflow tables in core Joomla.

The second query is limited to 20 articles.
I am using Joomla 4.0 alpha 7. I think it is a new feature. We are preperaing our site for Joomla 4. Anyway the problem finally is on how Joomla calculates the total rows for paging. The problem exists on Joomla 3.x. See my own answer on the topic for details.

Re: Large Database setup

Posted: Sun Aug 11, 2019 9:19 am
by Per Yngve Berg
Mod. Note: Relocated the topic back to Joomla 4 related from the 3.x forum.

Re: Large Database setup

Posted: Sun Aug 11, 2019 9:42 am
by Per Yngve Berg
Mysql have a count function to count the number of records. It will return the total number of records even if a limit is set on the query. I don't see a reason to run the query twice. I see they use the count on the associations.

Re: Large Database setup

Posted: Wed Aug 14, 2019 12:38 pm
by christoforosKor
Per Yngve Berg wrote:
Sun Aug 11, 2019 9:42 am
Mysql have a count function to count the number of records. It will return the total number of records even if a limit is set on the query. I don't see a reason to run the query twice. I see they use the count on the associations.
Yes I know, but that how it is implemented at the backend for showing articles.

Re: Large Database setup

Posted: Fri Aug 16, 2019 8:21 am
by Per Yngve Berg
File a bug report at Github.

Re: Large Database setup

Posted: Tue Aug 27, 2019 11:43 am
by christoforosKor
Per Yngve Berg wrote:
Fri Aug 16, 2019 8:21 am
File a bug report at Github.
I have file it but it seems it is not a priority at this time.