database resource, way too high

[MeesterGijs]

Hello,   I received this email. What can we do???????? I'm a bit scared I must admid. The MySQL database deb124449_wp1 contains tables that are (over) full, including the table wp_wsal_metadata. When the relevant tables are called, for example when loading a page with a visitor. Then a lot of server resources are consumed. This has an impact on the accessibility of both your website and our server. Our advice is to clean up the relevant tables and optimize the actions on the database so that the overload on the database is removed. In concrete terms, this makes your website considerably slower for visitors. The following points help enormously in getting your website and database faster:

• Optimize queries and actions on the database where possible.
• Clear data in tables and archive data where possible. A.o. the table wp_wsal_metadata is full of old log data from the WP Security Audit Log Plugin. It is best to clear this up and / or archive it in a separate table
• Optimize database and convert tables to InnoDB: https://www.antagonist.nl/blog/2017/08/database-optimalisatie/
• Switch to a newer PHP version: https://www.antagonist.nl/help/nl/webhosting/advanced/php-selector
• Configure caching so that complex actions of your website are remembered: https://www.antagonist.nl/blog/2019/11/redis-instellen/

You can read how to view the database overload (blue lines) at: - https://www.antagonist.nl/help/nl/webhosting/monitoring#meer_inzicht_in_resources Normally database usage should be well below the limits, so that there is enough computing power and I/O left for the other things on your website. At the moment, the I/O to/from the database is therefore well outside the limits. This not only causes a very negative impact on the loading speed of your website, but also a negative impact on our database server. For now we have not yet closed the website. We do not like to do that, because we prefer to see that our customers' websites work reliably and quickly. Of course we would like to see this problem addressed as soon as possible! Can you keep me informed by email?

[MrVibe]

For visitors : You can enable server cache, so that when a non-logged in user opens the site, there is no load on your mysql server. For remaining, there is going to be a mysql hit whenever activites happen in your site. Question : Are you using the Unit timings addon ? and there are many users who are on your site doing courses ? Also disable WordPRes heartBeat hit, Auto save hits in your site. WP Rocket has these configurations. We're planning to add Archive capability into the system so that only data which is current and active remains in the site and rest can be shafted away.

[MeesterGijs]

I have autoptimize for critical css and use wp rocket. But often when I have an issue I am adviced to not use caching. So this advice does conflict with that. But, yes I use caching. I do have some pages excluded: https://imgur.com/a/heTI1HX These are my heartbeat settings in WP Rocket. Anything I should change? I don't using Unit timing addon, because of the size it takes.. My questions: - Which PHP version can I use? The most recent one? I remember from 2019 that I was advinced to dial it back to 7.1 or even 7 because of conflicts. I'm currently using 7.2 Can I improve to 7.4? What is stored in the database rows wwp_postmeta and wp_usermate? Can I empty those? And if not, can I do anything about it's size? Respectively 70 en 81 MB. Should I use Redis caching instead of WP Rocket? Or are they all the same. My hosting is talking about Redis. Could there be benefits of using a house caching plugin/tool instead of WP Rocket or anything else? What does this info tell you? it's this week.. Please answer all questions. Thank you so much. Gijs

[MrVibe]

We use caching in Wplms.io with WP Rocket and it works great. Redis AND memcached both are good solutions based from Instructure provider point. But with a 80mb database this should not be an issue.

Redis and WP Rocket are two different things, Rocket caches for the front end, Redis is for inmemory cache, like if there are two similar mysql queries then they'll be fetched from memory not from database.

Which Mysql Version are you using ? Is it MAriaDB or Mysql ?

PHP version definitely makes an impact but your bottle neck is mysql :

You can also make some changes, like :

if you have big quizzes 100+ questions, then enabling "Check Answers switch" would make a big impact when it is disabled. Becasue then we're making 100 inserts in 1 go.

[MeesterGijs]

My database was about 350 MB, now after some changes just 120MB. Should I give Redis caching a try? I use mysql I gues... https://imgur.com/a/w5bkINo I have small quizzes of about 10/15 questions. But every course has at least 4 quizzes. I have 60+ courses, every courses has 6+ elements and at least 4 quizzes. I have a forum connected to every course. And about 2000 active memberships and 1500 expired memberships. I could use one forum for all courses. Does that save substantial space/speed? How should I interprete your graph? I don't really understand. Which php version should I use? Any other settings in LMS that I should change? Gijs

[Anshuman Sahu]

Hi we are going to build a plugin to delete the rows which are not being fetched .this would be very complex plugin and would take time to build .

[MeesterGijs]

My hosting is actually threatening me to delete my site if I don't lower the database usage; I/O. I have been working around the clock to find ways to lower it. Any other suggestions? And what time will that take?

[MeesterGijs]

Dear Alex, Any idea when to expect 4.0?

[MrVibe]

I think you should consider creating a saperate server for mysql needs or amazon RDS for your mysql needs. A 350 mb database is not that big, we have 1.5 gb database for this site and runs smoothly. Besides, I do not think the size is causing issues, its the number of read and writes the mysql must be having in your site. Can you ask how big of an impact your site is having on the host ? What is the package that you're having with your host ? Make sure you keep backups. I also would like to mention that we would be launching hosted WPLMS solution on amazon AWS after the 4.0 update.

[MeesterGijs]

Dear Mr.Vibe, I currently have between 250-1000 visitors per day. Does an extra server make much sense? Does sound very interesting, though. Definitely something I'll keep in mind. 1,5 gb database is very comforting to hear :) Impact on my site? Is this something useful for you: https://imgur.com/a/20MkGvK My package is 2 CPU cores, 2048 MB, unlimited bandwith, 256 MB redis caching and unlimited SSD. Anything else you want to know? Great to hear. Can you give an appr. date for the 4.0 release? First mention was 5 months ago :) Thanks for help. Look forward to your reply. My website is my baby. I try to take good care of it.

[Anshuman Sahu]

We are talking about impact impact of your website on the hosting provider due to which they are warning you to delete your site ? I guess this is your server metrics and this also does not shows any significant memory usage . Max average limit is upto 162% out of 200%(2 cores) . you can ask your hosting provider to upgrade your plan for now or purchase plan on amazon to host your database and import your mysql dump to new database and change the database host in wp-config.php .   For wplms 4.0 well we are working on it : adding more features to it to compete in market .This is why its getting delayed .    

[Author]

Posts