Slow forum access / database too large (Technics)

by Otto Tuil @, Netherlands, Saturday, March 18, 2017, 13:24 (2568 days ago)

Hi all,

My forum has currently more than 200,000 entries. It sill runs nicely, but I noticed that the performance is slowly degrading. After contact with my web host he replied by saying that there are a lot of database queries. It looks like there is a query:

SELECT COUNT(*) FROM mlf2_entries WHERE pid = 0 AN

every time a user accesses the forum. I guess that is for the calculation of the amount of entries and the number of pages. He was wondering if that amount of queries are necessary, because it takes a lot of resources. Is there is for instance a possibility to calculate the total number of entries only once a day, and keep a daily counter for the new entries after the last calculation? Or perhaps there are other ways to speed up the database?

Regards,
Otto (Holland)

Slow forum access / database too large

by Otto Tuil @, Saturday, March 18, 2017, 13:29 (2568 days ago) @ Otto Tuil

In addition, I noticed that creating a backup of the entries database does not work properly. The backup appeared to be incomplete. Perhaps also a result of the large amount of entries.

Avatar

database too large

by Auge ⌂, Sunday, March 19, 2017, 12:14 (2567 days ago) @ Otto Tuil
edited by Auge, Friday, March 31, 2017, 14:40

Hello

In addition, I noticed that creating a backup of the entries database does not work properly. The backup appeared to be incomplete. Perhaps also a result of the large amount of entries.

Yes and no. Yes, more entries means longer execution time of the backup script. But the execution time of a PHP-script is limited. With around 200,000 entries the database is definitely too large for a backup with a PHP-script on a hosted webserver. Especially it's an issue, when your account is a shared hosting account. There you have to share also the PHP-environment and the execution resources for the scripts. So in the end it's not a limitation of the database but a (reasonable) limitation of the PHP-environment.

I think, the whole concept of a backup from within the PHP-script in it's current form is disputeable. But that's a question of a big rework and/or the available tools from outside the script. If your hosting company makes a different available (i.e. via phpMyAdmin or similar) use this tool or test it at least.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!

database too large

by Otto Tuil @, Monday, March 20, 2017, 21:12 (2565 days ago) @ Auge

Thanks for the info, Auge. I already backed up the entries via phpMyAdmin. After some editing I could place the backup to the forum again via the backup function. I guess that creating the backup takes more resources than writing back :)

Otto

Avatar

Slow forum access, @Milo

by Auge ⌂, Sunday, March 19, 2017, 11:54 (2567 days ago) @ Otto Tuil

Hello

My forum has currently more than 200,000 entries. It sill runs nicely, but I noticed that the performance is slowly degrading. After contact with my web host he replied by saying that there are a lot of database queries.

Yes, there a several queries on every request for a page. Some of them are optimisable.

Is there is for instance a possibility to calculate the total number of entries only once a day, and keep a daily counter for the new entries after the last calculation?

It would be possible to count the entries once a day and set a second counter for the entries of the day so both values could be totalled. But that's not the way I would do it. For me it seeems to be more feasible to reset the one value for the number of entries every time a new entry occurs. So we'll have no counting at all, only a number in a table, that increases from time to time.

@Milo: Since we have the new table *_temp_infos I think, it would be a good idea to store such values (one valid value for a whole forum but not a static value) there, wouldn't it?

Or perhaps there are other ways to speed up the database?

I would expect several queries as optimisable.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!

Avatar

Slow forum access, @Milo

by Micha ⌂, Sunday, March 19, 2017, 12:31 (2567 days ago) @ Auge

Hi,

@Milo: Since we have the new table *_temp_infos I think, it would be a good idea to store such values (one valid value for a whole forum but not a static value) there, wouldn't it?

Sounds good, yes. We can add a trigger to the entry table to update this value on insert/delete.

/Micha

--
applied-geodesy.org - OpenSource Least-Squares Adjustment Software for Geodetic Sciences

Avatar

Slow forum access, @Milo

by Auge ⌂, Sunday, March 19, 2017, 14:08 (2567 days ago) @ Micha

Hello Milo

@Milo: Since we have the new table *_temp_infos I think, it would be a good idea to store such values (one valid value for a whole forum but not a static value) there, wouldn't it?


Sounds good, yes. We can add a trigger to the entry table to update this value on insert/delete.

Yesss, triggers. Because I never used them in MySQL [1] every time they are mentioned I'm a bit surprised. :-)

As far as I researched, triggers was introduced with MySQL 5.0.7 and the user needs the granted privilege (German language) for it. We can set the minimal supported MySQL server version to the version 5.0.7. IMHO it's no problem to require nowadays a least version 5.5 (it's a long time ago I saw older versions), so this should not be an issue.

But how to deal with privilege thing? What, when the forum operator doesn't has this database privilege on her/his server installation? Don't offer the feature? Work around the restriction with PHP-code? If so, why not using it from the beginning?

Hmm, I think a trigger is the way to go in this case. But I also think we need at least an argumentation for the case, an operator misses this function because of the restrictions of the running system.

Aside from that we need IMHO a database pro. I think there is a big potential for optimisations.

