MySQL Performance: MySQL => Write buffer

Members

Help

Home

Home » Performance » MySQL » Write buffer

Show: Today's Messages :: Show Polls :: Message Navigator

Write buffer [message #1103]

Mon, 16 April 2007 07:51

geelwortel
Messages: 25
Registered: September 2006

Junior Member

Hi all

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
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

Andrew

Report message to a moderator

Re: Write buffer [message #1121 is a reply to message #1103 ]

Fri, 20 April 2007 18:12

Peter
Messages: 405
Registered: August 2006

Senior Member
Super Guru

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.

Peter Zaitsev, MySQL Performance Expert
MySQL Performance Blog - http://www.mysqlperformanceblog.com
MySQL Consulting http://www.mysqlperformanceblog.com/mysql-consulting/

Report message to a moderator

Re: Write buffer [message #1132 is a reply to message #1121 ]

Mon, 23 April 2007 02:25

geelwortel
Messages: 25
Registered: September 2006

Junior Member

Hi Peter

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.

Andrew

Report message to a moderator

Re: Write buffer [message #1139 is a reply to message #1132 ]

Tue, 24 April 2007 04:55

geelwortel
Messages: 25
Registered: September 2006

Junior Member

I found a server variable in 5.1.x that fixes the problem, write_buffer_size (not the one used for myisamchk), what is the 5.0.x version of this variable?

Report message to a moderator

Previous Topic:	The best way to setup multiple locations?
Next Topic:	Configuring my.cnf

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Tue Jan 6 05:40:06 EST 2009

Total time taken to generate the page: 0.01761 seconds

.:: Contact :: Home :: MySQL Support by Percona.com ::.