Performance enhancements? (General)

by Wendell, Tuesday, December 17, 2013, 17:27 (3754 days ago)

About 3.5 years ago, I installed MLF without any idea of the success that would result. We now have over 3,000 users and around 235,000 postings. We get 25,000 unique visitors per month and still climbing.

This is all great and people love the forum for the most part. However, it's on a VPS with some pretty great specs but the forum uses a LOT of resources. I'm finding that I have to optimize the database daily and reboot every few days to keep it running halfway decent.

So, with that said, does anyone have any advice in terms of performance?

Avatar

Performance enhancements?

by Auge ⌂, Thursday, December 19, 2013, 16:37 (3752 days ago) @ Wendell

Hello

About 3.5 years ago, I installed MLF without any idea of the success that would result. We now have over 3,000 users and around 235,000 postings. We get 25,000 unique visitors per month and still climbing.

Congratulation, I took a look into your forum. It seems well attended.

This is all great and people love the forum for the most part. However, it's on a VPS with some pretty great specs but the forum uses a LOT of resources. I'm finding that I have to optimize the database daily and reboot every few days to keep it running halfway decent.

So, with that said, does anyone have any advice in terms of performance?

Maybe the database queries and/or the table structures can bear some revisioning. But that's a difficult task. It would be worse to <do_some_research /> kill the patient with the cure.

I'll have a look in it during the next week (without a promise for success).

Tschö, Auge

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

Performance enhancements?

by grognard, Sunday, December 22, 2013, 16:01 (3749 days ago) @ Wendell

Congratulations on the success of your forum. :-) Like Auge, I looked at your site and it looks great! I love the aesthetics; I think you nailed 'clean and simple'.

I'm not going to pretend I understand the database structure and optimizations there, but on a basic administrative level - is your hosting optimized for the kind of traffic you get? Msql and Apache (if Apache) are rarely ever set up on VPS for 'real' traffic. Also VPS vary tremendously in quality and configuration etc. What is causing the issue? The database size? Parsing tables? Memory usage? CPU? Honestly, compared to most standard php/mysql forum applications MLF is pretty low fi and very tweakable.

My own forum runs on an embedded device using only non-volatile memory card/s for storage. It has practically no traffic (and never will) but I'm curious to know what sort of resource issues you are running in to in the long term.

Performance enhancements?

by Wendell, Sunday, December 22, 2013, 16:55 (3749 days ago) @ grognard

My host has tweaked the server as much as he can to ensure that it is performing optimally. We've upgraded the hardware twice, which made a small improvement each time, but not enough to warrant throwing even more hardware at it. We reboot it every few days as there is a very noticeable slowdown as time goes on. I also manually optimize the database every evening before going to bed. These two practices seem to help the most.

My host always points to the caching as a big problem. Part of the tweaks he did to the server were aimed at improving the cache system. I don't know enough about that stuff to tell you exactly what he did.

Thank you both for your replies. :-)

Performance enhancements?

by grognard, Sunday, December 22, 2013, 17:38 (3749 days ago) @ Wendell

My host has tweaked the server as much as he can to ensure that it is performing optimally. We've upgraded the hardware twice, which made a small improvement each time, but not enough to warrant throwing even more hardware at it. We reboot it every few days as there is a very noticeable slowdown as time goes on. I also manually optimize the database every evening before going to bed. These two practices seem to help the most.

My host always points to the caching as a big problem. Part of the tweaks he did to the server were aimed at improving the cache system. I don't know enough about that stuff to tell you exactly what he did.

Thank you both for your replies. :-)

Sounds like the hardware is inadequate - if you've upgraded twice its probably lacking RAM. If the 'caching' is an issue its probably sub-optimal hardware/mysql setup. Its possible there is some kind of SQL issue with MLF and large databases ofc. Although there are other sites with huge databases out there. You mentioned rebooting which, assuming its a LINUX/BSD platform, means its running out of memory. I think your only real recourse will be to go to a dedicated machine with the right software and hardware specification for the site utilization.

Good luck with it anyway. :-)

Avatar

Performance enhancements?

by Auge ⌂, Monday, December 23, 2013, 00:46 (3749 days ago) @ grognard

Hello

Sounds like the hardware is inadequate - if you've upgraded twice its probably lacking RAM.

Hmmm ... I don't think so. If the server was upgraded twice we should consider the hardware as outbidden.

If the 'caching' is an issue its probably sub-optimal hardware/mysql setup. Its possible there is some kind of SQL issue with MLF and large databases ofc. Although there are other sites with huge databases out there. You mentioned rebooting which, assuming its a LINUX/BSD platform, means its running out of memory.

