Developer/DBA
who works on Oracle /SQL Server thinks MySQL implement stored procedure in a same
way as Oracle/MS SQL Server. They incorrectly believe that stored procedures
are compiled and stored in a global stored procedure cache, similar to the
stored procedure cache in Microsoft SQL Server] or Oracle[
This
is wrong. Flat-out incorrect.
Here
is the truth: Every single connection to the MySQL server maintains it’s
own stored procedure cache.
This
means two very important things that users of stored procedures should
understand:
- If you operate in a shared-nothing environment — for example, the majority of PHP and Python applications that do not use connection pooling or persistent connections — if your application uses stored procedures, the connection is compiling the stored procedure, storing it in a cache, and destroying that cache every single time you connect to the database server and issue a CALL statement
- If you use stored procedures, the memory usage of every single connection that uses those stored procedures is going to increase, and will increase substantially if you use many stored procedures
Ooops, I Invalidated Everything Again
So,
what happens when you CREATE, ALTER, or DROP any stored
procedures? Since MySQL stores all stored procedure execution code on the
connection threads, each of those connection threads must invalidate the
procedure in its caches that has changed, right?
No,
it’s worse. Every time ANY stored procedure is added, dropped, or
updated, ALL stored procedures on ALL connection threads will be invalidated
and must be re-compiled. Here is how the “caches” are invalidated:
from
/sql/sp_cache.cc, lines 193-197, in
MySQL 5.5
/*
Invalidate all routines in all caches.
SYNOPSIS
sp_cache_invalidate()
NOTE
This is called when a VIEW definition is
created or modified (and in some
other contexts). We can't destroy sp_head
objects here as one may modify
VIEW definitions from prelocking-free SPs.
*/
void sp_cache_invalidate()
{
DBUG_PRINT("info",("sp_cache: invalidating"));
thread_safe_increment(Cversion,
&Cversion_lock);
}
It’s
a bit misleading, since it actually doesn’t invalidate anything at all. What
the above code does is increment the global “Cversion” variable. When a
connection thread attempts to execute, drop or insert a new procedure, it will
notice that it’s local cache’s version number is less than this Cversion number
and will destroy the entire cache and rebuild it gradually as procedures are
affected or executed.
So, Should You Use Stored Procedures in
MySQL?
The
above warning doesn’t necessarily mean that you should never use
stored procedures? No. What it means (besides being a bit of a rant on the
implementation of MySQL’s stored procedures) is that you should be aware of
these issues and use stored procedures where they make the most sense:
- When you know that you will be executing the stored procedure over and over again on the same connection — for instance, in a bulk loading script or similar
- When you know that you will not be disconnecting from the MySQL server at the end of script execution — for instance, if you use JDBC connection pooling
- When you know that you have a limited number of stored procedures and the memory usage of connections won’t be an issue
Finally,
if you see benchmarks that purport to show a huge performance increase from
using stored procedures in MySQL, be careful to understand what the benchmark
is doing and whether that benchmark represents your real-world environment.
For instance, if you see a huge performance increase in sysbench when using
stored procedures, but you have a PHP shared-nothing environment, understand
that those benchmark results mean very little to you, since sysbench
connections don’t get destroyed until the end of the run…
Reference
http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/
No comments:
Post a Comment