I am going to use java and mysql for my application, but I'm not really sure how to connect for the best performance.
A short background to what the application will do: capture ip packages and insert extracted data into a database with up to 100 packets per second peak time. there might be the need of some ip -> int, mac -> long conversions when inserting..if that is more effective than storing just strings. Basically one package would consist of timestamp,ip,mac1,mac2.
So..I have been thinking, since I would use a consumer / producer pattern, with one queue, and only one thread doing database inserts I was thinking that no connection pooling would help..I would just open a connection once and keep it open, since i am capturing and inserting live. Also..since I would be using some conversions maybe stored procedures would be the best...and then just JDBC, and no extra layer above it.
Does it sound reasonable using JDBC (and mysql myisam) using stored procedures for this kind of application? Would it be better to use batch updates instead of stored procedures and take 1000 at a time or just do on the fly with stored procedures?
Thanks, good stuff, so according to the best pracitices I do not really need store procedures, prepared statements are good enough for my application. However, on all examples of preparedstatements they are always prepared and closed just before and after the magic happens. Wouldn't it be better to prepare the statement once when the class is loaded, and then reuse this statement all over without closing it?
Also, it didn't help me answering whether I should keep my database connection open all the time or close it every time I have done something with the database. I ahve only one database client and it needs to enter information continuesly.
subject: Recommendations connecting to the database