Using MySQL query cache with Invision

[Donnie95]

Hello,

I was wondering if anyone is using MySQL Query Cache with Invision. Will this cause issues? I am trying to reduce my website TTFB, especially on topics. (I have a lot of rep points)

Thanks.

[Stuart Silvester]

Yes, customers have used the query cache before however it was deprecated in MySQL 5.7.20 and does not exist in MySQL 8 (which we now recommend).

I doubt that would make an impact on TTFB though. It might be worth seeing if you've got any slow queries specific to your community.

[Donnie95]

30 minutes ago, Stuart Silvester said:

Yes, customers have used the query cache before however it was deprecated in MySQL 5.7.20 and does not exist in MySQL 8 (which we now recommend).

I doubt that would make an impact on TTFB though. It might be worth seeing if you've got any slow queries specific to your community.

I checked MYSQL slow logs, most of the slow logs are 'core_sessions' and 'core_view_updates'

::IPS\Session\Store\_Database::updateSession:381*/ INSERT INTO `core_sessions` ( `id`, `member_name`, `seo_name`, `member_id`, `ip_address`, `browser`, `running_time`, `login_type`...

But I use the default 10s long_query_time, I think I need to change it to 3s.

I won't be using the query cache as it is deprecated. I'm unsure about what I can do to reduce the TTFB on topics, 1 second is too high.

OPcache is enabled.

PHP FPM set dynamic.

I'm using Redis.

Nginx.

Cloudflare.

My server using NVME SSD and is not overloaded.

 

[Marc Stridgen]

The best advice I can give you there is to contact your hosting company on server performance issues.

Its quite a common misconception that having a lot of x will solve y. It is the configuration of such items generally you would need to be looking at, and that is an optimisation thing on your server. And there are far too many variables for us to be able to give you a simple answer. This is why we have a cloud environment. So people dont have to think about these things (and in our experience, most just want to run their site). 

As a start, I would advise taking a step back and questioning what you have done already

• NGinx - Why?
• Redis - Are you sure you need it? Is the overhead speeding things up, or slowing it down?
• mySQL - Is it configured to use the resources you have?
• Cloudflare - What items do you have set up, and are some of those items potential overhead on initial load?
• opcache - Is this configured correctly, or is it slowing things down?

 

[Donnie95]

21 minutes ago, Marc Stridgen said:

The best advice I can give you there is to contact your hosting company on server performance issues.

Unmanaged hosting, and the server is powerful enough, the site is not using that much resource. I believe my issue is with caching or MySQL and reputation points. The leaderboard on my site takes 20 seconds to load, while a simple page like the staff directory loads quickly. (200ms)

19 minutes ago, Marc Stridgen said:

Redis - Are you sure you need it? Is the overhead speeding things up, or slowing it down?

I completely forgot about the overhead setting for Redis, I definitely need to try it.

Opcache, cloudflare and nginx are configured correctly. Not sure about MYSQL. 

[Marc Stridgen]

Just based on these 2 statements here

26 minutes ago, Donnie95 said:

Unmanaged hosting, and the server is powerful enough, the site is not using that much resource. I believe my issue is with caching or MySQL and reputation points.

and

26 minutes ago, Donnie95 said:

Not sure about MYSQL. 

I would very highly advise getting something managed, or someone to manage this one on your behalf

[Driven 2 Services]

I agree with Marc on this.  Out of the box MySQL, PHP-FPM, and opcache settings, in my experience, are rarely optimal. I've gotten an order of magnitude better speed from MySQL alone with proper tuning.

[Donnie95]

10 minutes ago, Driven 2 Services said:

I agree with Marc on this.  Out of the box MySQL, PHP-FPM, and opcache settings, in my experience, are rarely optimal. I've gotten an order of magnitude better speed from MySQL alone with proper tuning.

What tuning did you do to MYSQL?

[Driven 2 Services]

There's not a one size fits all answer. Drive Fsync speed, available ram, database size, other programs, MySQL (or MariaDB) version, number of CPU cores, if Query cache is available what the cache's fragmentation rate is, whether full ACID compliance is needed or if they can live with a second or so of loss if a crash occurs, and so many more variables.

There are many items that need to be tested, and benchmarked to do it properly.  I don't suggest tools like mysqltuner  -  it will often give very bad advice and I've seen outrageous settings it recommended that people used which hurt performance.  Its okay for getting an idea of a few basic stats.

 

 

 

 

Edited January 24 by Driven 2 Services

[Donnie95]

I increased the InnoDB buffer pool size from 128M to 2GB (My database is 2GB) While there is an increase in RAM usage, I haven't observed any significant improvements in speed.

I'll test whether upgrading my server makes a difference. Please let me know if you have more tips for speed improvements with PHP-FPM, MySQL 8, and NGINX.

[Marc Stridgen]

17 hours ago, Donnie95 said:

What tuning did you do to MYSQL?

In all honesty, if you have to ask that question, I would very much advise not doing this yourself. 

[AlexWebsites]

On 1/24/2024 at 6:54 AM, Donnie95 said:

I am trying to reduce my website TTFB

I'm in the same boat and started this topic as I'm thinking through my setup as well and testing different caching and speed improvements...if interested in joining the discussion.