I have an application that reads and writes when producing a report for ±200 clients monthly data. The reading is done to collect data and the writing is done to store data in a self made temp. table for later use. This process happens 1 per client that the report is used for. The reading is fine but the writing to the table times out and stop the report from running. On win 98 I don’t have a problem, on Win 2000 it takes 45 seconds (everytime) before I get the time out and on Win XP it takes 15 seconds (everytime) to timeout. These timeout values stays the same everytime I run this big report. If I use 50 clients the report takes ± 2 min to run but completes on all client OSes.
I’m thinking that I have problems with a write buffer becoming full and the server is trying to flush the buffer but can’t. Is this possible or what else can be wrong.
Server: 3.23.48 max (tried v4.x and v5.0 same problem)
Memory: 4 Gig
CPU: P4 2.8 Intel
OS: Suse LINUX
Client ODBC Driver: 3.51.14 (happens on all ODBC drivers after 3.51.06
Thank you for your help
I do not get what the problem is.
Can you describe it in more exact terms on MySQL level, such as I run this query and it stalls or something similar.
You need to tell us what happens on MySQL level.
The report runs for ± 200 clients, for each client 3 select queries and 3 insert/update queries runs, the first gets the client information and writes to a self made temp. table. The second query gets the workers for that client and updates the temp. table. The third query gets the hours and pay rates per worker and and then updates the temp. table. The results are calculated and then the report are displayed.
The clients(various Windows OS) starts the report and the server(Linux) process the report and provide the answers. The client connects with ODBC 3.51.12/14. The problem is that this process/report runs for ± 15 seconds on XP, 45 seconds Win 2000 BUT 98 have no problems. When we run this report from a XP/2000 client the connection drops with error 10048 can’t connect to the server and then the server is unavailable for all connection for ± 1min. We tracked all queries passed to the server and every time the connection is dropped when a updated/insert query is passed, this is why I think that the server have a problem with a write buffer.
I hope this explains it beter.