| Subject | Compiling stored procedure while database in production |
|---|---|
| Author | |
| Post date | 2016-03-17T02:43:05Z |
I had a strange coincidence today, but I'm wondering if it actually may have been cause/effect instead.
I'm running fb_inet_server.exe 2.5.2.26539 on Windows 2008 Server. Earlier today I created a simple "for select do update" stored procedure against a database that may have had 20-25 open connections from applicaiton servers/web servers. Compiling the stored proc seemed to take a very long time - I would have expected about 5 seconds, but it still hadn't finished after a couple of minutes.
In the meantime I jumped on a couple of other tasks, and then started getting support calls from users that the applications were non-responsive. I saw that the DB usage had grown from the typical 1.5GB to 3.5GB, and was running at a steady 12% CPU. It stayed that way for a while, so I opened another copy of my DB management app and connected. I immediately tried to take a look at MON$STATEMENTS to see What was running, but the query never returned results. I could not connect to the DB after that, as even ISQL just hung when trying to start.
After 30 minutes I had to reboot the server to bring the system back up, with hopefully no damage to the DB. There are no entries in Firebird.log that indicate what the original issue was, and sweeps that have occurred since seem normal.
I'm now wondering whether compiling that simple stored proc could have been the cause of all of this? I don't do major development during normal production hours, but I have certainly created or altered stored procedures many many times while this database was online without issue.
Thanks,
Bob M..
I'm running fb_inet_server.exe 2.5.2.26539 on Windows 2008 Server. Earlier today I created a simple "for select do update" stored procedure against a database that may have had 20-25 open connections from applicaiton servers/web servers. Compiling the stored proc seemed to take a very long time - I would have expected about 5 seconds, but it still hadn't finished after a couple of minutes.
In the meantime I jumped on a couple of other tasks, and then started getting support calls from users that the applications were non-responsive. I saw that the DB usage had grown from the typical 1.5GB to 3.5GB, and was running at a steady 12% CPU. It stayed that way for a while, so I opened another copy of my DB management app and connected. I immediately tried to take a look at MON$STATEMENTS to see What was running, but the query never returned results. I could not connect to the DB after that, as even ISQL just hung when trying to start.
After 30 minutes I had to reboot the server to bring the system back up, with hopefully no damage to the DB. There are no entries in Firebird.log that indicate what the original issue was, and sweeps that have occurred since seem normal.
I'm now wondering whether compiling that simple stored proc could have been the cause of all of this? I don't do major development during normal production hours, but I have certainly created or altered stored procedures many many times while this database was online without issue.
Thanks,
Bob M..