Let's compare: Alfie has also a well attended forum. More than 8000 registered people but with around 11700 postings not nearly a twentieth of number of postings of Wendells forum. I've no idea about daily page views.

His forum is driven by My Little Forum 1 (v 1.8 β (an old β)). It's the (further developed) ancestor of the actual software. There was never a report of performance problems like Wendells report. Otherwise I can't imagine, that Alex introduced an undiscovered performance break a while ago without many reports like this over the time.

It seems to be a wide field ...

Tschö, Auge

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

Avatar

Performance enhancements?

by Alfie ⌂, Vienna, Austria, Tuesday, December 24, 2013, 14:39 (3747 days ago) @ Auge
edited by Alfie, Tuesday, December 24, 2013, 20:10

Hi

Let's compare: Alfie has also a well attended forum. More than 8000 registered people but with around 11700 postings not nearly a twentieth of number of postings of Wendells forum. I've no idea about daily page views.

Much quieter site. Size of the database 12 MB; this year (average daily use):
211 unique visitors, 15 simultaneous connections, 761 visits, 1,090 pages, 2,815 hits, bandwidth 6.28 MB.

[…] There was never a report of performance problems like Wendells report.

Yep. On one hand lower traffic, but on the other hand it’s located on a busy shared host. Last reboot in June; I optimize the database once ~ every three months.

--
Cheers,
Alfie (Helmut Schütz)
BEBA-Forum (v1.8β)

Performance enhancements?

by Wendell, Tuesday, December 24, 2013, 17:21 (3747 days ago) @ Alfie

Thanks for this discussion, everyone. At least I know I'm doing the right thing by optimizing it often. :)

Much quieter site. Size of the database 12 MB; this year (average daily use):
211 unique visitors, 15 simultaneous connections, 761 visits, 1090 pages, 2815 hits, bandwith 6.28 MB.

For comparison: size of the database is 156 MB, 219,675 unique visitors, 881,148 visits, and 730 pages.

Avatar

Performance enhancements?

by Alfie ⌂, Vienna, Austria, Tuesday, December 24, 2013, 20:09 (3747 days ago) @ Wendell
edited by Alfie, Tuesday, December 24, 2013, 20:18

Hi

Much quieter site. Size of the database 12 MB; this year (average daily use):
211 unique visitors, 15 simultaneous connections, 761 visits, 1,090 pages, 2,815 hits, bandwidth 6.28 MB.


For comparison: size of the database is 156 MB, 219,675 unique visitors, 881,148 visits, and 730 pages.

Yep, but we have to compare your daily use: ~1,500 unique visitors, ~2,500 visits, ~16,000 pages. So that’s one order of magnitude larger than mine. As I have seen the last two days you serve about 2–5 times more connect’s. So I posit the bottleneck is the database indeed. Due to performance issues (slow rendering by GeSHi) Alex opted in mlf for two tables: One keeping the original entry with BBCodes, and a cache-table – containing already rendered HTML. Instead of calling GeSHi all the time, the code looks up whether there is already an entry in the cache-table. If yes, GeSHi is bypassed. Quick question: What are the sizes of both tables in your installation?

--
Cheers,
Alfie (Helmut Schütz)
BEBA-Forum (v1.8β)

Performance enhancements?

by Wendell, Wednesday, December 25, 2013, 08:31 (3747 days ago) @ Alfie

Quick question: What are the sizes of both tables in your installation?

mlf_entries is 127.1 mib and mlf_entries_cache is 104.4 mib.

Performance enhancements?

by Wima, Thursday, December 26, 2013, 12:25 (3745 days ago) @ Alfie

Yep, but we have to compare your daily use: ~1,500 unique visitors, ~2,500 visits, ~16,000 pages.

Da auch ich Schwierigkeiten mit der Geschwindigkeit von mlf habe hier die Daten meines Forums. Täglich rund 1500 Besuche, 1000 eindeutige Besucher und 12000 Seiten. Das Forum liegt auf einem Vserver. Serverneustart und Dantenbankoptimierung bringen keine Verbesserung. Innerhalb eines Jahres wurden rund 31000 Beiträge geschrieben. Die Tabelle entries ist 33MB groß und entries_cache 30MB.

Das alte Forum unter mlf1 war und ist erheblich schneller. Es ist noch als Archiv auf dem gleichen Server online mit rund 233000 Beiträgen. Die Datenbank ist 234MB groß.

Wima

Avatar

Performance enhancements?

by Auge ⌂, Thursday, December 26, 2013, 23:04 (3745 days ago) @ Wima

Hallo

Yep, but we have to compare your daily use: ~1,500 unique visitors, ~2,500 visits, ~16,000 pages.


