These days I’m working with a customer who has an application based entirely on stored routines on MySQL side. Even though I haven’t worked much with stored procedures, I though it’s going to be a piece of cake. In the end – it was, but there’s a catch.
My initial idea was – I’ll just analyze queries in the slow query log generated by our mysql build running with long_query_time=0, get the slowest ones and work on them. It wasn’t really all the way I expected..
For a showcase I have created a function “whatstheweatherlike”. Let’s call it and see what shows up in the slow query log:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | mysql> SELECT whatstheweatherlike(5); +----------------------------------------------------------+ | whatstheweatherlike(5) | +----------------------------------------------------------+ | Its 5°C, time is 02:28:05, feels [like] almost summer! | +----------------------------------------------------------+ 1 row in set (0.00 sec) host # tail /var/log/mysql/mysql-slow.log # Time: 090115 2:25:28 # User@Host: me[me] @ localhost [] # Thread_id: 3432 Schema: test # Query_time: 0.000695 Lock_time: 0.000284 Rows_sent: 1 Rows_examined: 0 Rows_affected: 0 Rows_read: 1 # QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No # Filesort: No Filesort_on_disk: No Merge_passes: 0 # No InnoDB statistics available for this query SELECT whatstheweatherlike(5); |
Not overly useful for query optimization, is it? Well, I can still aggregate the slow query log with mk-log-parser and find routines that MySQL spends most of the time on, but I don’t want to go over the complicated logic of stored procedure just to figure out what queries are being executed. This is where Jeremy’s SHOW PROFILE patch – which is included in all Community tree based Percona MySQL builds since it is part of MySQL community version ever since 5.0.37 (note – not all Percona builds are done on Community tree) – comes into play. Let me show you how this works:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT whatstheweatherlike(3); +-----------------------------------------------------------+ | whatstheweatherlike(3) | +-----------------------------------------------------------+ | Its 3°C, time is 02:43:25, feels [like] snow is melting | +-----------------------------------------------------------+ 1 row in set (0.00 sec) mysql> show profiles; +----------+------------+-------------------------------------------------------------+ | Query_ID | Duration | Query | +----------+------------+-------------------------------------------------------------+ | 1 | 0.00005100 | SELECT CURTIME() into time | | 2 | 0.00014100 | SELECT feeling INTO feels FROM weather WHERE temp = in_temp | +----------+------------+-------------------------------------------------------------+ 2 rows in set (0.00 sec) |
So now not only do we get to see what queres were executed, how much time they spent executing but we can also get a profile for each of the queries individually. I think this is pretty cool and it helped me a lot. Sure, you may still need to check routine and track the logic so having constants that were used rather than variables would be even more useful. Nevertheless I think this is a great way to find what’s happening under the scenes so you could start digging deeper.
On the other hand, this way of manual profiling you can’t really see real time stats, for example if it was slow because of locks, in the logs you may not see it. I will ask my colleagues to check if it would be hard to implement configurable variable for switching between routine and query logging, so maybe one day we’ll be able to have both ways of looking at it.
Is there a practical benefit to using stored procedures in a production app these days? I can see the potential for greater security (since things like injection attacks get harder), but that can be solved with careful coding practises as well e.g. if I escape my SQL properly or use prepared statements I’m largely immune to SQL injection attacks.
Which leaves the theoretical benefit of greater efficiency since a stored procedure can access data with a latency of zero and grind over it faster than a client app which is running at the far end of a network link, but are folks really seeing a benefit here?
In other words are a lot of folks actually building on top of stored procedures these days? Is it working out?
Answer to Pat:
Well first like you say, you have security, for example, we have an log/register. We have one stored procedure/function wich allow adding data to the table. The function is runned under an own user account, wich is the only account who has writing priviliges to this table, making it impossible to anyone to delete any data from our log.
secondly, i prefer to have all my querys in the databas, the application using the databas should’n be able to determine wih data he will recive. if a query need to changes, you change the query on one place only.
@Pat
I know it’s probably a bit late for comments, but I have something to add based on my experience.
In a network where the web and database servers are separate and traffic volume is high (which is the case in most large organizations), it makes sense to localize any and all intermediate queries on the same database server. We have this problem where 500 queries every second slows the web server down by a very high margin (5-10 seconds a request). So, if you can make few queries from the web servers and have all the local (optimized) processing on the database server, network traffic is reduced to minimum possible.
Nice tip.
I would love to see the full query though…
There is no ‘show full profile’ like ‘show full processlist’ to see the full SQL… ?