List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:April 27 1999 4:38pm
Subject:Compression with CLIENT_COMPRESS protocol ?
View as plain text  
>>>>> "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
Thread
Compression with CLIENT_COMPRESS protocol ?BAUMEISTER Alexandre27 Apr
  • Re: Compression with CLIENT_COMPRESS protocol ?unknown27 Apr
    • Re: Compression with CLIENT_COMPRESS protocol ?Paul DuBois27 Apr
      • Re: Compression with CLIENT_COMPRESS protocol ?unknown27 Apr
    • Re: Compression with CLIENT_COMPRESS protocol ?Michael Widenius28 Apr
  • Compression with CLIENT_COMPRESS protocol ?Michael Widenius28 Apr
    • Reaction: Compression with CLIENT_COMPRESS protocol !!!BAUMEISTER Alexandre28 Apr
      • Reaction: Compression with CLIENT_COMPRESS protocol !!!Michael Widenius28 Apr
        • Re: Reaction: Compression with CLIENT_COMPRESS protocol !!!BAUMEISTER Alexandre28 Apr
        • Stupid question!!Ryan28 Apr
          • Re: Stupid question!!Darrell Shifflett28 Apr
            • RE: Stupid question!!Ryan28 Apr
Re: Compression with CLIENT_COMPRESS protocol ?Fred Lindberg27 Apr