http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html <http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html> http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables/ <http://www.mysqlperformanceblog.com/2006/06/09/why-mysql-could-be-slow-with-large-tables/> http://bugs.mysql.com/bug.php?id=9544
<http://bugs.mysql.com/bug.php?id=9544>These were few links that I had referred. I would say that above link helped me upto _some_ extend to reach towards the solution. I have spent good time with "Synaptic Package Manager" and finally observed that mysql-server-5.0 was referring to few of the libraries that in general referred by mysql-server-5.1. So simply I have degraded those libraries version and after this change I have found good results. BTW, this was tough one to sort out. ;) This is all I have to say at the moment. :-) Thanks! -- Ashish On Mon, Feb 21, 2011 at 10:45 PM, varun bhansaly <[email protected]>wrote: > Hi Ashish, > Can you please share those links as I have been facing similar issues with > MySQL 5.x & Ubuntu ? > > On Mon, Feb 21, 2011 at 10:34 PM, Ashish Vijaywargiya < > [email protected]> wrote: > > > Alright, After reading few articles on MySql server setup I have managed > to > > get the solution of my problem. Actually changes in my.cnf was not needed > > as > > I was using code base for development purpose. The issue was related to > > package dependency in MySql. I have corrected few package version and my > > problem got resolved. > > > > Thanks Mike & BJ for your help! > > > > -- > > Ashish > > > > On Mon, Feb 21, 2011 at 2:40 AM, Mike <[email protected]> wrote: > > > > > Your key buffer is pretty gigantic. Try these: > > > > > > # > > > # * Fine Tuning > > > # > > > key_buffer = 64M > > > max_allowed_packet = 640M > > > thread_stack = 192K > > > thread_cache_size = 8 > > > max_connections = 256 > > > table_cache = 2400 > > > table_definition_cache = 2400 > > > max_heap_table_size = 512M > > > tmp_table_size = 512M > > > join_buffer_size = 5M > > > thread_concurrency = 10 > > > query_cache_limit = 64M > > > query_cache_size = 128M > > > > > > > > > On Sun, Feb 20, 2011 at 12:00 PM, Ashish Vijaywargiya > > > <[email protected]> wrote: > > > > Hello Mike, > > > > > > > > Not much difference. > > > > > > > > Here is the output of recent "ant run-install' command. > > > > > > > > [java] 2011-02-21 01:25:02,929 (main) > > > > [EntityDataLoadContainer.java:408:INFO ] =-=-=-=-=-=-= Finished the > > data > > > > load with 15353 rows changed. > > > > [java] 2011-02-21 01:25:02,929 (main) [ > > > > ContainerLoader.java:113:INFO ] Shutting down containers > > > > > > > > BUILD SUCCESSFUL > > > > Total time: 19 minutes 10 seconds > > > > ashish@ashish-laptop:~/ofbiz_dev/testing$ > > > > > > > > I am sharing the content of my.cnf file so that you can better > comment. > > > > > > > > ashish@ashish-laptop:~/ofbiz_dev/testing$ cat /etc/mysql/my.cnf > > > > # > > > > # The MySQL database server configuration file. > > > > # > > > > # You can copy this to one of: > > > > # - "/etc/mysql/my.cnf" to set global options, > > > > # - "~/.my.cnf" to set user-specific options. > > > > # > > > > # One can use all long options that the program supports. > > > > # Run program with --help to get a list of available options and with > > > > # --print-defaults to see which it would actually understand and use. > > > > # > > > > # For explanations see > > > > # http://dev.mysql.com/doc/mysql/en/server-system-variables.html > > > > > > > > # This will be passed to all mysql clients > > > > # It has been reported that passwords should be enclosed with > > > ticks/quotes > > > > # escpecially if they contain "#" chars... > > > > # Remember to edit /etc/mysql/debian.cnf when changing the socket > > > location. > > > > [client] > > > > port = 3306 > > > > socket = /var/run/mysqld/mysqld.sock > > > > > > > > # Here is entries for some specific programs > > > > # The following values assume you have at least 32M ram > > > > > > > > # This was formally known as [safe_mysqld]. Both versions are > currently > > > > parsed. > > > > [mysqld_safe] > > > > socket = /var/run/mysqld/mysqld.sock > > > > nice = 0 > > > > > > > > [mysqld] > > > > # > > > > # * Basic Settings > > > > # > > > > > > > > # > > > > # * IMPORTANT > > > > # If you make changes to these settings and your system uses > > apparmor, > > > you > > > > may > > > > # also need to also adjust /etc/apparmor.d/usr.sbin.mysqld. > > > > # > > > > > > > > user = mysql > > > > socket = /var/run/mysqld/mysqld.sock > > > > port = 3306 > > > > basedir = /usr > > > > datadir = /var/lib/mysql > > > > tmpdir = /tmp > > > > skip-external-locking > > > > # > > > > # Instead of skip-networking the default is now to listen only on > > > > # localhost which is more compatible and is not less secure. > > > > bind-address = 127.0.0.1 > > > > # > > > > # * Fine Tuning > > > > # > > > > key_buffer = 2048M > > > > max_allowed_packet = 2048M > > > > thread_stack = 192K > > > > max_heap_table_size = 512M > > > > tmp_table_size = 512M > > > > join_buffer_size = 5M > > > > thread_cache_size = 8 > > > > > > > > # This replaces the startup script and checks MyISAM tables if needed > > > > # the first time they are touched > > > > myisam-recover = BACKUP > > > > max_connections = 100 > > > > table_cache = 2400 > > > > #table_definition_cache = 2400 > > > > thread_concurrency = 10 > > > > # > > > > # * Query Cache Configuration > > > > # > > > > query_cache_limit = 64M > > > > query_cache_size = 128M > > > > # > > > > # * Logging and Replication > > > > # > > > > # Both location gets rotated by the cronjob. > > > > # Be aware that this log type is a performance killer. > > > > # As of 5.1 you can enable the log at runtime! > > > > #general_log_file = /var/log/mysql/mysql.log > > > > #general_log = 1 > > > > > > > > log_error = /var/log/mysql/error.log > > > > > > > > # Here you can see queries with especially long duration > > > > #log_slow_queries = /var/log/mysql/mysql-slow.log > > > > #long_query_time = 2 > > > > #log-queries-not-using-indexes > > > > # > > > > # The following can be used as easy to replay backup logs or for > > > > replication. > > > > # note: if you are setting up a replication slave, see README.Debian > > > about > > > > # other settings you may need to change. > > > > #server-id = 1 > > > > #log_bin = /var/log/mysql/mysql-bin.log > > > > expire_logs_days = 10 > > > > max_binlog_size = 100M > > > > #binlog_do_db = include_database_name > > > > #binlog_ignore_db = include_database_name > > > > # > > > > # * InnoDB > > > > # > > > > # InnoDB is enabled by default with a 10MB datafile in > /var/lib/mysql/. > > > > # Read the manual for more InnoDB related options. There are many! > > > > # > > > > # * Security Features > > > > # > > > > # Read the manual, too, if you want chroot! > > > > # chroot = /var/lib/mysql/ > > > > # > > > > # For generating SSL certificates I recommend the OpenSSL GUI > "tinyca". > > > > # > > > > # ssl-ca=/etc/mysql/cacert.pem > > > > # ssl-cert=/etc/mysql/server-cert.pem > > > > # ssl-key=/etc/mysql/server-key.pem > > > > > > > > > > > > > > > > [mysqldump] > > > > quick > > > > quote-names > > > > max_allowed_packet = 16M > > > > > > > > [mysql] > > > > #no-auto-rehash # faster start of mysql but no tab completition > > > > > > > > [isamchk] > > > > key_buffer = 16M > > > > > > > > # > > > > # * IMPORTANT: Additional settings that can override those from this > > > file! > > > > # The files must end with '.cnf', otherwise they'll be ignored. > > > > # > > > > !includedir /etc/mysql/conf.d/ > > > > > > > > > > > > I have commented out "#table_definition_cache = 2400" as it was not > > > allowing > > > > me to restart mysql server. > > > > > > > > Do you think that switching to Mysql 5.1.x can make any difference > > here? > > > In > > > > past I have also tried on Mysql 5.1.x version but at that time didn't > > do > > > > "Fine Tunning" thing. > > > > May be this time setting few additional parameter on Mysql 5.1.x can > > give > > > > better result. WDYT? > > > > > > > > Thanks! > > > > > > > > -- > > > > Ashish > > > > > > > > > > > > On Mon, Feb 21, 2011 at 12:39 AM, Ashish Vijaywargiya < > > > > [email protected]> wrote: > > > > > > > >> Wow, too quick Mike. Thanks a lot! > > > >> Doing this changes right away and will be back to you in next 15 > > minutes > > > or > > > >> so. > > > >> > > > >> This is quite interesting, I am having 1 more machine on which > Ubuntu > > > 8.04 > > > >> is running and over there I didn't do any manual adjustment. > > > >> The "ant run-install" command takes about 4-5 minutes in completion. > > > >> > > > >> -- > > > >> Ashish > > > >> > > > >> > > > >> On Mon, Feb 21, 2011 at 12:35 AM, Mike <[email protected]> > wrote: > > > >> > > > >>> Tuning. Ofbiz has over 800 tables. Here are a few things to > tweak. > > > >>> > > > >>> table_cache = 2400 > > > >>> table_definition_cache = 2400 > > > >>> max_heap_table_size = 512M > > > >>> tmp_table_size = 512M > > > >>> join_buffer_size = 5M > > > >>> query_cache_limit = 64M > > > >>> query_cache_size = 128M > > > >>> > > > >>> > > > >>> On Sun, Feb 20, 2011 at 10:54 AM, Ashish Vijaywargiya > > > >>> <[email protected]> wrote: > > > >>> > Hello, > > > >>> > > > > >>> > Since last 2 years I was using OFBiz Trunk + Mysql 5.0.x + Ubuntu > > > 8.04 + > > > >>> Sun > > > >>> > Jdk1.6. Everything was working as expected & the command "ant > > > >>> run-install" > > > >>> > was taking about 4 - 5 minutes on my laptop. > > > >>> > > > > >>> > Before 3, 4 month I have switched to Ubuntu 10.04 but now "ant > > > >>> run-install" > > > >>> > command is taking about 19 - 22 minutes on my laptop. > > > >>> > *On my machine I have observed that "ant run-install" command is > > > taking > > > >>> too > > > >>> > much time at the time of index creation.* > > > >>> > > > > >>> > I have tried with all the mysql connector(3.0.x, 3.1.x & 5.x) but > > all > > > >>> the > > > >>> > time completion time for "ant run-install" command remains around > > 19 > > > - > > > >>> 22 > > > >>> > minutes. > > > >>> > > > > >>> > ashish@ashish-laptop:~/ofbiz_dev/testing$ svn info > > > >>> > Path: . > > > >>> > URL: https://svn.apache.org/repos/asf/ofbiz/trunk > > > >>> > Repository Root: https://svn.apache.org/repos/asf > > > >>> > Repository UUID: 13f79535-47bb-0310-9956-ffa450edef68 > > > >>> > Revision: 1072680 > > > >>> > Node Kind: directory > > > >>> > Schedule: normal > > > >>> > Last Changed Author: erwan > > > >>> > Last Changed Rev: 1072600 > > > >>> > Last Changed Date: 2011-02-20 19:43:03 +0530 (Sun, 20 Feb 2011) > > > >>> > > > > >>> > Recently ran this command and here is the out come of time frame: > > > >>> > > > > >>> > [java] 2011-02-21 00:17:43,537 (main) > > > >>> > [EntityDataLoadContainer.java:397:INFO ] 00004 of 15353 from > > > >>> > > > > >>> > > > > > > file:/home/ashish/ofbiz_dev/testing/specialpurpose/webpos/data/WebPosSecurityData.xml > > > >>> > [java] 2011-02-21 00:17:43,537 (main) > > > >>> > [EntityDataLoadContainer.java:408:INFO ] =-=-=-=-=-=-= Finished > the > > > data > > > >>> > load with 15353 rows changed. > > > >>> > [java] 2011-02-21 00:17:43,537 (main) [ > > > >>> > ContainerLoader.java:113:INFO ] Shutting down containers > > > >>> > > > > >>> > BUILD SUCCESSFUL > > > >>> > Total time: 21 minutes 35 seconds > > > >>> > > > > >>> > Anyone using OFBiz Trunk + MySql 5.0.x + Ubuntu 10.04 + Sun > Jdk1.6 > > > >>> > combination? How much time it is taking to run "ant run-install" > > > command > > > >>> for > > > >>> > you? > > > >>> > I am assuming that I may need to do some additional settings > inside > > > >>> my.cnf > > > >>> > file in Ubuntu 10.04. > > > >>> > Any pointer would be greatly appreciated. > > > >>> > > > > >>> > Thanks! > > > >>> > > > > >>> > -- > > > >>> > Ashish > > > >>> > > > > >>> > > > >> > > > >> > > > > > > > > > > > > > -- > Regards, > Varun Bhansaly >
