Re: bytea memory improvement - Mailing list pgsql-jdbc

Kris Jurka wrote: <blockquote cite="midPine.BSO.4.63.0609260131040.29854@leary2.csoft.net" type="cite"><br /><br /> On
Wed,23 Aug 2006, Luis Vilar Flores wrote: <br /><br /><blockquote type="cite">  To all that already forgot the first
emails,I developed an modified version of the method toBytes from the org.postgresql.util.PGbytea class.  The old
methoduses 3 buffers to translate the data from the nework to the client, this uses too much memory.  My method only
uses2 buffers, but does one more pass through the original buffer (to calculate it's final size). <br /><br
/></blockquote><br/> I'm not super impressed with these timing results.  They are certainly showing some effects due to
GC,consider the rise in time here at 10.5MB. <br /></blockquote> Well, thanks a lot for the attention. My main purpose
wasto reduce the memory footprint. But, before I did the tests, I had the idea that the new method would be slower than
theolder ... So it would only be better on large files, i.e. where the reduced memory usage was more important than raw
speed.This was because of the extra cycle through the array.<br /><blockquote
cite="midPine.BSO.4.63.0609260131040.29854@leary2.csoft.net"type="cite"><br /><blockquote type="cite">OLD method: <br
/>size: 9.5MB execute+next: 804ms getBytes: 377ms used mem: 66169KB <br /> size: 10.5MB execute+next: 634ms getBytes:
546msused mem: 73112KB <br /> size: 11.5MB execute+next: 689ms getBytes: 450ms used mem: 80057KB <br /> size: 12.5MB
execute+next:748ms getBytes: 482ms used mem: 87001KB <br /></blockquote><br /> I came up with my own contrived
benchmark(attached) that attempts to focus solely on the getBytes() call and avoid the time of fetching results, but it
doesn'tgive really consistent results and I haven't been able to come up with a case that actually shows the new method
wasfaster even with 30MB of data.  This is on Debian Linux / 2xOpteron 246 / jdk 1.5.0-05. <br /></blockquote> The new
methodis very similar to the old, but it just computes the final size before the copy. The old method does less
instructionsto convert an array, the new method is only faster when the older is slowed down by garbage
collection/memoryallocation.<br /><blockquote cite="midPine.BSO.4.63.0609260131040.29854@leary2.csoft.net"
type="cite"><br/> I've committed this to CVS HEAD with a rather arbitrarily set MAX_3_BUFF_SIZE value of 2MB.  Note
thatthis is also the escaped size, so we may actually be dealing with output data a quarter of that size.  If anyone
coulddo some more testing of what a good crossover point would be that would be a good thing. <br /></blockquote> I
thinkthe old option should be there for a while, but I hope that the new method proves to be as fast as the old, so we
canjust discard the MAX_3_BUFF_SIZE and always compute the final size - as the method code would be clearer that
way.<br/><blockquote cite="midPine.BSO.4.63.0609260131040.29854@leary2.csoft.net" type="cite"><br /> Thanks for your
patiencewith this item. <br /></blockquote> It's me who thanks for such a great product ...<br /> I will check the new
benchmark,the see the memory usage, and garbage collection ...<br /><blockquote
cite="midPine.BSO.4.63.0609260131040.29854@leary2.csoft.net"type="cite"><br /> Kris Jurka<br /><pre wrap="">
 
<hr size="4" width="90%" />
import java.sql.*;

public class ByteaTest2 {
public static void main(String args[]) throws Exception {    Class.forName("org.postgresql.Driver");    Connection conn
=DriverManager.getConnection("jdbc:postgresql://localhost:5432/jurka","jurka","");
 
    for (int k=0; k<5; k++) {        long t1 = System.currentTimeMillis();        long total = 0;
        for (int j=0; j<10; j++) {            PreparedStatement pstmt = conn.prepareStatement("SELECT
varcharsend(repeat(?,?))");           pstmt.setString(1, "a\\001");            pstmt.setInt(2, 150000);
ResultSetrs = pstmt.executeQuery();            rs.next();            for (int i=0; i<100; i++) {                byte
b[]= rs.getBytes(1);                total += b.length;            }
 
            rs.close();            pstmt.close();        }        long t2 = System.currentTimeMillis();
System.out.println(t2-t1);   }}
 
} </pre></blockquote><br /><br /><div class="moz-signature">-- <br /></div><p><font color="#7da647"><font
face="Verdana,sans-serif"><font size="2" style="font-size: 10pt;"> Luis Flores </font></font></font><p><font
color="#7da647"><fontface="Verdana, sans-serif"><font size="2" style="font-size: 8pt;"> Analista de
Sistemas</font></font></font><p><ahref="http://www.evolute.pt"><font face="Verdana, sans-serif"><font size="2"
style="font-size:8pt;"><b>Evolute</b> - Consultoria Informática<br /><br /></font></font></a> <font
color="#7da647"><fontface="Verdana, sans-serif"><font size="2" style="font-size: 8pt;"> Email: </font></font></font> <a
href="mailto:lflores@evolute.pt"><fontface="Verdana, sans-serif"><font size="2" style="font-size:
8pt;">lflores@evolute.pt</font></font></a><p><font color="#7da647"><font face="Verdana, sans-serif"><font size="2"
style="font-size:8pt;"> Tel: (+351) 212949689</font></font></font><div style="text-align: justify;"><font
color="#7d7d7d"><fontface="Verdana, sans-serif"><font size="1" style="font-size: 7pt;"><br /> AVISO DE
CONFIDENCIALIDADE</font></font></font><br/><font color="#7d7d7d"><font face="Verdana, sans-serif"><font size="1"
style="font-size:7pt;"> Esta mensagem de correio electrónico e eventuais ficheiros anexos são confidenciais e
destinadosapenas à(s) pessoa(s) ou entidade(s) acima referida(s), podendo conter informação privilegiada e
confidencial,a qual não poderá ser divulgada, copiada, gravada ou distribuída nos termos da lei vigente. Caso não seja
odestinatário da mensagem, ou se ela lhe foi enviada por engano, agradecemos que não faça uso ou divulgação da mesma. A
distribuiçãoou utilização da informação nela contida é interdita. Se recebeu esta mensagem por engano, por favor
notifiqueo remetente e apague este e-mail do seu sistema. Obrigado. <br /></font></font></font><font
color="#7d7d7d"><fontface="Verdana, sans-serif"><font size="1" style="font-size: 7pt;"> </font></font></font><br
/><fontcolor="#7d7d7d"><font face="Verdana, sans-serif"><font size="1" style="font-size: 7pt;"> CONFIDENTIALITY
NOTICE</font></font></font><br/><font color="#7d7d7d"><font face="Verdana, sans-serif"><font size="1" style="font-size:
7pt;">This e-mail transmission and eventual attached files are intended only for the use of the individual(s) or
entity(ies)named above and may contain information that is both privileged and confidential and is exempt from
disclosureunder applicable law. If you are not the intended recipient, you are hereby notified that any disclosure,
copying,distribution or use of any of the information contained in this transmission is strictly restricted. If by any
meansyou have received this transmission in error, please immediately notify the sender and delete this e-mail from
yoursystem. Thank you. </font></font></font></div>