Hall of Fame

[TigerClaw]

Hello,

I'm having problem with too may db connection to the website and I was reading the dtberio posts about query cache optimisations.

Meanwhile you work on that it would be very good to have a "Frozen" cache option where all the pages and queries are cached a part the homepage/random/features pages (in general the minimum needed to get new content indexed).

Something like what is pointed out here: http://www.phpvibe.com/forum/suggestions/html-caching/msg17838/#msg17838

I used it in the past with very good results on very big (100k+pages) Wordpress site using https://wordpress.org/plugins/wp-super-cache/

This would decrease enormously the DB load but it would give the possibility to get indexed new video/content anyway.

[Marius P.]

Hi! Is on my to do list for next upgrade. What are the stuck queries?

[TigerClaw]

After doing some tests with the various query cache the situation seems to be better.

I can't point out a specific query because my slow query log is almost empty. It is just there are too may connections going on and the server is struggling even if it is a dual processor with 64 Gb of RAM.

Really looking forward for the new version!

[Marius P.]

I assume you need to do some optimization on the SQL, first thing.

[TigerClaw]

So I arrived to a quite stable configuration but I still have some issues like:

[!!] Joins performed without indexes: 248647
[!!] Temporary tables created on disk: 27% (9M on disk / 36M total)
[!!] Table locks acquired immediately: 88%

Adjust your join queries to always utilize indexes
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
    join_buffer_size (> 19.5M, or always use indexes with joins)

join_buffer_size is already quite large. I'll try to increase it again but it is necessary to improve the queries instead of trying to fix them with caching.

About the InnoDB suggestion.
Did you already try to use it with PhpVibe? Any performance increase?

Thanks!

[Marius P.]

Didn't, I plan to change the query system to remove the joins by using cache details of each users, categories, etc.