Da auch ich Schwierigkeiten mit der Geschwindigkeit von mlf habe hier die Daten meines Forums. Täglich rund 1500 Besuche, 1000 eindeutige Besucher und 12000 Seiten. Das Forum liegt auf einem Vserver. Serverneustart und Dantenbankoptimierung bringen keine Verbesserung. Innerhalb eines Jahres wurden rund 31000 Beiträge geschrieben. Die Tabelle entries ist 33MB groß und entries_cache 30MB.

Die Fragen, die ich Wendell stellte, gehen nun auch an dich:

Wie und womit führts du die Datenbankoptimierung durch? Worin zeigt sich der beobachtete Unterschied?

Das alte Forum unter mlf1 war und ist erheblich schneller. Es ist noch als Archiv auf dem gleichen Server online mit rund 233000 Beiträgen. Die Datenbank ist 234MB groß.

Das lässt die Wahrscheinlichkeit meiner Vermutung, dass die Queries in den Skripten selbst nicht das Problem sind, größer werden. Die Abfragen erfolgen in mlf1 an vielen Stellen in mehreren Schritten [1], wo mlf2 nur eine optimierte Abfrage absetzt.

Deine Beobachtung, dass die Datenstruktur in mlf1 schneller ist, zeigt sich wie? Kannst du das bitte – zusätzlich zu den obigen Fragen – auch noch einmal beschreiben?

[1] Beispiel: zuerst das Lesen der Daten eines Postings, danach das Lesen der Daten zurm Benutzer, der das Posting erstellt hat. Das erfolgt in mlf2 in einem Schritt.

Tschö, Auge

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

Performance enhancements?

by Wima, Saturday, December 28, 2013, 06:38 (3744 days ago) @ Auge

Wie und womit führts du die Datenbankoptimierung durch? Worin zeigt sich der beobachtete Unterschied?

Ich mache das mit PhpMyAdmin, wie von Wendell weiter unten beschrieben. Einen Unterschied konnte ich allerdings nicht feststellen.

Deine Beobachtung, dass die Datenstruktur in mlf1 schneller ist, zeigt sich wie? Kannst du das bitte – zusätzlich zu den obigen Fragen – auch noch einmal beschreiben?

In mlf1 erscheinen die Seiten in der Regel sofort, ohne bemerkbare Verzögerung. bei mlf2 gibt es oft eine Verzögerung von ein paar Sekunden. Das können auch mal 10 - 20 Sekunden sein.

Der Effekt tritt weniger zu Tage wenn, wie zur Zeit um 7:00, wenig Betrieb im Forum ist.

Wima

Avatar

Performance enhancements?

by Auge ⌂, Monday, December 30, 2013, 23:03 (3741 days ago) @ Wima

Hallo

Wie und womit führts du die Datenbankoptimierung durch? Worin zeigt sich der beobachtete Unterschied?


Ich mache das mit PhpMyAdmin, wie von Wendell weiter unten beschrieben. Einen Unterschied konnte ich allerdings nicht feststellen.

Hast du Daten bezüglich des Overheads/Überhangs vor der Optimierung?

Deine Beobachtung, dass die Datenstruktur in mlf1 schneller ist, zeigt sich wie? Kannst du das bitte – zusätzlich zu den obigen Fragen – auch noch einmal beschreiben?


In mlf1 erscheinen die Seiten in der Regel sofort, ohne bemerkbare Verzögerung. bei mlf2 gibt es oft eine Verzögerung von ein paar Sekunden. Das können auch mal 10 - 20 Sekunden sein.

Ich habe in den Skripten und in der Datenbank meiner Testinstallation (vergleichsweise armselige 12 Beiträge) bis jetzt nichts gefunden, was diese Wartezeiten erklären könnte. Der einzige Grund, der mir durch den Kopf schwirrt, ist die Art der Datenbankabfragen, wie ich ihn schon ansatzweise beschrieb.

Während in mlf1 die Daten zu Postings zumindest teilweise in zwei Schritten aus der Datenbank geholt werden (Schritt 1: lade das Posting, Schritt 2: lade, so es sich um das Posting eines registrierten Benutzers handelt, die Daten aus der Benutzertabelle), wird dies in mlf2 in einem Schritt erledigt (lade das Posting, prüfe, ob es zu diesem Posting Daten zum Benutzer in der Benutzertabelle gibt und lade diese, falls die Prüfung zutrifft). Dass das bei ungünstig gesetzten Indizes länger dauern kann, da die Datenbankengine bei vielen Einträgen länger zur Suche braucht, mag sein. Ich kann mir aber so ohne weiteres nicht vorstellen, dass das bis zu 20 Sekunden dauert.

