How to Optimize Joomla with more 100,000 Content Items?

[vnnguyendung]

I love Joomla, and I have many websites with Joomla. Now, I have 2 two websites with more 100,000 Content Items.

My websites has been optimized with JCH, JotCache. But I have many members log in, and add more 100 content items every day.

When the member logs in, the page has no cache, and is very slow (Extremely long TTFB).

I analyze the process, and JAccess::preloadPermissions() had more 100.000 loops and called more 100.000 JDatabaseDriverMysqli->fetchObject.
This may be the cause of slow and overloaded websites.

I have read these:
- https://github.com/joomla/joomla-cms/issues/13542
- https://github.com/joomla/joomla-cms/pull/6728
- https://github.com/joomla/joomla-cms/pull/14268

How to Optimize my website?
Should I switch to Drupal or Typo3, October for website with more 100,000 Content Items, multiple users login?

Thank you.

122.922 rows in table #__content

sql_cout_table_content.png

123.763 rows in table #__assets

sql_cout_table_asset.png

debug in single article (with url ?tmpl=component)
Time: 3048.18 ms / 3907.22 ms Memory: 74.095 MB / 85.50 MB Application: After Access::preloadPermissions (com_content)

aricle_single.png

Add $count in file Access.php

code_assess_count.png

And var_dump result: 123.594 loop

vardump_article.png

[vnnguyendung]

JAccess::preloadPermissions() called 124.719 JDatabaseDriverMysqli->fetchObject.

profile_article.png

Debug in category blog

article_category.png

And var_dump result in category

vardump_category.png

[Per Yngve Berg]

Could the new JSON data type in Mysql 8.0 be a solution?

https://dev.mysql.com/doc/refman/8.0/en/json.html

[vnnguyendung]

Could the new JSON data type in Mysql 8.0 be a solution?

I continue test
------------------------
php 7.0.15 - MariaDB 10.1.21
Time: 3048.18 ms / 3907.22 ms Memory: 74.095 MB / 85.50 MB Application: After Access::preloadPermissions (com_content)
------------------------
php 7.2.13 - MariaDB 10.1.37
Time: 1928.11 ms / 2737.76 ms Memory: 74.091 MB / 83.02 MB Application: After Access::preloadPermissions (com_content)
-----------------------

system-information.png

php72_debug_article.png

[vnnguyendung]

I view on github at https://github.com/joomla/joomla-cms/issues/13542, and drewgg commented that he see https://github.com/joomla/joomla-cms/pull/12809 solves the issue above, merged to Joomla 4

[AMurray]

Joomla 4 is not yet stable - it's still Alpha. I wouldn't use it on production sites.

[Forsh]

What the heck. I was having trouble with my 7k of pages … dang, if you have some tips or best practices once you get your 100,000 page site going, post an update about what you did please. I would be super-curious to know your recipe and visit your site to see it running smooth with 100,000 indexed pages.

[vnnguyendung]

What the heck. I was having trouble with my 7k of pages … dang, if you have some tips or best practices once you get your 100,000 page site going, post an update about what you did please. I would be super-curious to know your recipe and visit your site to see it running smooth with 100,000 indexed pages.

I cache evething on site. I use cache on my site with extension:

First, I use JCH Optimize to Combines JavaScript & CSS, minifies and compresses JavaScript. JCH Optimize cache file css and js so make website more fast.

Next, I use JotCache with config: turn on Browser Caching, Edit page Invalidate, And option turn off cache with view="form".

Next, I use cloudflare on my domain: turn on "DNS and HTTP Proxy -CDN". Cloudflare CDN help Cached Requests, Stop Threats.

[Forsh]

What the heck. I was having trouble with my 7k of pages … dang, if you have some tips or best practices once you get your 100,000 page site going, post an update about what you did please. I would be super-curious to know your recipe and visit your site to see it running smooth with 100,000 indexed pages.

I cache evething on site. I use cache on my site with extension:

First, I use JCH Optimize to Combines JavaScript & CSS, minifies and compresses JavaScript. JCH Optimize cache file css and js so make website more fast.

Next, I use JotCache with config: turn on Browser Caching, Edit page Invalidate, And option turn off cache with view="form".

Next, I use cloudflare on my domain: turn on "DNS and HTTP Proxy -CDN". Cloudflare CDN help Cached Requests, Stop Threats.

Nice, appreciate the insight. I keep screwing myself up by not organizing things or something. If you cache so much that means your site structure never changes right? I jumped in feet first and didn't organize things. I never encountered this problem in the past because my sites were just a few pages with maybe a few hundred images that didn't really take much time to keep up with. I had goten up to around 20,000 pages and all of a sudden "one" little situation became hundreds or thousands of situations. That's why I was curious about how you manage such a large site...I never really expected the managing of a site would be so time consuming!

I'm thinking of just starting from scratch and doing everything better and I'm going to try to emulate what you're doing as best I can. Do you have any other suggestions on this? Specifically for someone starting from page 1 that wants to grow large with thousands of pages.

Few more questions hope you don't mind!

• Is your content indexed in the Joomla content or do you use a different content extension? I was looking at something like Easyblog.

• How do you maintain the database itself? Does your layers of caching eliminate the need to maintain and optimize a database?

I've been thinking of creating subdomains for each content category to distribute the database calls among many databases versus just a single database. I have zero experience with optimizing a database and maintaining one because my background is Graphic Design so visuals/asthetics are more my thing. Good ifea or waste of my time?

Thanks again for your insight into this! I've found it very difficult to find any recipie for doing this right and especially doing it right from the very beginning.