mysql stored procedure performance issue

I've this situation

2 of the same DB, one on DEV server the other on PROD server ( entirely separated server )

My web application is running on the DEV server together with the DEV DB. My stored procedure runs 0.300seconds-0.600seconds on DEV DB, However the same procedure runs 3-12secs on the PROD Server.(from the same web app on the DEV server) Both DB structures,indexes and data are the same. A significant delay.

Only difference is version DEV - 5.0.67-community PROD - 5.1开发者_运维知识库.54-community

For normal sql statements, the delay are not so huge in difference.

What could be a likely cause(s) ? Also will setting mysql cache improve performances?

Cheers

BTW, let me add on, my stored proc is creating temporary tables for other proc to use data. Production DB is also connected to the same web app on another PROD webserver that has incoming traffic i suppose. But how come this only happens for my stored procedure. My other queries or normal queries doesnt not have significant difference in timing.

Could deadlock cause delay?

DEADLOCK was detected, 
OS WAIT ARRAY INFO: reservation count 17394257, signal count 14459620
--Thread 6600 has waited at G:\mysql-5.1.54-winbuild\mysql-community-nt-5.1.54-build\storage\innobase\
Mutex at 02878180 created file .\dict\dict0dict.c line 713, lock var 1
waiters flag 1
Mutex spin waits 0, rounds 367825986, OS waits 15398330
RW-shared spins 2245011, OS waits 810719; RW-excl spins 2893516, OS waits 820381
------------------------
LATEST DETECTED DEADLOCK
------------------------
110408  9:05:45
*** (1) TRANSACTION:
TRANSACTION 0 203543446, ACTIVE 0 sec, OS thread id 6584 fetching rows
mysql tables in use 2, locked 2
LOCK WAIT 761 lock struct(s), heap size 60736, 30170 row lock(s)

Things to look at:

• compare the query plans you get from DEV and PROD
• size of the tables in PROD compared to DEV
• the load on the PROD server - is the cpu maxed out? is there a lot of wait i/o?
• contention and locking in PROD