PDA

View Full Version : Stored Procedures and Triggers


vantage255
27 May 2006, 00:28
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
27 May 2006, 03:03
Sorry, we still run mysql4 which doesn't have them.

Zachery
27 May 2006, 06:28
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
27 May 2006, 18:06
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
29 May 2006, 01:15
I'm using 5.1 - are there bugs with it? I've had 1 instance where mysql hung on me.

vantage255
29 May 2006, 16:39
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
29 May 2006, 17:53
I just upgraded to 5.1, can you inform me how to do this? Im insterested.

vantage255
29 May 2006, 19:01
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
30 May 2006, 00:22
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
31 May 2006, 04:37
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
31 May 2006, 08:45
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.