Gucken wir mal rein. Die Benutzertabelle hat nur einen Index, nämlich die user_id. Im weiteren Verlauf wird das noch wichtig. Erstmal ist die index.inc.php dran. Die Queries stammen aus dieser Datei und sind von PHP-Sprenkeln befreit, umformatiert. Der direkte Aufruf per mysql_query wird jeweils weggelassen.

In der index.inc.php, die alle Threads listet, werden die Daten folgendermaßen abgefragt. Nach der Ermittlung, ob es Kategorien gibt und ob eine ausgewählt wurde, werden die Threads, die ausgegeben werden sollen, ermittelt.

SELECT
id,
tid
FROM mlf_entries
WHERE category = 3 AND pid = 0 AND spam = 0
ORDER BY sticky DESC, TIME DESC
LIMIT 30, 30

Die auszulesenden Felder id und tid als auch zwei der Felder, nach denen gefiltert werden soll, nämlich category und pid, haben Indizes. Das sollte also keine Probleme heraufbeschwören.

In einer Schleife werden nun pro Durchlauf für jeden der gefundenen Threads die Daten aller jeweils zugehörigen Postings gesucht (Zeile 87).

SELECT
id,
pid,
tid,
mlf_entries.user_id,
user_type,
UNIX_TIMESTAMP(TIME) AS TIME,
UNIX_TIMESTAMP(TIME + INTERVAL 60 MINUTE) AS TIMESTAMP,
UNIX_TIMESTAMP(last_reply) AS last_reply,
name,
user_name,
subject,
IF(text='', TRUE, FALSE) AS no_text,
category,
views,
marked,
locked,
sticky,
spam
FROM mlf_entries
LEFT JOIN mlf_userdata ON mlf_userdata.user_id = mlf_entries.user_id
WHERE tid = 7410 AND spam = 0
ORDER BY TIME ASC

Im JOIN wird geprüft, ob die user_id übereinstimmt. Diese ist, wie oben geschrieben, mit einem Index versehen. Auch die Filterbedingung (Feld tid) hat nach wie vor einen Index. Ob es sinnvoll ist, auch die anderen Felder, nach denen eventuell gefiltert werden soll (z.B. das Feld spam), mit einem Index zu versehen, vermag ich nicht zu beurteilen.

Danach erfolgen bei eingeschalteter Funktion noch Abfragen für die letzten Postings, die Tagcloud sowie für Admins und Moderatoren die mutmaßlichen Spampostings. Der Query für die letzten Postings entspricht dem für den Threadbaum, halt nur mit einem Limit von N Postings, zeitlich sortiert. Die Tagcloud sammelt die Tags, die in einer in den Settings festgelegten Periode von jetzt ab zeitlich abwärts benutzt wurden (functions.inc.php, ab Zeile 1633). Das ist, auch abseits der Datenbankabfrage, bestimmt nicht so performant, da diese als Text beim jeweiligen Posting stehen und nach dem Auslesen gesplittet und verarbeitet werden müssen. Die Funktion wird aber z.B. bei Wendell garnicht benutzt. Benutzt du die Tagcloud?

Nichts weist für mich auf die Ursache der langen Wartezeit hin. Falls hier jemand noch eine Idee hat, immer her damit.

Der Effekt tritt weniger zu Tage wenn, wie zur Zeit um 7:00, wenig Betrieb im Forum ist.

Das bestätigt Wendells Beobachtungen. Hier beobachte ich das nur gelegentlich, z.B. nach dem Absenden eines Postings. Das mag am vergleichsweise wenig frequentierten Forum liegen.

Tschö, Auge

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

Avatar

Performance enhancements?

by Auge ⌂, Thursday, December 26, 2013, 22:56 (3745 days ago) @ Wendell

Hello

Thanks for this discussion, everyone. At least I know I'm doing the right thing by optimizing it often. :)

Much quieter site. Size of the database 12 MB; this year (average daily use):
211 unique visitors, 15 simultaneous connections, 761 visits, 1090 pages, 2815 hits, bandwith 6.28 MB.


For comparison: size of the database is 156 MB, 219,675 unique visitors, 881,148 visits, and 730 pages.

A database with a size of around 150MB should not necessitate the optimisation every few days.

I looked into some of the scripts (mlf2, version 2.3.1).

As an example: thread.inc.php contacts the database 11 times to read, change or delete entries. In the script posting.inc.php I found 78 database calls.

To use the script posting.inc.php as an example. If requested, most of the database calls stay untouched. Some are for special purposes like moving or deleting existent postings, others are nessesary to read and write a posting. many of them read or write into one field in a database table to change a timestamp or to mark or lock a thread. There, where data from different tables were requested, queries works with JOINs and that's optimisation in itself.

