Stored Procedures and Triggers

[vantage255]

Has anyone attempted to convert any backend tasks into MySQL stored procedures?

I am working on a few to move data from large threads into archived threads, but has anyone else started working with this? What results have you seen?

[Paul M]

Sorry, we still run mysql4 which doesn't have them.

[Zachery]

I', waiting abit longer before I move to mysql5 due to some crashing bugs in 5.0.21, I'll hold off for .25 or at least untill 21 gets its issues fixed.

[vantage255]

Fair enough. I am testing 5.1 at the moment. I am very interested in the addition of clustering, now that it uses disk instead of ram.
One of the side effects is that I now have stored procedures. So I started testing. It looks like archiving thread data is pretty easily done. I'm experimenting with how many posts to move per query now. It looks like 100 is a decent numbet, but I am concerned that, on a big board, you might have several threads being archived in the same time frame.
I think this might need to be tuned per board.

[Erwin]

I'm using 5.1 - are there bugs with it? I've had 1 instance where mysql hung on me.

[vantage255]

It seams to be pretty stable for me.
Im not having any issues.

I really wanted to get a good idea of the performance Vs. 4.0 and I wanted to try out the native clustering. Now that it uses HD space instead of ram it may actualy be useable

[COBRAws]

I just upgraded to 5.1, can you inform me how to do this? Im insterested.

[vantage255]

Im not so good a tutorial writer, But I do have a few relivant links.

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Thats the MySQL trigger docs. Pretty decent docs if you are used to the way Mysql.org writes there stuff.

I also picked up the new Oreilly "MySQL: stored procedures" book. Its pretty easy to grok.

and I havent done anything with the new load ballancing setup yet though. Its my understanding that the config is pretty much teh same as it is for 5.0. but you can specify HD space to use for the cache.

[Erwin]

I'm going to downgrade from 5.1 back to 4.1 - for some reason mysql has been acting up recently and I don't know why, but suspect this may be the case.

It's harder to downgrade than it looks - with the size of my database import/export takes hours.

This is a good reference:
http://dev.mysql.com/doc/refman/5.0/en/downgrading-to-4-1.html

I'm still downgrading...

[COBRAws]

Im not so good a tutorial writer, But I do have a few relivant links.

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Thats the MySQL trigger docs. Pretty decent docs if you are used to the way Mysql.org writes there stuff.

I also picked up the new Oreilly "MySQL: stored procedures" book. Its pretty easy to grok.

and I havent done anything with the new load ballancing setup yet though. Its my understanding that the config is pretty much teh same as it is for 5.0. but you can specify HD space to use for the cache.
Thanks James

[Erwin]

LOL! You can downgrade from MySQL 5 to 4 but you lose all your auto-incremental keys. Which means all your postids, threadids, userids etc is all lost.

So in the end, I upgraded back to MySQL 5.1 and for fun, upgraded to PHP 5.1 as well. The only snag with PHP 5 is an APC bug, but that's easily worked around.

My advice for vB users - once you upgrade MySQL, don't try downgrading. :) It's a path of pain and suffering.