Invision Community 4: SEO, prepare for v5 and dormant account notifications By Matt Monday at 02:04 PM
Maximilian Atlantis Posted April 1, 2023 Posted April 1, 2023 (edited) In my host provider IONOS on the database dashboard, the size of my forum database shows 2 GB of 2 GB used, but when I open the database - it shows 797 MiB. Support agent can't answer me the question where is hiding the rest 1,203 MiB... Edited April 1, 2023 by Maximilian Atlantis
Randy Calvert Posted April 1, 2023 Posted April 1, 2023 (edited) This post was recognized by Marc! Randy Calvert was awarded the badge 'Helpful' and 5 points. Unfortunately this is a question for your hosting provider. IPS can’t control what they report or how they report it. It’s possible the database needs optimized or repaired, but again that’s something they would have to assist with. Also… if they can’t figure it out… IPS cloud offerings don’t have those restrictions. There is no database limits or even limits on page views anymore. Edited April 1, 2023 by Randy Calvert Marc, Askancy and Maximilian Atlantis 1 1 1
Adlago Posted April 1, 2023 Posted April 1, 2023 3 hours ago, Maximilian Atlantis said: In my host provider IONOS on the database dashboard, the size of my forum database shows 2 GB of 2 GB used, but when I open the database - it shows 797 MiB. Support agent can't answer me the question where is hiding the rest 1,203 MiB... A database backup size may also be added if your hosting account creates one Maximilian Atlantis and SJ77 1 1
Maximilian Atlantis Posted May 1, 2023 Author Posted May 1, 2023 no it's shows 2 GB of 2 GB used, but the actual size in phpmyadmin is 812mb
Randy Calvert Posted May 1, 2023 Posted May 1, 2023 1 minute ago, Maximilian Atlantis said: no it's shows 2 GB of 2 GB used, but the actual size in phpmyadmin is 812mb Unfortunately then you would need to ask your host to explain it. The IPB software itself does not control how database sizes are reported. They're the user of the database, not the administrator of it.
Marc Posted May 2, 2023 Posted May 2, 2023 Its not releasing space correctly unfortunately, so yes it would be your hosting company that would correct this. I tend to see this happen more on myisam than innodb, so thats certainly something that would be worth checking too (you should be using INNODB tables)
Maximilian Atlantis Posted May 3, 2023 Author Posted May 3, 2023 Here is the provider answer This is an update about your database issue. B quota showing in our system is accurate based on the quota checked in the MySQL server. PHPMyAdmin delivers wrong values for database size if used with innodb tables. PHPMyAdmin ignores data_free for innodb tables, even though innodb is the only storage engine which delivers realistic values for this value . The phpmyadmin developers consider that everybody uses innodb with file_per_table = 0 . If this would be the case all innodb files would be located within the ibdata1 file and no space could be reclaimed. In this case it would kind of make sense to report 0 as value for data_free. To fix this issue, database table needs to be optimize. wegorz23 1
Maximilian Atlantis Posted May 4, 2023 Author Posted May 4, 2023 Well... how can I optimize the database table?
Jim M Posted May 4, 2023 Posted May 4, 2023 1 minute ago, Maximilian Atlantis said: Well... how can I optimize the database table? You would need to speak with your hosting provider as the items they mentioned there are MySQL server configuration, not things we would optimize via the table schema itself.
Maximilian Atlantis Posted May 4, 2023 Author Posted May 4, 2023 They answeredI would like to inform you that there is no issue with the actual MySQL database structure. The quota showing on the database is correct. The only way to fix the issue is to optimize the tables on that particular database. You need to reconsider the cache table on this website as well.
Jim M Posted May 4, 2023 Posted May 4, 2023 1 minute ago, Maximilian Atlantis said: They answeredI would like to inform you that there is no issue with the actual MySQL database structure. The quota showing on the database is correct. The only way to fix the issue is to optimize the tables on that particular database. You need to reconsider the cache table on this website as well. Unfortunately, then if the database quota is correct then you would need to upgrade your hosting so that you aren't limited to 2GB. The database table structure would be all which we can assist with, everything outside of that would be on your hosting provider to carry out their configuration and maintenance.
Maximilian Atlantis Posted May 4, 2023 Author Posted May 4, 2023 So, how could you assist with the database table structure?
Jim M Posted May 4, 2023 Posted May 4, 2023 28 minutes ago, Maximilian Atlantis said: So, how could you assist with the database table structure? I'm not sure what you mean, it doesn't sound like you have a structure issue because there are no errors on your community or issues with indexes being accessed, etc... That is essentially the structure of the database tables. The maintenance and configuration of the MySQL server would handle when these items essentially need to have "garbage collection" done because the data has been deleted by a query/code and the server hasn't necessarily cleaned the space up yet to be used elsewhere.
Adlago Posted May 4, 2023 Posted May 4, 2023 43 minutes ago, Maximilian Atlantis said: So, how could you assist with the database table structure? And you check through phpMyAdmin which of the tables in your database actually has a large size. There is such a column - size, in the list of tables, just scroll and you will find which table has a strangely large size. This will direct help reasoning to you in the direction of solving the issue Maximilian Atlantis 1
Maximilian Atlantis Posted February 3 Author Posted February 3 This is doesn't work. All the sizes correct in DB
Jim M Posted February 3 Posted February 3 11 minutes ago, Maximilian Atlantis said: This is doesn't work. All the sizes correct in DB That would mean nothing is wrong that you can improve. You would need to upgrade your hosting if you are running into size limits. Maximilian Atlantis 1
Randy Calvert Posted February 3 Posted February 3 Or you need to start deleting a bunch of content. Personally I would switch to a different hosting provider that provides such restrictive database limits. Thats an artificial limit designed to make you upgrade to a higher package IMO. Maximilian Atlantis and teraßyte 1 1
Maximilian Atlantis Posted February 4 Author Posted February 4 Here is the techsupport answer Тhank you for reaching out regarding the persistent issue with your database size. I appreciate your patience, and I understand the importance of resolving this matter. Upon escalating the issue to our 2nd level support, here's what they were able to investigate: The discrepancy in the database size quota displayed in PHPMyAdmin compared to the Hosting Control Panel (HLT/CP) is likely due to the predominant use of InnoDB tables. InnoDB is the default storage engine for MySQL 5.7, and PHPMyAdmin may not accurately account for the space usage in InnoDB tables, particularly the data_free value. Here's a simplified explanation: InnoDB tables, which you likely use, have a high amount of "bloat" or data_free. PHPMyAdmin does not consider the data_free value for InnoDB tables, leading to a discrepancy in reported sizes. The reported size in HLT/CP is accurate based on the quota checked in the MySQL server. To resolve this issue, our recommendation is to optimize your database tables. This process involves reclaiming space by rewriting the tables, which is a normal database maintenance task. Unfortunately, database optimization is not within the scope of support provided by IONOS. Here are the suggested steps: 1. Access your PHPMyAdmin or database management tool. 2. Identify and optimize the InnoDB tables in your database. If you are not comfortable performing these steps or require further assistance, I recommend consulting with a web developer or a database administrator. They will be able to guide you through the optimization process and ensure that your database operates efficiently. Should you have additional questions or need further clarification, feel free to reach out. We appreciate your understanding and cooperation as we work towards a resolution.
opentype Posted February 4 Posted February 4 Even if you were able to optimize the data in one way or another, you are only pushing the problem away some months into the future, when the space is used up again. A 2GB limit is just too small for a successful Invision Community installation. Daniel F, Marc, Maximilian Atlantis and 2 others 4 1
teraßyte Posted February 4 Posted February 4 Also, considering the database's size, I would never recommend doing the "optimization" from phpMyAdmin. I'd rather do it from SSH to avoid timeouts. As opentype already mentioned, it's probably time to change hosting. Your site is not a good fit for that limit. Maximilian Atlantis 1
Maximilian Atlantis Posted February 6 Author Posted February 6 found one more useless solution https://www.ionos.com/help/hosting/troubleshooting-mysql-databases/optimizing-mysqlmariadb-databases-to-prevent-exceeding-the-capacity-limit/ It grow one mb per day... even if I delete old posts... the problem was a long time ago Tech support made sometning and it grow again
Recommended Posts