If the few queries could be optimised, it would be only a little step forward, but I don't think that it sovles the problem. If you and Wima, who reported the same problems, have to optimise the data structures manually, there must be other bottle necks.

Can you tell us please, what you are doing with what piece of software, when you optimise the database? What are the changes you observe?

Same questions will go in german to Wima.

Tschö, Auge

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

Performance enhancements?

by Wendell, Thursday, December 26, 2013, 23:43 (3745 days ago) @ Auge

Thank you for your follow-up. :)

I'm using phpMyAdmin to optimize all of the database tables every evening.

If I don't optimize even just one evening, the site is noticeably slower. What I mean by that is once I enter the URL into Chrome's address bar, nothing loads for a few seconds (white screen but with spinning icon), then suddenly the whole site loads pretty quickly. The longer I wait between optimizing the database, the longer this initial delay. After a week, it can take 10 to 15 seconds to load, sometimes more, depending on activity. This week there is less traffic than usual because of the holidays, so the effects wouldn't likely be as severe.

I get similar complaints from my users who experience the exact same thing. They get the "waiting for surveyorconnect.com" message in their browser's status bar for several seconds. Some take longer, but I'm sure that is all dependent on each user's internet connection and how good it is at the time.

Thanks again,
Wendell

Avatar

Performance enhancements?

by Auge ⌂, Friday, December 27, 2013, 00:41 (3745 days ago) @ Wendell
edited by Auge, Friday, December 27, 2013, 00:59

Hello

Thank you for your follow-up. :)

I'm using phpMyAdmin to optimize all of the database tables every evening.

I assume you do it in the tab "operations"->"optimize table"[1]. And, as an additional assumption, you do it for every table step by step?

Citation from the MySQL manual:


Use OPTIMIZE TABLE in these cases, depending on the type of table:

  • After doing substantial insert, update, or delete operations on an InnoDB table that has its own .ibd file because it was created with the innodb_file_per_table option enabled. The table and indexes are reorganized, and disk space can be reclaimed for use by the operating system.
  • After deleting a large part of a MyISAM or ARCHIVE table, or making many changes to a MyISAM or ARCHIVE table with variable-length rows (tables that have VARCHAR, VARBINARY, BLOB, or TEXT columns). Deleted rows are maintained in a linked list and subsequent INSERT operations reuse old row positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file. After extensive changes to a table, this statement may also improve performance of statements that use the table, sometimes significantly.

Ok, it's a defragmentation of the physical database file(s) in itself in case of many changes (many postings) and the presence of (many) columns in the database tables with variable length (VARCHAR, VARBINARY, BLOB, or TEXT columns). In our case it's the nature of postings in a forum to be of a variable length. Also the titles of postings, names of posters and other additional informations are intentionally of different (string) lenght for most of the entries.

Maybe the length of some of the data fields can be optimised. Examples: the unique id for a posting (field uniqid) has a lenght of 255 chars but the value has in my test installation only a length 13 chars for every single entry. The field ip has a length of 128 while an IP(v4) can have a maximal length of 15 chars(!) [2]. There are other fields for user data in the entries table with a length of 255 chars. Not in every case this length is suitable.

Especially in case of a posting from a registered user most of these fields stay empty in the entries table. But the fields have to be present because of the ability to post without a registration.

It may be a solution to split the two tables (entries and user data for registered users) into three tables, one for the postings, one for the user data of registered users (both are existing) and a third table for the user data of not registered users with a link to the posting.

If only registered users are allowed to post entries the third table stays empty. If not registered users can post in a forum, the third table replaces the user related fields in the entries table and that only, if the poster is not registered! There is, from a technical view, no change for registered users with this structural change. On the other hand the structure is much more clear and the database size will decrease a bit because there are only entries for user data if they are needed.

This needs a fundametal discussion about the database structure. Alex as the maintainer of the project has to be involved.

If I don't optimize even just one evening, the site is noticeably slower. What I mean by that is once I enter the URL into Chrome's address bar, nothing loads for a few seconds (white screen but with spinning icon), then suddenly the whole site loads pretty quickly. The longer I wait between optimizing the database, the longer this initial delay. After a week, it can take 10 to 15 seconds to load, sometimes more, depending on activity. This week there is less traffic than usual because of the holidays, so the effects wouldn't likely be as severe.

I get similar complaints from my users who experience the exact same thing. They get the "waiting for surveyorconnect.com" message in their browser's status bar for several seconds. Some take longer, but I'm sure that is all dependent on each user's internet connection and how good it is at the time.

