Обсуждение: Inserts disappear after some time under high load
Hello, We run a high load site in which teachers create and assign Quizzes that students take. On an average we have about 150 to 200 students taking tests concurrently not to forget teachers using our reports feature to generate reports. We have explored all other possibilities and are exploring the possibility that the JDBC driver might be at fault for the problem we are facing. Any help/suggestions are welcome. The problem is with table inserts not visible after some time. We confirmed that the inserts went in earlier by logging the primary key that we got from the DB. After some time the record itself disappears. The problem occurs irrespective of whether we do things in a transaction or not. So we don't set autocommit false or do any rollback either. We also don't get any exceptions for the first insert not going through. Moreover, we have noticed that this happens ONLY under high load. Our setup is as follows. Postgresql 7.3.4 pg73jdbc3 driver Redhat 9.0 Apache/Tomcat 3.3.1 We don't use any connection pooling. Problem description: There are 2 tables in the DB . One which stores quiz session information like ipaddress,date_taken etc (quiz_session) and the other which stores student responses (quiz_responses) . ============ ============ quiz_session quiz_responses =========== ============ session_id(PK) quiz_responses_id(PK) date_taken session_id(FK) etc.. responses ...etc When the student starts the quiz we create a entry in the quiz_session table by first selecting the nextval from the quiz_session_session_id sequence(session_id being the primary key). We store this session_id in a variable(temp_session_id) and do an insert into quiz_session for that student. This statement never throws an exception and the value returned by the execution of this query is never less than 1. We print out the session_id we received to a log file. We store the session_id we got in the first step in the Tomcat session in an object for later use when the students atart answering questions one by one. When the student answers a question we try to insert the same session_id to the quiz_responses table as a foreign key along with his responses. The constraints are already set up. This proceeds normally under normal load but throws a referential integrity exception under high load. We also log this insert into the other table to a file and found out that the session_id which we tried to insert is the same one that we got in the first step. Moreover when we go back and check the quiz_session table for the record having this session id, the record itself is not found and that can mean that the first insert didn't go through at all. But what baffles us is why we didn't get SQL exception at that point. We want to find out whether the semantics of the transaction are affected by high load and whether the DB driver can be a possible reason for this kind of behaviour.. Any help is highly appreciated. Thanks, Rajiv
Just to add: We also make sure that the connection,statements are closed properly everytime. Rajiv > Hello, > We run a high load site in which teachers create and assign Quizzes that > students take. On an average we have about 150 to 200 students taking > tests concurrently not to forget teachers using our reports feature to > generate reports. > > We have explored all other possibilities and are exploring the possibility > that the JDBC driver might be at fault for the problem we are facing. Any > help/suggestions are welcome. > > The problem is with table inserts not visible after some time. We > confirmed that the inserts went in earlier by logging the primary key that > we got from the DB. After some time the record itself disappears. The > problem occurs irrespective of whether we do things in a transaction or > not. So we don't set autocommit false or do any rollback either. We also > don't get any exceptions for the first insert not going through. Moreover, > we have noticed that this happens ONLY under high load. Our setup is as > follows. > Postgresql 7.3.4 > pg73jdbc3 driver > Redhat 9.0 > Apache/Tomcat 3.3.1 > We don't use any connection pooling. > > Problem description: > There are 2 tables in the DB . One which stores quiz session information > like ipaddress,date_taken etc (quiz_session) and the other which stores > student responses (quiz_responses) . > > ============ ============ > quiz_session quiz_responses > =========== ============ > session_id(PK) quiz_responses_id(PK) > date_taken session_id(FK) > etc.. responses ...etc > > > When the student starts the quiz we create a entry in the quiz_session > table by first selecting the nextval from the quiz_session_session_id > sequence(session_id being the primary key). We store this session_id in a > variable(temp_session_id) and do an insert into quiz_session for that > student. This statement never throws an exception and the value returned > by the execution of this query is never less than 1. We print out the > session_id we received to a log file. > > We store the session_id we got in the first step in the Tomcat session in > an object for later use when the students atart answering questions one by > one. > > When the student answers a question we try to insert the same session_id > to the quiz_responses table as a foreign key along with his responses. The > constraints are already set up. This proceeds normally under normal load > but throws a referential integrity exception under high load. We also log > this insert into the other table to a file and found out that the > session_id which we tried to insert is the same one that we got in the > first step. Moreover when we go back and check the quiz_session table for > the record having this session id, the record itself is not found and that > can mean that the first insert didn't go through at all. But what baffles > us is why we didn't get SQL exception at that point. > > > We want to find out whether the semantics of the transaction are affected > by high load and whether the DB driver can be a possible reason for this > kind of behaviour.. > > Any help is highly appreciated. > Thanks, > Rajiv > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
Is your code always using the commit() method on Connection objects instead of a Statement execution for "commit"? The latter technique is often applied by people who aren't experienced with JDBC, but it will generate inconsistent update behavior. Just checking... rajiv@altec.org wrote: >Hello, >We run a high load site in which teachers create and assign Quizzes that >students take. On an average we have about 150 to 200 students taking >tests concurrently not to forget teachers using our reports feature to >generate reports. > >We have explored all other possibilities and are exploring the possibility >that the JDBC driver might be at fault for the problem we are facing. Any >help/suggestions are welcome. > >The problem is with table inserts not visible after some time. We >confirmed that the inserts went in earlier by logging the primary key that >we got from the DB. After some time the record itself disappears. The >problem occurs irrespective of whether we do things in a transaction or >not. So we don't set autocommit false or do any rollback either. We also >don't get any exceptions for the first insert not going through. Moreover, >we have noticed that this happens ONLY under high load. Our setup is as >follows. >Postgresql 7.3.4 >pg73jdbc3 driver >Redhat 9.0 >Apache/Tomcat 3.3.1 >We don't use any connection pooling. > >Problem description: >There are 2 tables in the DB . One which stores quiz session information >like ipaddress,date_taken etc (quiz_session) and the other which stores >student responses (quiz_responses) . > >============ ============ >quiz_session quiz_responses >=========== ============ >session_id(PK) quiz_responses_id(PK) >date_taken session_id(FK) >etc.. responses ...etc > > >When the student starts the quiz we create a entry in the quiz_session >table by first selecting the nextval from the quiz_session_session_id >sequence(session_id being the primary key). We store this session_id in a >variable(temp_session_id) and do an insert into quiz_session for that >student. This statement never throws an exception and the value returned >by the execution of this query is never less than 1. We print out the >session_id we received to a log file. > >We store the session_id we got in the first step in the Tomcat session in >an object for later use when the students atart answering questions one by >one. > >When the student answers a question we try to insert the same session_id >to the quiz_responses table as a foreign key along with his responses. The >constraints are already set up. This proceeds normally under normal load >but throws a referential integrity exception under high load. We also log >this insert into the other table to a file and found out that the >session_id which we tried to insert is the same one that we got in the >first step. Moreover when we go back and check the quiz_session table for >the record having this session id, the record itself is not found and that >can mean that the first insert didn't go through at all. But what baffles >us is why we didn't get SQL exception at that point. > > >We want to find out whether the semantics of the transaction are affected >by high load and whether the DB driver can be a possible reason for this >kind of behaviour.. > >Any help is highly appreciated. >Thanks, >Rajiv > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > >
On 16/10/2003 10:40 rajiv@altec.org wrote: > Hello, > We run a high load site in which teachers create and assign Quizzes that > students take. On an average we have about 150 to 200 students taking > tests concurrently not to forget teachers using our reports feature to > generate reports. > > We have explored all other possibilities and are exploring the > possibility > that the JDBC driver might be at fault for the problem we are facing. Any > help/suggestions are welcome. > > The problem is with table inserts not visible after some time. We > confirmed that the inserts went in earlier by logging the primary key > that > we got from the DB. After some time the record itself disappears. The > problem occurs irrespective of whether we do things in a transaction or > not. So we don't set autocommit false or do any rollback either. What about the autocommit value in postgresql.conf? Have you changed that to false (very bad idea with JDBC)? > We also > don't get any exceptions for the first insert not going through. > Moreover, > we have noticed that this happens ONLY under high load. Define high load. How many transactions per second? > Our setup is as > follows. > Postgresql 7.3.4 > pg73jdbc3 driver > Redhat 9.0 Should be ok > Apache/Tomcat 3.3.1 Ugh! Why are you using something that obsolete (and probably quite buggy). My advice is upgrade to the latest 4.1.x version. Apart from anything else, you'll find it many times faster :) > We don't use any connection pooling. So what exactly do you do? How do you ensure that you don't exceed max-connections? > [snip] > > We want to find out whether the semantics of the transaction are affected > by high load and whether the DB driver can be a possible reason for this > kind of behaviour.. My guess is that something in your application is not thread-safe. Make sure that connections can't be used by more that one request at a time (hint: use Tomcat's connecftion pooling). -- Paul Thomas +------------------------------+---------------------------------------------+ | Thomas Micro Systems Limited | Software Solutions for the Smaller Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +------------------------------+---------------------------------------------+