Hi Seung! Yes, you have to do some tuning to get the performance from a transactional database. You should not use autocommit=1, because then the database has to physically write the log segment to disk after each individual insert. Try to insert your data in a single transaction, only a single commit after the insert, or a few commits on the way. I understand you measured several times, doing DELETE FROM TABLE in between. Innobase uses some CPU to physically remove the delete marked records in a purge operation. DROP TABLE and TRUNCATE TABLE (in MySQL-4.0) are faster. I will also add a small optimization to auto_increment columns. Currently it executes SELECT MAX (KEY) FROM TABLE before each insert, which wastes some CPU and is not necessary. It is better cache the latest inserted key value to main memory, to the data dictionary. The FreeBSD bug is known. I will run tests on our FreeBSD machine in the next few days. Obviously there is something wrong with the FreeBSD port. Was it so that it hung and used 100 % of CPU? That has been reported also from Italy. Best regards, Heikki At 06:11 PM 3/30/01 +0900, you wrote: >Hi Heikki, I have run a few test for innobase tables on linux and BSD > >1. It seems that 'innobase' table was slower than 'bdb' table for 'insert' command, which is surprising(or not? o_O) > >2. It may be due to that fact that I dont' know much about optimazing the 'innobase' table. Do you know how to boost the performance of 'bdb' or 'innobase table'? Please let me know...:( > >3. I mainly tested the innobase table with linux, but I actually want to run mysql with 'bdb' or 'innobase' tables on FreeBSD. > >4. However, mysql just hung when I tried to insert the 10000 rows of data, the same data used for linux test, using 'mysql < data.sql'. I used 'my-large.cnf' and changed the setting as follwings. > >innobase_buffer_pool_size=400M >innobase_additional_mem_pool_size=20M > >FreeBSD setting >4.2 release #1 >pentium 550, 512 RAM, mysql-3.23.35 > >complied with ./configure -- >1. >--prefix=/usr/local/mysql >--with-charset=euc_kr >--with-low-memory >--without-debug >--without-readline >--with-mysqld-ldflags=-all-static >--with-mit-threads=no >--with-client-ldflags=-all-static >--with-innobase >--with-bdb > >2. >--with-charset=euc_kr > >--with-innobase >--with-berkeley-db > >---------------------------------------------------------------------------- > >Linux setting >Redhat 6.2, kernal 2.2.14-5.0, pentium550, 128 RAM, mysql-3.23.35 >complied with ./configure --with-charset=euc_kr --with-berkeley-db --with-innobase > > >Other setting >1. The data used had three columns, 'no', 'name', 'grade' with 'no' as the primary index and auto_increament and grade as a key. There were 10000 rows, and I inserted the data using 'shell>mysql < data.sql'. >2. Whenever I inserted the data, I simply did 'delete from table where no > 0' instead of dropping the table. >2. I used 'my-medium' as 'my.cnf' > >'mysql insert' benchmarking result( all times are in seconds). >1. with key, autocommit=1, flush_log_at_trx=1, all on, basic setting > >myisam > >real 5.61 >real 5.10 >real 5.08 > >innobase > >real 68.57 >real 69.27 >real 83.26 >real 100.87 >real 99.99 >real 102.31 >real 108.82 >real 114.23 >real 124.23 >real 94.42 >real 98.20 > >bdb > >real 56.83 >real 43.49 >real 39.76 >real 54.77 >real 48.75 > >2. with key, autocommit=1, flush_log_at_trx=1, bdb off, basic setting > >myisam > >real 4.48 >real 3.01 >real 3.08 >real 3.08 >real 3.04 > >innobase > >real 70.04 >real 99.04 > >with >innobase_buffer_pool_size=100M >innobase_additional_mem_pool_size=10M > >real 76.60 >real 80.97 >real 81.52 >real 107.47 > >3. with key, autocommit=1, flush_log_at_trx=1, bdb on > >bdb > >real 53.14 >real 54.29<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> ><HTML><HEAD> ><META http-equiv=Content-Type content="text/html; charset=ks_c_5601-1987"> ><META content="MSHTML 5.50.4522.1800" name=GENERATOR> ><STYLE></STYLE> ></HEAD> ><BODY bgColor=#ffffff> ><DIV><FONT size=2> ><DIV><FONT size=2>Hi Heikki, I have run a few test for innobase tables on linux >and BSD</FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>1. It seems that 'innobase' table was slower than 'bdb' table >for 'insert' command, which is surprising(or not? o_O)</FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>2. It may be due to that fact that I dont' know much about >optimazing the 'innobase' table. </FONT><FONT size=2> Do you know how >to boost the performance of 'bdb' or 'innobase table'? Please let me >know...:(</FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>3. I mainly tested the innobase table with linux, but I >actually want to run mysql with 'bdb' or 'innobase' tables on >FreeBSD.</FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>4. However, mysql just hung when I tried to insert the 10000 >rows of data, the same data used for linux test, using 'mysql < >data.sql'. I used 'my-large.cnf' and changed the setting >as follwings. </FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT >size=2>innobase_buffer_pool_size=400M<BR>innobase_additional_mem_pool_size= 20M</FONT></DIV> ><DIV><FONT size=2> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>FreeBSD setting</FONT></DIV> ><DIV><FONT size=2>4.2 release #1</FONT></DIV> ><DIV><FONT size=2>pentium 550, 512 RAM, mysql-3.23.35</FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>complied with ./configure --</FONT></DIV> ><DIV><FONT size=2>1.</FONT></DIV> ><DIV><FONT size=2>--prefix=/usr/local/mysql <BR>--with-charset=euc_kr ><BR>--with-low-memory <BR>--without-debug <BR>--without-readline ><BR>--with-mysqld-ldflags=-all-static<BR>--with-mit-threads=no</FONT></DIV> ><DIV><FONT size=2>--with-client-ldflags=-all-static </FONT></DIV><FONT >size=2></FONT></FONT></DIV> ><DIV><FONT size=2><FONT size=2> ><DIV>--with-innobase</DIV> ><DIV>--with-bdb</DIV> ><DIV> </DIV> ><DIV>2. </DIV> ><DIV>--with-charset=euc_kr <BR> ><DIV>--with-innobase</DIV> ><DIV>--with-berkeley-db </DIV> ><DIV> </DIV> ><DIV>---------------------------------------------------------------------- ------</FONT></DIV></DIV></FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>Linux setting</FONT></DIV> ><DIV><FONT size=2>Redhat 6.2, kernal 2.2.14-5.0, pentium550, 128 RAM, >mysql-3.23.35</FONT></DIV> ><DIV><FONT size=2>complied with ./configure --with-charset=euc_kr >--with-berkeley-db --with-innobase</FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>Other setting</FONT></DIV> ><DIV><FONT size=2>1. </FONT><FONT size=2>The data used had three columns, 'no', >'name', 'grade' with 'no' as the primary index and auto_increament and >grade as a key. </FONT><FONT size=2>There were 10000 rows, and I inserted the >data using 'shell>mysql < data.sql'.</FONT></DIV> ><DIV><FONT size=2>2. Whenever I inserted the data, I simply did 'delete from >table where no > 0' instead of dropping the >table. </FONT></DIV> ><DIV><FONT size=2>2. I used 'my-medium' as 'my.cnf'</FONT></DIV> ><DIV><FONT size=2></FONT><FONT size=2> </DIV></FONT> ><DIV><FONT size=2>'mysql insert' benchmarking result( all times are in >seconds).</FONT></DIV> ><DIV><FONT size=2>1. with key, autocommit=1, flush_log_at_trx=1, all on, basic >setting</FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>myisam</FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>real 5.61<BR>real 5.10<BR>real 5.08</FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>innobase</FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>real 68.57<BR>real 69.27<BR>real 83.26<BR>real 100.87<BR>real >99.99<BR>real 102.31<BR>real 108.82<BR>real 114.23<BR>real 124.23<BR>real >94.42<BR>real 98.20</FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>bdb</FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>real 56.83<BR>real 43.49<BR>real 39.76<BR>real 54.77<BR>real >48.75</FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>2. with key, autocommit=1, flush_log_at_trx=1, bdb off, basic >setting</FONT></DIV> ><DIV><FONT size=2></FONT> </DIV> ><DIV><FONT size=2>myisam</FONT></DIV> ><DIV> </DIV> ><DIV><FONT size=2>real 4.48<BR>real 3.01<BR>real 3.08<BR>real 3.08<BR>real >3.04</FONT></DIV> ><DIV> </DIV> ><DIV><FONT size=2>innobase</FONT></DIV> ><DIV> </DIV> ><DIV><FONT size=2>real 70.04<BR>real 99.04</FONT></DIV> ><DIV> </DIV> ><DIV><FONT size=2>with ><BR>innobase_buffer_pool_size=100M<BR>innobase_additional_mem_pool_size=10M </FONT></DIV> ><DIV> </DIV> ><DIV><FONT size=2>real 76.60<BR>real 80.97<BR>real 81.52<BR>real >107.47</FONT></DIV> ><DIV> </DIV> ><DIV><FONT size=2>3. with key, autocommit=1, flush_log_at_trx=1, bdb >on</FONT></DIV> ><DIV> </DIV> ><DIV><FONT size=2>bdb</FONT></DIV> ><DIV> </DIV> ><DIV><FONT size=2>real 53.14<BR>real >54.29</FONT></DIV></FONT></DIV></BODY></HTML> --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