Your description of the behaviour is plausible. Thank you for your explanations.

[1] The names for tab and link are reverse translated (by me) from my german phpMyAdmin installation.
[2] When an IP is stored as an integer it would have a length of four bytes. But this needs many changes in the code (especially many many database queries).

Tschö, Auge

[edit]PS: If you optimise the tables from the "structure" tab you'll see a note about ... err ... (maybe) backlog (in german it's called "Überhang", someone with a better translation is invited to correct mine). Can you please tell us the typical values for the affected tables? Thank you.[/edit]

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

Performance enhancements?

by Wendell, Friday, December 27, 2013, 02:35 (3745 days ago) @ Auge
edited by Wendell, Friday, December 27, 2013, 02:43

I assume you do it in the tab "operations"->"optimize table"[1]. And, as an additional assumption, you do it for every table step by step?

In phpMyAdmin, once I select the database, it gives a list of all tables (Structure tab). I use the link under the list to "check all", then use the dropdown to select "optimize table".

Prior to optimization:
[image]

Results page:
[image]

If only registered users are allowed to post entries the third table stays empty.

Yes, I only allow registered users to post.

Avatar

Performance enhancements?

by Auge ⌂, Friday, December 27, 2013, 03:49 (3745 days ago) @ Wendell

Hello

I assume you do it in the tab "operations"->"optimize table"[1]. And, as an additional assumption, you do it for every table step by step?


In phpMyAdmin, once I select the database, it gives a list of all tables (Structure tab). I use the link under the list to "check all", then use the dropdown to select "optimize table".

Ah, ok.

Results page:
[image]

The five tables with "OK" was optimised at this point.

One question: The column "Overhead" (by the way, this is the correct translation for "Überhang") was empty in every row before you optimised the database tables or differed their value from "0" or "-" in the rows of the above named tables? I would expect a byte size greater than 0. Otherwise I don't know what should be optimised.

So, it's 4:45 am here, time to go to bed.

Tschö, Auge

PS: A giant lag of ca. 15 seconds while generating the postings entry!

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

Performance enhancements?

by Wendell, Tuesday, December 31, 2013, 05:58 (3741 days ago) @ Auge

BEFORE:
[image]

AFTER:
[image]

Avatar

Performance enhancements?

by Auge ⌂, Tuesday, December 31, 2013, 19:40 (3740 days ago) @ Wendell

Hello

IMHO could 5kb overhead not cause 15 to 20 seconds waiting time. *head scratching*

Tschö, Auge

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

Performance enhancements?

by Wendell, Thursday, January 02, 2014, 16:51 (3738 days ago) @ Auge

It's bizarre, isn't it?

I did have a short chat with my host who said it's the caching that's the problem. Apparently it's not very efficient.

I also received an email from someone who didn't want to put specific technical information about his website in a public forum (I can understand that), but he recommended that I take a serious look at ZBBlock http://www.spambotsecurity.com/zbblock.php -- it did seem to help reduce some load times but certainly not a complete cure. I think with sites like mine with a bit more traffic, you have to be concerned not only about the threat of spammers, but the fact that they are constantly looking for a place with vulnerabilities. ZBBlock puts a stop to that.

Avatar

ZBBlock

by Alfie ⌂, Vienna, Austria, Thursday, January 02, 2014, 17:18 (3738 days ago) @ Wendell
edited by Alfie, Sunday, January 05, 2014, 02:26

Hi Wendell,

I did have a short chat with my host who said it's the caching that's the problem. Apparently it's not very efficient.

Likely. If I recall it correctly Alex indroduced caching since Geshi turned out to be very slow.* It seems that you don’t use syntax highlighting in your forum. Maybe it’s worthwhile to have a look at some old code of mlf before caching was introduced.

[…] ZBBlock […] -- it did seem to help reduce some load times but certainly not a complete cure.

Maybe. Give it a try. It’s not complicated to set up.

I think with sites like mine with a bit more traffic, you have to be concerned not only about the threat of spammers, but the fact that they are constantly looking for a place with vulnerabilities. ZBBlock puts a stop to that.

I have ZBBlock on my site monitoring registration, login, and the contact form since Nov 2012 – see here. I had ~32,000 blocks last year. The nice thing with ZBBlock is that once an IP is blocked (three attempts) only an HTTP 503 is returned. Traffic = 0. :-D

──────────
* Edit: Wrong. It was already the BBCode parser class (see this post). Alex introduced caching in 2007-05-07 (mlf 2.0 beta 11). Last version without caching was 2.0 beta 10 (2007-04-20).

--
Cheers,
Alfie (Helmut Schütz)
BEBA-Forum (v1.8β)

