Jump to content

Using MySQL query cache with Invision


Recommended Posts

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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?

 

Link to comment
Share on other sites

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. 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by Driven 2 Services
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...