Tschö, Auge

[1] I use them often in MS SQL Server but there I have one server, not hundreds of users with differing environments.

--
Trenne niemals Müll, denn er hat nur eine Silbe!

Avatar

Slow forum access, @Milo

by Micha ⌂, Sunday, March 19, 2017, 14:23 (2567 days ago) @ Auge

Hi Auge,

Yesss, triggers. Because I never used them in MySQL [1] every time they are mentioned I'm a bit surprised. :-)

Maybe, my suggestion was over-hasted because I take a (very) short look into the source code. Of course, there are some SQLs that starts with SELECT COUNT(*) ... but there are several WHERE-conditions. How to handle these conditions?

/Micha

--
applied-geodesy.org - OpenSource Least-Squares Adjustment Software for Geodetic Sciences

Avatar

Slow forum access, @Milo

by Auge ⌂, Sunday, March 19, 2017, 17:56 (2567 days ago) @ Micha
edited by Auge, Sunday, March 19, 2017, 18:04

Hello Milo

Yesss, triggers.


Maybe, my suggestion was over-hasted because I take a (very) short look into the source code. Of course, there are some SQLs that starts with SELECT COUNT(*) ... but there are several WHERE-conditions.

My question referred only to the forum wide counting of entries. There are further, differing cases. Those was not objective of my question.

How to handle these conditions?

You are right. The conditions are there not without a reason. Counting the entries of the forum can't be done in one variable if the current behaviour should be kept. There might be for some visitors hidden categories (categories only for registered users, categories only for admins and mods). Currently they are not taken into account in the counting for users without permissions.

1. We can count the entries once a day and live with the imprecision over the day.
2. We can count the entries once a day and summate the count of postings of the day.
3. We can count all entries together live (everytime a new entry is posted) without remarking the category of the entries.
4. We can count the entries per category and summate the entries in the categories with help of the access informations from the categories table.

The first three solutions abdicate the separate counting of special categories. No one will die in consequence but we would give up a (small) feature most visitors never notices. The fourth solution is the sophisticated one. It would be no problem to have more than one counter. But there are also questions for this scenario.

- How to count entries in a forum without categories?
- How to handle the count of entries in a forum without categories when the admin creates categories and moves entries into these new categories?
- How to handle the count of entries in categories in a forum where the admin deletes one, a few or all categories?

Not, that these questions are not answerable, but these questions must be asked.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!

Avatar

Slow forum access, @Milo

by Micha ⌂, Monday, March 20, 2017, 08:01 (2566 days ago) @ Auge

Hi,

1. We can count the entries once a day and live with the imprecision over the day.
2. We can count the entries once a day and summate the count of postings of the day.
3. We can count all entries together live (everytime a new entry is posted) without remarking the category of the entries.
4. We can count the entries per category and summate the entries in the categories with help of the access informations from the categories table.

I think, one should analysis the COUNT-querys to get an overview of the several conditions. Based on this overview, we can discuss a possible solution.

/Micha

--
applied-geodesy.org - OpenSource Least-Squares Adjustment Software for Geodetic Sciences

Avatar

Slow forum access, @Milo

by Auge ⌂, Monday, March 20, 2017, 08:21 (2566 days ago) @ Micha

Hello

I think, one should analysis the COUNT-querys to get an overview of the several conditions.

I'm working on a list of the queries in every single include-file.

Based on this overview, we can discuss a possible solution.

Yes.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!

Avatar

Slow forum access, a few thoughts for a discussion

by Auge ⌂, Sunday, March 19, 2017, 21:13 (2566 days ago) @ Otto Tuil

Hello

Because of Otto's report and further observations about this forum loading slow, I began to control the database queries in the files from the directory includes. The check is not finished yet. Because I work on the software not for one complete year, I don't know every "dark corner" of the code. Thatswhy I have several questions.

To quieten Otto because of the lot of database queries, most of them are useful and often you'll find groups of queries where only one of them get's executed on one page request. That's not a very elegant code notation but it's no problem. But I also found several queries which are questionable to me.

I found blocks to read, delete and rewrite the cached version of the user data (namely the signature) in the entry.inc.php (line #307 ff.) and the thread.inc.php (line #189 ff.). Is there a reason for the behaviour to check for a cached version of the signature and create one if necessary in scripts, that only should read these data? I expect the signature only to be editeable in the users own edit form and in the user edit form for the admins. Why not creating the cached version when editing the users data and only then? Do the cached data expire so it is necessary to recreate them from time to time? If not, the code is IMHO useless.

There are update queries for the user data, i.e. in the login.inc.php (line #151 ff.) but not only there. When storing the time of the last login, logout and the users IP of this session a few further columns will be updated with their current values because of the table definitions. That's ok even it is improvable. But why the heck the columns language, time_zone and theme gets updated? There should be no change of the values during the login process. Is there any reason for it?

Maybe we can begin with removing unnecessary code to make executing of the forum scripts faster. Therefore we need sureness about questionable code parts.

Tschö, Auge

--
Trenne niemals Müll, denn er hat nur eine Silbe!

RSS Feed of thread