Hi! I am usally not asking for help. Normally, I am figuring out my problems by myself but I have a seriouse problem I am not sure how to handle. And I think my requirements are kind of special. Also my database "skills" aren't that good and maybe my problem is just a newby one but I hope you are willing to help me.
I need to program a kind of data distributor that got data from several devices and publish them over an OPC-Ua interace to other clients. I got data from about 100 to 1000 devices, usually a bunch of data all 10ms. These network packets consist of 10 to 50 different values, lets call them data items. The requirements are: Parsing the network packets and publish the data to the correct OPC-UA Structure (called namespace). This namespace is a kind of n-ary tree structure. Furthermore, if the program exits ( also abnormaly - e.g. blackout or computer shutdown) the data sent at last should be loaded into the namespace after next startup. Therefore, I have to do a kind of persistent data storage. Optimally in an atomic way - so if the server crashes while writing to hard disk and the data got corrupted, max. one device should be reinitialized with "standard" values. By now, I am writing the data to XML-files and if one device (identical to an XML file) can't be read at startup, the whole device is reinitialized in the namespace by getting the needed information from the name of the XML file (for example device_0012_12342.xml). This is, of course, very inefficient and slow because I have to write a whole XML file of about 100 to 400kB every time I got a network packet-update for a device and these network packets are only part of the data of a device. After googling around, I found h2, since it seems to be the best choice for my problem. However, after testing a bit, I found some drawbacks I don't know how to handle: -I'd like to stuck with the structure I got: Every device has 1 file (e.g. database). -If only one row is corrupt I have to reinitialize the device as a whole (determined by the system - it is quite complex and this is the safest way). What exceptions can I except if one row or the whole db is corrupt? -I found that opening a connection to a db takes quite a "long" time. Ideally, I'd like to write every data item seperately to the corresponding database because of information hiding - It would be nice to implement the db write whenever one data item is updated. This doesn't seem to be possible to me because of the fast updates I have to process. ( every 10 ms about 10 to 50 "newly opened" connections seems to be utopical). Therefore, I'd like to update one db after receiving a whole packet. This seems to be more realistic but opening the connection, preparing the statements (preparedStamenet), writing and closing the connection seems to be a very process intensive action too. -So I tried to open several (1000) connections to the databases at startup and appending them to every device object but it seems that they are very memory intensive. With standard configuration, they are taking about 2 gig of ram and 2 gig of hard disk. I have already changed the cache size with the "CACHE_SIZE=" parameter. I cropped it down to 200 MB RAM (with CACHE_SIZE=50 [KB]) but I am afraid that this drops the write speed more than opening connection does. - I also read about connection pools but I do not know how they are working and i am not sure if they could solve my problems because I got the data packets not "ordered". So I need to open the databases nearly arbitrarily. -Another issue i am facing is what happens when the server crashes while DBs are locked? I do not have to lock the DBs since my program is the only one writing to them, so should i switch of lock mode? It seems that formally locked databases are opened very slowly after a crash. Could you provide me with hints wether h2 db is the correct choice and if you think so, how I could handle the task? Thank you very much in advance. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
