I am working in a company where we have an ERP system running on a dedicated server with LAMP (2 x Octa-Core E5-2620 V4 2.1GHz w/HT 32 threads with 32GB of RAM and two 480 GB SSD in hardware based RAID 1, 20 GB dedicated to MySQL), the database currently has a size of about 16 GB, according to me the MySQL configurations are fine, but if you require them, I post them here. Some colleagues use a lot the Stored Procedures (SP) in MySQL 5.7, There are many details with SP, like deadlocks and other details related to concurrency and performance. In the normal queries everything is fine. Can you please tell me if it is advisable to use the SP in MySQL 5.7 or not? Or why SPs have so many failures in MySQL 5.7?
This is bound to be a subjective question, and answers will tend to be opinion-based, which is more or less discouraged by the site’s guidelines. However, I will try to give an answer that is based in facts.
It is common in other brands of SQL database (e.g. Oracle or Microsoft SQL Server) to develop a robust set of stored procedures as a data layer for your application. However, it’s not as common in the MySQL community. Stored procedures are not as mature in MySQL as they are in commercial databases.
MySQL stored procedures have a lot of disadvantages compared to other implementations:
- No debugger. Some developer tools attempt to mimic a debugger by inserting debug logging statements within the body of the stored routine.
- No compiler. Stored routines are compiled just-in-time by each thread that uses them. Compiled routines are not shared between threads. If a routine is modified, the compiled version is discarded in all threads and must be recompiled. If more than 256 routines are used in a given thread, all 256 are discarded and must be recompiled.
- No support for packages.
- No standard library provided of procedures and functions that comprise an application framework.
- No official task-oriented manual for developing stored routines. There is reference documentation only.
Disadvantages that may not be limited to MySQL (may apply to other brands just as much):
Developers on your team are probably very productive with their primary programming language, e.g. Java, Go, Python, etc. They undoubtedly are less productive with the unfamiliar MySQL stored routine language.
Stored routines run on the database server, not on application servers. Modern application architecture relies on scaling out to multiple servers as much as possible. You can scale out application code more easily than a database server, or even push logic out to clients (i.e. browsers). It’s not possible to scale out if a lot of your business logic is in stored routines.
If you need to scale out the database by sharding, it’s not easy to make stored procedures query any shard besides the one they are running on.
I consulted for a site whose developers were PHP experts, but they struggled with coding and debugging and optimizing MySQL stored procedures. Their PHP application servers were nearly idle, whereas the CPU was overloaded on the database server. Their application code was in fact waiting most of the time for elaborate stored procedures to run, and they were limited on their ability to scale up because there’s a finite amount of computing horsepower on the database server.
For these reasons, I hardly ever use stored procedures, except for infrequent administrative tasks. Not as part of the application’s core transactional work.
(This does not disagree with Bill’s answer.)
I sometimes find Stored Routines (Procedures and Functions) useful:
- Structured coding for a frequently used task.
- Structured coding for isolating a task.
- Speed up a set of SQL statements that need to be run on a ‘remote’ server. This amortizizes the network latency.
I do not find them to be slower than equivalent code. I do not see more deadlocks. (Both sound like sloppy coding, not something intrinsic with SPs.) I would be interested to hear of specific examples.
As with any coding style, there are tradeoffs; once you become proficient in the alternatives, you should pick the best technique for the task.
As Bill says, MySQL and MariaDB provide little if any reason to rush to use Stored Routines.
Cursors can only be used in Stored Procs. But don’t use them! Instead, think through how you can use the power of SQL to apply something to an entire set instead of walking through rows one at a time.