Anticipate the load on a MySQL server

[mparak]

Hi,
Our application has local databases and posts to a server. If the server is offline (established by a failed ping) we post local and upload to the server when it is back online. This has served us brilliantly.
The problem lies in the fact that occasionally the server is too busy to respond, but continues to respond to pings. This leads to the the workstations appearing to hang while they wait for the server. If the server were simply offline we would go offline and it would be fine. The fact that it is "there" but busy is not detectable until its too late. The connect timeouts are not useful as the workstations have successfully connected.
What I am looking for is a timeout when I issue a sql command or a way of detecting this "BUSY" state to go offline.
Any advice would be really welcome.

Regards
M

[mparak]

What would be excellent is if we could get feed back from every sql command that takes longer than xx milliseconds. One could then somehow abandon the query and process it later perhaps.

[marsupilami]

Hello mparak,

I have a program where the user decides if he wants to use the online database or offline database. When the user decides for the offline database, I start a separate thread that has its own TZConnection to try to connect to the online database and give a message to the user if it is available.

Maybe this is something you can do? Use the local database, start a separate thread to check for the remote database and then, if everything is well, switch to the remote database?
Best regards,

Jan

[mparak]

Hooba, (marsupilami)

Its not quite what I had in mind.
The Switch from local to server is not the problem.
What we need is the ability to pre assess the response state of the server before we hit it. If we look at the show processlist during a busy period w can see the long sql queries that take too long.
We are by the way able to use the show proceslist to avoid high traffic times.
I was hoping for a more elegant solution.
M

[mdaems]

Oohh, you've got real belgian comics over there! (André Franquin is born within 2 km from my workplace)

Seems to me too that using a separate thread to lauch sql is the only solution then. But I'm not a multithreader, so I don't know how to monitor such a separate thread from the main program, nor do I know if it can be aborted when you decide to switch over to local mode. Is that possible anyway?

Mark

[mparak]

True I really loved his comics.

I know this is a silly question, but, from mdeams comment above maybe its worthwhile asking Jan (marsupalami) to explain to us how he creates a threaded connection.

With a sample. Or example.

This would be an education for me and and I sure for lots of Zeos guys out there.

Warmest Regards
M

[marsupilami]

Hello Mohammed,

for your usecase may be it is the best to connect to the local DB as a default. Then you check your remote database in a separate thread and if everything works out good you might want to change to the remote database.

For checking, if the remote database is available I use this thread:

Code: Select all

unit ThreadServerCheck;

interface

uses
  Classes;

type
  TServerCheckThread = class(TThread)
  private
    { private declarations }
  protected
    ServerFound: Boolean;
    procedure Execute; override;
    procedure UpdateMainForm;
  end;

implementation

uses SysUtils, MainForm, ZConnection, DatenmodulDM, Graphics;

{ Important: Methods and properties of objects in the VCL or CLX can
   only be used in methods, that call Synchronize, for example:

      Synchronize(UpdateCaption);

   where UpdateCaption could look like this:

    procedure TServerCheckThread.UpdateCaption;
    begin
      Form1.Caption := 'Changed in a thread';
    end; }

{ TServerCheckThread }

procedure TServerCheckThread.Execute;
var
  Connection: TZConnection;
  Result: Boolean;
begin
  Result := True;
  // create your own separate connection
  Connection := TZConnection.Create(nil);
  //configure your connection object here
  try
    Connection.Connect;
    // you could do some additional checks here. 
    // You should create your own separate queries here.
    Connection.Disconnect;
  except
    Result := False;
  end;
  FreeAndNil(Connection);

  if Result Then Synchronize(UpdateMainForm); // important: Use Synchronize for calling methods, that do changes in the main thread!
end;

procedure TServerCheckThread.UpdateMainForm;
begin
  // here you can do operations in the context of your main thread. I use it for
  // changing a static text. But you also could set a flag for your main thread to
  // to instruct it to change the database whenever it is convenient to do so.
  frmMain.ConnStateLbl.Caption := frmMain.ConnStateLbl.Caption + ', Server aber vorhanden';
  frmMain.ConnStateLbl.Font.Color := clRed;
  frmMain.ConnStateWarningTxt.Visible := True; 
end;

end.

in the OnCreate event of my main Form I have some small code like this to crate and run the thread:

Code: Select all

procedure TfrmMain.FormCreate(Sender: TObject);
var
  X: TServerCheckThread;
begin
   X := TServerCheckThread.Create(false);
  // from here the server check is running in the background while your main
  // application continues to run
end;

And as for the Marsupilami: I like it too - but I didn't have much time to watch it on TV... ;o)

I hope this helps you. I use this with a Firebird database and Zeos 6 but I think it should work for you too

Best regards,

Jan

[mparak]

I still have to test it but it looks real sweet.
thanks