[RC] Automated MySQL Table Optimization

A place for MOD Authors to post and receive feedback on MODs still in development. No MODs within this forum should be used within a live environment!
Scam Warning
Skinny Vinny
Registered User
Posts: 230
Joined: Tue Dec 01, 2009 7:10 pm

[RC] Automated MySQL Table Optimization

Post by Skinny Vinny »

Modification Name: Automated MySQL Table Optimization
Author: Skinny Vinny

Modification Description: Adds automated mysql table optimization. Optimizes ALL tables of connected database, including custom mod tables and site tables from phpBB integration.
Modification Version: 1.0.1

Requirements: This modification is for boards/sites running on mysql databases only! Other dbs require a different modification. Built in safety to only run on mysql installations.


Modification Download: http://svmods.com/download/svmods_optim ... tabase.zip

I happened to notice the 'phpBB optimization' mod in these forums and thought that although their idea/intention was good, their application of such a mod was incorrect. There's no reason this can't be automated and encompass the entire database.

This is another hook. Well, kind of: it doesn't 'hook' into anything, but being named as a hook and placed in the hook directory, the script is included in phpBB execution. It only runs on cron execution (when IN_CRON defined), and defaults to only running once a week.

The hook builds a list of every table in your database, and optimizes them all. The functionality of your site/boards are not interrupted during optimization: the db reads from the temp tables optimization creates during execution, and update/insert queries should pend completion. At most, activity during optimization may result in a few second delay in loading. This is only when the optimization query is executing.

Update, just adding text to explain mysql optimization:
OPTIMZE TABLE should be used if you have deleted a large part of a table or if you have made many changes to a table with variable-length rows (tables that have VARCHAR, BLOB or TEXT columns). Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space.

OPTIMIZE TABLE works by making a temporary copy of the original table. The old table is copied to the new table (without the unused rows), then the original table is deleted and the new one is renamed. This is done in such a way that all updates are automatically redirected to the new table without any failed updates. While OPTIMIZE TABLE is executing, the original table is readable by other clients. Updates and writes to the table are stalled until the new table is ready.
If you wish to change the frequency after installation, simply update the config table (find config name svmods_optimize_db_freq) to the number of days you wish between optimization. Very large and high traffic boards may wish to do this daily. Smaller boards far less frequently.

Should any issues arise from the use of the hook as scripted, please let me know immediately so that I can address them. Please give feedback regardless, as I believe little testing is required beyond my own tests and it should be safe to move this along to an rc.
Last edited by Skinny Vinny on Tue Aug 10, 2010 5:14 pm, edited 8 times in total.

Skinny Vinny
Registered User
Posts: 230
Joined: Tue Dec 01, 2009 7:10 pm

Re: [BETA] Automated MySQL Table Optimization

Post by Skinny Vinny »

Corrected hooking issue.
Dl file updated, thoroughly tested on my dev boards.
Let me know if any issues arise.

Skinny Vinny
Registered User
Posts: 230
Joined: Tue Dec 01, 2009 7:10 pm

Re: [RC] Automated MySQL Table Optimization

Post by Skinny Vinny »

FYI, took a moment to pack this up and submit it to the mod db.
It's been running on my demo board since July 20th without incident.
Load the file, purge the cache, have a nice day.
Runs from within the native cron.

User avatar
VSE
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 5095
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman
Contact:

Re: [RC] Automated MySQL Table Optimization

Post by VSE »

Looks good, nice and simple!

For what it's worth, it might be denied by the MOD team because it does not follow phpBB's coding guidelines.
http://code.phpbb.com/svn/phpbb/branche ... codelayout

It should take all of 2 minutes to adjust it to follow their coding layout. ;)
🧩 My Extensions 🧩 ✨ YES...they ALL work with phpBB 3.3!! ✨
Please do not PM me for support.

Skinny Vinny
Registered User
Posts: 230
Joined: Tue Dec 01, 2009 7:10 pm

Re: [RC] Automated MySQL Table Optimization

Post by Skinny Vinny »

I like that they admit it's trivial.
If they wish to add line breaks and repack, they may, but they'll find I do not throw every opening block bracket onto its own line. It's a terrible waste of space on my screens, and it's not something I'm likely to adopt anytime soon. To me, it's painful to look at. Everyone has their preferences, but if that's their grounds for exclusion from the mod db....

This one wouldn't be too bad, as it's so short and simple. But the fb connect mod I submitted.... oh hell no lol. Again, they can add them and repack if they want, but I'm not coding that way. They may suggest their preferences, but making them requirements would be going way too far.

I'm not bragging or trying to promote myself, but I'm no hack. Some of the mods I have planned for phpbb are essential for modern applications and communities. I'd hate to think that phpbb would be chasing off community contributors like me over something so trivial. That's exactly what would happen. PhpBB in general is on shakey ground: a lot of bad reputation, and way behind the times on plugins and modules. Looks to be heading in the right direction, but it's not a good time to be pushing off contributors lol.

I'm happy to give back to the phpbb community, but really? There's nothing in it for mod developers to release to the public through the mod db. In fact, we would be far better off providing them ourselves. Offering support for our own work in our own works. There's no reward here: just good karma from giving back to the community. However long it takes, or whatever reason they have for exclusion.... if it's an error or legitimate issue, I'll handle it. If it's something as dumb as line breaks... well I guess that's between phpbb and its users.

Point: if it's that important to them, they can do it lol. I'm half tempted to remove the repack permission on principle alone! If my work is ever to be excluded because I didn't add 100 line breaks... what a shame, have a nice day :)

