>>>>> "BAUMEISTER" == BAUMEISTER Alexandre <alex@stripped>
> writes:
BAUMEISTER> Bonjour,
BAUMEISTER> A program of mine is continuously updating a remote MySQL database
BAUMEISTER> table with a text file once per minute. Until last week my program
BAUMEISTER> was doing this :
BAUMEISTER> - gzip compression of the text file
BAUMEISTER> - sending the .gz to the remote server using FTP
BAUMEISTER> - http request on a cgi on the remote server which :
BAUMEISTER> - gzip -d the text file
BAUMEISTER> - LOAD DATA INFILE to the table.
BAUMEISTER> This worked quite well but I found it no 'clean'.
BAUMEISTER> I had to program that in the past because when I first wrote that
BAUMEISTER> prog. there was not "LOAD DATA _LOCAL_ INFILE".
BAUMEISTER> Next week, I upgraded to MySQL-3.22.21 and decided to use the LOAD
BAUMEISTER> DATA LOCAL to update the remote table.
BAUMEISTER> My new prog. is connecting to the remote MySQL server with the
BAUMEISTER> CLIENT_COMPRESS protocol.
BAUMEISTER> But it seems that the CLIENT_COMPRESS protocol compresses a lot less
BAUMEISTER> than gzip.
BAUMEISTER> I have MRTG scanning the bandwidth used by the server which is
BAUMEISTER> sending the data to the remote server. And compared to last week
BAUMEISTER> when my prog was using gzip+ftp to send the data, the new LAOD DATA
BAUMEISTER> LOCAL program uses twice more bandwidth and then the transfer delay
BAUMEISTER> is more important.
BAUMEISTER> Is it normal ? Is there a plan to optimize the CLIENT_COMPRESS
BAUMEISTER> protocol in order to have more rapid transfers ? Why not to use the
BAUMEISTER> gzip algorithm (it's GNU isn't it ?) ?
MySQL does use the gzip algorithm for the compression. The difference
is that MYSQL does a separate gzip for each package to keep things
safe.
You can get better compression simply by increasing the
'max_allowed_packet' variable in your client.
Have you checked that client/server protocol is really compressed?
(In other words: Are you sure your server and client both are
compiled with compression ?)
BAUMEISTER> Another question :
BAUMEISTER> - When a client is doing a 'LOAD DATA INFILE' command, during the
BAUMEISTER> INSERT, all other clients doing select on that table are locked.
BAUMEISTER> What I wonder is if when using 'LOAD DATA LOCAL INSERT', are the
BAUMEISTER> others clients locked while the data are sent from the client to
BAUMEISTER> the server or are they locked only once the data are all
BAUMEISTER> transferred and the server is doing the insert ?
MySQL inserts the rows at the same time it gets them; In other words,
your other clients are locked during the whole transfer.
Another option is to generate INSERT statements with many rows; In
this case you your other clients can continue to work between inserts.
Regards,
Monty
