Made some changes to the database setup

[Husker]

Do people see any improvements in performance on the site? I think it is faster, but on this crappy laptop, it is pretty hard to tell.

I am hoping that with these changes, the database corruption we have been seeing (about every month), will be a thing of the past (fingers crossed).

H.

[beowoulfe]

Thanks, I for one would have blown myself up or poisoned myself by now if it weren't for this site.

[Ayay]

Well done Husker! Everything I click opens instantly. Writing a post, submitting it, then editing it and seeing it updated in the forum all happens without delay.

[The Baker]

Don't know if it is what you just did but it is going like a rocket tonight.
Which is good because I can glance at the posts and quickly move on to the ones that are relevant to what I do.
Thanks.

[Husker]

I am hopeful that with the database 'tuning' that things will work a lot faster, and actually perform better with our hosting system, than it did before. The DB will not cache a lot more in memory, thus hitting the physical disk a lot less (in theory). From the small amount of time, since I have made this change, things do seem very snappy. Response time for rendering a full page, even on my horrible system is well under a second, and that is for just about any page. Prior to this, it varied, between 1 and 5s (sometimes longer). We were also having crashing of certain tables, causing the site to go down, about monthly. That is the main concern I have. If that gets fixed, and the site gets sped up as a bonus, then I will be very happy. I am very hopeful, that is the exact situation we have now.

H.

[rad14701]

These tweaks do seem to be making the boards more responsive... You must have been making changes when I was getting errors last night... HAven't seen any today, so far... Fingers crossed...

[Husker]

The crash of the phpbb3_sessions db last night, triggered my digging into what can be done to optimize the DB.

WARNING, techno babble to follow. This may put most people to sleep.

'Fixing' (rebuilding), the sessions DB is pretty trivial. Also, pretty safe, because if there is a bad record, and it is lost, it does not really matter. That user will simply get a new session key the next time they log in. The only time this 'could' make any difference at all, is if a bot is using the sid (session id), in it's file naming (i.e. a bot that is multi-socketing, but trying to keep a single session 'view' to the server), then until that bot rolls through the changed pages it is pulling, and re-pulls the forum index pages, it will be downloading 0 byte files. But for 'normal' users, this is never a problem. They simply get a new server session, and proceed forward.

However, EVER having to rebuild that table (and having it crash consistently), was showing that 'something' was not right in the DB. Yes, easy to fix, but we should not be having to do that time and time again.

I am really hoping that the DB tweaks have solved the problem. We were having multiple threads hitting REAL files, at the same time, locking them (just a hypothesis by me, but I think it is a very likely cause of the problem). Now, most of those read/writes will be done into memory cache, and thus, will not have the file locking issues to cause problems. The DB will flush any real writes out to disk, when the DB is not 'busy'. As long as the updates and commits are handled properly (and they 'should'), then more caching should help resolve these type issues, IF it really is this issue.

Fingers still crossed .....

Note, if this DOES work much better, I may remove all time aging of session files, within phpbb, and simply write a cron script, to perform an update, and scrap all sessions, that are more than a week old. Removing the 'cleanup' from happening within phpbb, causes that update to happen only when my cron script runs (probably once a day), compared to happening every page load (and possibly more often than that), if phpbb is used to age those records.

H.

[rad14701]

Husker, your techno-babble makes sense to me... But, then again, we're IT Geeks...

[rtalbigr]

Husker, your techno-babble makes NO sense to me, but the site is much faster, pages are coming up almost instantly, so thanks for all your hard work!

Big R

[Samohon]

There is a substantial speed improvement H, at least on my Archlinux rig...

Was wondering why the site was giving me errors too last night...Thanks H, its all been for the best...