User avatar
Sshadow
Registered User
Posts: 299
Joined: Thu Aug 20, 2009 3:54 pm
Name: Chris C.
Contact:

Re: [RC] Automated MySQL Table Optimization

Post by Sshadow »

Very nice and simple mod. I would also hope something so trivial would not exclude good mods from the DB. Our community needs to be more open to good coders and modders not chase them away.

Abera
Registered User
Posts: 674
Joined: Mon Jan 15, 2007 1:26 am

Re: [RC] Automated MySQL Table Optimization

Post by Abera »

Don't kick Skinny to the curb over bloated coding on the way the syntax of an addon that he doesn't do, or use. He saved me alot of time and another addon that didn't work to redirect my site to a html with everything I wanted and it worked once he told me where to put everything. Maybe coding and thinking out of the box is the new way to go? ;)

Skinny Vinny
Registered User
Posts: 230
Joined: Tue Dec 01, 2009 7:10 pm

Re: [RC] Automated MySQL Table Optimization

Post by Skinny Vinny »

For the record, because I don't want anyone thinking I'm hostile or refusing to make mod teams life any easier... my position is that it's very stupid for something like what was suggest to be the conditions for denial to mod db.

The files I pack up are separate from the files I work with. Would it be a big deal for me to run down through those copies and add those line breaks? Not really. Beta/RC versions from the svmods site will most often not have these breaks for the reasons I stated before. The more code I can get on my screen, the better... but that's MY preference.

I have to repack the connect hook today as 1.0.2 as a minor issue was found in templates with group management, and since I'm doing it anyways I'm switching it to the async js. I may run down through with my enter key (will be amusing to see how many lines it adds), and may repack this one while I'm at it.

User avatar
VSE
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 5095
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman
Contact:

Re: [RC] Automated MySQL Table Optimization

Post by VSE »

Vinny, this one is very simple, and I already did it too...

It's just:
-braces on new lines
-spaces between all assignment and arithmetic operators and concoctenations
-use of single quotes except when variables are used in the string

I'll PM you the file.
I'd love to see this one get accepted into the MODDB here. :)
🧩 My Extensions 🧩 ✨ YES...they ALL work with phpBB 3.3!! ✨
Please do not PM me for support.

Skinny Vinny
Registered User
Posts: 230
Joined: Tue Dec 01, 2009 7:10 pm

Re: [RC] Automated MySQL Table Optimization

Post by Skinny Vinny »

Seems you can't replace/update the file in mod queue until it's acted upon. I posted message with url to updated pack if they want to use it instead.

Ty for the edits :)

User avatar
VSE
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 5095
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman
Contact:

Re: [RC] Automated MySQL Table Optimization

Post by VSE »

NP... As for the MOD queue, you can simply re-submit your MOD to it (as a "new revision"), and they will know to drop the older version in favor of the new one when they review it. :)
🧩 My Extensions 🧩 ✨ YES...they ALL work with phpBB 3.3!! ✨
Please do not PM me for support.

User avatar
RMcGirr83
Recognised Extension Developer
Posts: 21034
Joined: Wed Jun 22, 2005 4:33 pm
Location: Your display
Name: Rich McGirr
Contact:

Re: [RC] Automated MySQL Table Optimization

Post by RMcGirr83 »

It's in the process of being tested so I don't believe you can upload a new one until that process is completed.

BTW and FWIW, spacing/tabbing is not deny worthy it's just easier to read when like that (helps us help you). :)
In times of change, learners inherit the earth, while the learned find themselves beautifully equipped to deal with a world that no longer exists - Eric Hoffer
Former Modifications/Extensions Team Member | My extensions
Appreciate the extensions/mods/support then buy me a beer
All requests for support via PM will be ignored

Skinny Vinny
Registered User
Posts: 230
Joined: Tue Dec 01, 2009 7:10 pm

Re: [RC] Automated MySQL Table Optimization

Post by Skinny Vinny »

Yep yep. VSE, I tried adding as a revision and get a trigger_error screen of "You already have a revision in the validation queue. You must wait until the previous revision is approved or denied to submit a new one."

That's not cool lol.

mut2nt
Registered User
Posts: 47
Joined: Sun Feb 21, 2010 8:22 pm
Location: HELL
Name: none mut2nt
Contact:

Re: [RC] Automated MySQL Table Optimization

Post by mut2nt »

i got this error ...
A general error occured: General Error
» SQL ERROR [ mysql4 ]

Duplicate entry 'svmods_optimize_db_freq' for key 1 [1062]

An sql error occurred while fetching this page. Please contact an administrator if this problem persists.

User avatar
VSE
Extensions Development Coordinator
Extensions Development Coordinator
Posts: 5095
Joined: Sat Jan 17, 2009 9:37 am
Location: Los Angeles, CA
Name: Matt Friedman
Contact:

Re: [RC] Automated MySQL Table Optimization

Post by VSE »

I have a question about timeouts.

Most servers are set to a max execution time of 30 seconds in their php.ini file.

I know my database is big and usually takes at least 3~4 minutes to optimize.

Would this script therefor timeout if I were to use it? And were it to timeout during a table optimization, would that possibly corrupt the table?

(I was at one time working on a database optimizer/repairer for phpBB too, which I abandoned because of concerns over this, which I could never find answers for.

Edit: I think I will bring mine back to life. Researching this, I found a way around any php timeout issues :) )
🧩 My Extensions 🧩 ✨ YES...they ALL work with phpBB 3.3!! ✨
Please do not PM me for support.

Locked

Return to “[3.0.x] MODs in Development”