Avatar

ZBBlock

by Auge ⌂, Saturday, January 04, 2014, 07:23 (3737 days ago) @ Alfie

Hello

I did have a short chat with my host who said it's the caching that's the problem. Apparently it's not very efficient.


Likely. If I recall it correctly Alex indroduced caching since Geshi turned out to be very slow. It seems that you don’t use syntax highlighting in your forum. Maybe it’s worthwhile to have a look at some old code of mlf before caching was introduced.

Please bear in mind, that the hoster may talk about the caching of the webserver and not about the forums intern posting caching. That is technically a significant difference.

[…] ZBBlock […] -- it did seem to help reduce some load times but certainly not a complete cure.


Maybe. Give it a try. It’s not complicated to set up.

I can confirm that.

I think with sites like mine with a bit more traffic, you have to be concerned not only about the threat of spammers, but the fact that they are constantly looking for a place with vulnerabilities. ZBBlock puts a stop to that.


I have ZBBlock on my site monitoring registration, login, and the contact form since Nov 2012 – see here. I had ~32,000 blocks last year. The nice thing with ZBBlock is that once an IP is blocked (three attempts) only an HTTP 503 is returned. Traffic = 0. :-D

Yes, the unnesessary traffic caused by spammers may be one reason for the bad answer time. They request one page after the other, sometimes for several hours, to post their spam at least one per minute. I had two of such attacks in 2013. With ZBBlock or a comparable software you can detect and dump most of these requests (without much work for the forum script) to /dev/null or HTTP 503.

Tschö, Auge

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

ZBBlock

by Wendell, Saturday, January 04, 2014, 17:37 (3736 days ago) @ Auge

Please bear in mind, that the hoster may talk about the caching of the webserver and not about the forums intern posting caching. That is technically a significant difference.

He was actually talking specifically about the forum software's caching system.

ZBBlock

by Wendell, Saturday, January 04, 2014, 17:36 (3736 days ago) @ Alfie

I did actually setup ZBBlock right before my last post above and it does seem to help a little bit.

Avatar

ZBBlock

by Auge ⌂, Friday, January 10, 2014, 19:28 (3730 days ago) @ Wendell

Hello Wendell

I did actually setup ZBBlock right before my last post above and it does seem to help a little bit.

Now, six days later, can you share your first experiences?

Tschö, Auge

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

ZBBlock

by Wendell, Saturday, January 11, 2014, 14:34 (3729 days ago) @ Auge

Now, six days later, can you share your first experiences?

It's definitely not a total fix for performance, but it did make a significant difference. The site is a little snappier now.

Avatar

Table entries vs. no of posts

by Alfie ⌂, Vienna, Austria, Sunday, January 05, 2014, 02:05 (3736 days ago) @ Wendell
edited by Alfie, Sunday, January 05, 2014, 02:38

Hi Wendell,

I’m wondering why both your mlf_entries and mlf_entries_cache have ~ double as many rows as the number of posts shown in the footer of the forum. In my understanding these numbers should be exactly [sic] equal.* Maybe a case for record linkage. If you really have doublets in the DB it would extremely decrease its performance.

──────────
* … if logged in as admin. Do you have ~50% of posts in categories not visible to the public? That would be another explanation.

--
Cheers,
Alfie (Helmut Schütz)
BEBA-Forum (v1.8β)

Table entries vs. no of posts

by Wendell, Friday, January 10, 2014, 17:26 (3730 days ago) @ Alfie

Yes, admins can see a lot more than the public can. I used to have a Politics category and it got out of control, so I made it available only to admins, but kept it for prosperity. Because we have had a competitor make wild accusations and made personal threats toward us, we also keep all "deleted" posts in a hidden category for our records.

Avatar

Archive?

by Alfie ⌂, Vienna, Austria, Saturday, January 11, 2014, 13:55 (3729 days ago) @ Wendell

Hi Wendell,

if you don’t need the luxury of accessing these posts through the forum’s scripts, consider firing up phpMyAdmin. Generate an “archive”-table, copy the respective posts from mlf_entries and delete them from mlf_entries/mlf_entries_cache. Maybe that helps in speeding up the regular forum.

--
Cheers,
Alfie (Helmut Schütz)
BEBA-Forum (v1.8β)

Archive?

by Wendell, Saturday, January 11, 2014, 16:49 (3729 days ago) @ Alfie

if you don’t need the luxury of accessing these posts through the forum’s scripts, consider firing up phpMyAdmin. Generate an “archive”-table, copy the respective posts from mlf_entries and delete them from mlf_entries/mlf_entries_cache. Maybe that helps in speeding up the regular forum.

