Large Database setup Topic is solved

Be informed that this forum is not an official support forum for Joomla! 4.0. Any issues regarding Joomla! 4.0 must be reported at https://issues.joomla.org/.

Joomla! 4.0 is still in its Alpha stage. This forum should be used for sharing information about Joomla! 4.0.

Moderator: ooffick

Post Reply
christoforosKor
Joomla! Intern
Joomla! Intern
Posts: 62
Joined: Mon Aug 27, 2012 2:22 pm

Large Database setup

Post by christoforosKor » Fri Jul 12, 2019 6:07 am

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.
Last edited by toivo on Fri Jul 12, 2019 8:38 am, edited 1 time in total.
Reason: mod note: moved from 4.x Related

User avatar
john-doe
Joomla! Guru
Joomla! Guru
Posts: 909
Joined: Tue Apr 19, 2011 7:39 pm
Location: Colombia
Contact:

Re: Large Database setup

Post by john-doe » 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.
www.aldemar-hernandez.com - Custom templates and design services.

christoforosKor
Joomla! Intern
Joomla! Intern
Posts: 62
Joined: Mon Aug 27, 2012 2:22 pm

Re: Large Database setup

Post by christoforosKor » Sat Jul 13, 2019 6:22 am

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

ilputto
Joomla! Fledgling
Joomla! Fledgling
Posts: 4
Joined: Tue Aug 06, 2019 3:50 pm

Re: Large Database setup

Post by ilputto » Wed Aug 07, 2019 12:48 pm

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

User avatar
changlee
Joomla! Explorer
Joomla! Explorer
Posts: 400
Joined: Tue Nov 20, 2007 11:05 am
Location: Greece
Contact:

Re: Large Database setup

Post by changlee » 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?
If you do not programm your life, someone else will do it for you.
SMTP Newsletter APP: https://www.emailbat.com

User avatar
Slackervaara
Joomla! Ace
Joomla! Ace
Posts: 1003
Joined: Sat Aug 13, 2011 6:27 am

Re: Large Database setup

Post by Slackervaara » 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/

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 26187
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

Re: Large Database setup

Post by Per Yngve Berg » 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.

christoforosKor
Joomla! Intern
Joomla! Intern
Posts: 62
Joined: Mon Aug 27, 2012 2:22 pm

Re: Large Database setup

Post by christoforosKor » Sun Aug 11, 2019 6:46 am

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

christoforosKor
Joomla! Intern
Joomla! Intern
Posts: 62
Joined: Mon Aug 27, 2012 2:22 pm

Re: Large Database setup

Post by christoforosKor » Sun Aug 11, 2019 6:53 am

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

christoforosKor
Joomla! Intern
Joomla! Intern
Posts: 62
Joined: Mon Aug 27, 2012 2:22 pm

Re: Large Database setup

Post by christoforosKor » Sun Aug 11, 2019 6:54 am

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.

christoforosKor
Joomla! Intern
Joomla! Intern
Posts: 62
Joined: Mon Aug 27, 2012 2:22 pm

Re: Large Database setup

Post by christoforosKor » Sun Aug 11, 2019 6:56 am

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.

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 26187
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

Re: Large Database setup

Post by Per Yngve Berg » Sun Aug 11, 2019 9:19 am

Mod. Note: Relocated the topic back to Joomla 4 related from the 3.x forum.

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 26187
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

Re: Large Database setup

Post by Per Yngve Berg » 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.

christoforosKor
Joomla! Intern
Joomla! Intern
Posts: 62
Joined: Mon Aug 27, 2012 2:22 pm

Re: Large Database setup

Post by christoforosKor » Wed Aug 14, 2019 12:38 pm

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.

User avatar
Per Yngve Berg
Joomla! Master
Joomla! Master
Posts: 26187
Joined: Mon Oct 27, 2008 9:27 pm
Location: Akershus, Norway

Re: Large Database setup

Post by Per Yngve Berg » Fri Aug 16, 2019 8:21 am

File a bug report at Github.

christoforosKor
Joomla! Intern
Joomla! Intern
Posts: 62
Joined: Mon Aug 27, 2012 2:22 pm

Re: Large Database setup

Post by christoforosKor » Tue Aug 27, 2019 11:43 am

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.


Post Reply

Return to “Joomla! 4 Related”