Hello everyone,
Thanks for your attention and I will attempt to provide a good amount of detail here.
We have an application that writes (INSERTS and UPDATES) data to a Pervasive v9 database. It connects to the database through the ODBC layer. The application and Pervasive ("the database") exist on the same PC. The application essentially receives real time information from the network, decides if that data type exists in the database (though an SQL SELECT lookup) and then either issues a batch of INSERT statements or UPDATE statements. Each network update (1 packet) can trigger up to 30 INSERTS or UPDATES, so in times of heavy activity the load can be marginally high (50% of CPU cycles for the NTDBMGR process).
What eventually happens is that at some point, Pervasive seems to "freeze". We notice our client applications no longer see data, or the data is no longer refreshing. The database connecting application seems to think it's writing to the database fine (well, it's passing data to the ODBC layer fine) and everything is hunky dory. The MKD files at this point grow endlessly, even with just UPDATE statements they grow. I have seen them grow to 8 X 2 GB files (roughly 16 GB of data, the original tables are a few megs of actual data) before we have caught the problem.
If I fire up the Pervasive Control Center, I cannot access the tables. If I access the table the Pervasive Control Center goes unresponsive, and I have to restart the Pervasive services in order to use the Pervasive Control Center again. I can access other databases fine using Pervasive CC, its just this particular database/table that is giving us the trouble. If I attempt to delete the table Pervasive Control Center freezes. Unfortunately when this happens I am forced to create a new database, create new tables, and start fresh. I can then restart the PC and manually delete the errant MKD (and MK*) files to free up the hard drive room.
We have experienced this on four separate computers, so it's not an isolated incident. Since we're using ODBC, I have switched two of the offending PC's to a competitors open source database (whose name shall not be mentioned here, I imagine that's taboo) and we have yet to experience these sorts of issues.
Any suggestions? I know that's sort of vague, but in general, I wonder if anyone has experiences weird issues using the ODBC layer on a machine with a heavy load?
Thanks! Matt