Good point. I have backed up and deleted the unneeded entries from mlf_entries. However, I don't see any way to associate the mlf_entries_cache with the main entries.

Would it be safe to simply delete all entries from mlf_entries_cache? Would it regenerate the cache on its own or would I be breaking the site?

Thanks for all the help!

P.S. Since I really just wanted a record that I could look back on when needed (which would hopefully be never), I simply exported the entries I didn't need online anymore as a backup. No need really to move them into another table.

Avatar

Archive?

by Alfie ⌂, Vienna, Austria, Saturday, January 11, 2014, 17:12 (3729 days ago) @ Wendell

Hi Wendell,

I have backed up and deleted the unneeded entries from mlf_entries. However, I don't see any way to associate the mlf_entries_cache with the main entries.

Don’t worry, it’s too late. ;-) They should have had identical ids.

Would it be safe to simply delete all entries from mlf_entries_cache? Would it regenerate the cache on its own or would I be breaking the site?

In my understanding [sic] the former. The scripts should look up whether an entry with the same id already exists in mlf_entries_cache, and – if not – generate a new entry with rendered HTML. Untested (I don’t run mlf 2.x). Suggestion: Delete one entry of the cache table in the database, call the post in the forum, and check whether a new entry is generated.

[…] Since I really just wanted a record that I could look back on when needed […], I simply exported the entries I didn't need online anymore as a backup. No need really to move them into another table.

Sounds reasonable.

--
Cheers,
Alfie (Helmut Schütz)
BEBA-Forum (v1.8β)

Avatar

Archive?

by Auge ⌂, Saturday, January 11, 2014, 19:09 (3729 days ago) @ Alfie

Hello

Would it be safe to simply delete all entries from mlf_entries_cache? Would it regenerate the cache on its own or would I be breaking the site?


... The scripts should look up whether an entry with the same id already exists in mlf_entries_cache, and – if not – generate a new entry with rendered HTML.

correct

[…] Since I really just wanted a record that I could look back on when needed […], I simply exported the entries I didn't need online anymore as a backup. No need really to move them into another table.


Sounds reasonable.

This is not a solution by itself. Wendell may have a smoother running forum after deleting a number of archived postings from the database table. But what's with a forum with the (former) number of around 235,000 active postings?

I hope, Alex will join this discussion.

Tschö, Auge

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

Archive?

by Wendell, Tuesday, January 14, 2014, 15:31 (3726 days ago) @ Auge

This is not a solution by itself. Wendell may have a smoother running forum after deleting a number of archived postings from the database table. But what's with a forum with the (former) number of around 235,000 active postings?

I agree, as I will reach that number again soon, and will likely experience more slowdowns. It does seem to get slower and slower as it grows and I keep throwing things at it to help performance. Probably the most helpful solution so far has been the ZBBlock addition. Optimizing the database each night helps a lot, too.

Archive?

by Wendell, Tuesday, January 14, 2014, 15:29 (3726 days ago) @ Alfie

Would it be safe to simply delete all entries from mlf_entries_cache? Would it regenerate the cache on its own or would I be breaking the site?


In my understanding [sic] the former. The scripts should look up whether an entry with the same id already exists in mlf_entries_cache, and – if not – generate a new entry with rendered HTML. Untested (I don’t run mlf 2.x). Suggestion: Delete one entry of the cache table in the database, call the post in the forum, and check whether a new entry is generated.

I have tested this theory and it works. I completely erased all entries in the mlf_entries_cache table and it is repopulating itself. Thank you!

Performance enhancements?

by Schlaefer, Tuesday, January 28, 2014, 09:06 (3712 days ago) @ grognard

I'm not going to pretend I understand the database structure and optimizations there, but on a basic administrative level - is your hosting optimized for the kind of traffic you get? Msql and Apache (if Apache) are rarely ever set up on VPS for 'real' traffic. Also VPS vary tremendously in quality and configuration etc. What is causing the issue? The database size? Parsing tables? Memory usage? CPU? Honestly, compared to most standard php/mysql forum applications MLF is pretty low fi and very tweakable.

Mlf uses barebone PHP (no framework) and is performant. What kills mlf in the end are the DB queries. From what I've seen an average shared hoster installation get's into trouble around 1500+ users and 300.000+ entries, which is admittedly not so "little" anymore.

Mlf also caches rendered HTML in separate tables, which is O.K. if your DB is small, but contributes to the DB bottleneck in the long run.

Performance enhancements?

by simon12, Tuesday, January 07, 2014, 16:32 (3733 days ago) @ Wendell

Thanks for sharing some advice on this discussion,. It really helps me and i know will help others as well.

RSS Feed of thread