> -----Original Message----- > From: Devananda [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 13:06 > To: Jeff > Cc: mysql@lists.mysql.com > Subject: Re: MyISAM to InnoDB > > > Jeff wrote: > >> Cut orignal thread because it was too long > > > > > > The system is only used as a database server, it's a dual processor > > system with 2gig of ram. > > > > As you can see, Table1's MyISAM data file is quite large at > 2.1 gig. > > Taking this into account what size InnoDB data files should I > > configure in my my.cnf file? > > > > I was thinking of this: > > > > My.cnf > > > > <snip> > > > > [mysqld] > > > > datadir=/var/lib/mysql > > socket=/var/lib/mysql/mysql.sock > > log-bin > > server-id=70 > > port = 3306 > > skip-locking > > key_buffer = 384M > > max_allowed_packet = 1M > > table_cache = 512 > > sort_buffer_size = 2M > > read_buffer_size = 2M > > myisam_sort_buffer_size = 64M > > thread_cache = 8 > > query_cache_size = 32M > > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 4 > > set-variable= max_connections=500 > > > > ### InnoDB setup ### > > > > # use default data directory for database innodb_data_home_dir = > > /DATA/dbdata/ innodb_data_file_path = > > /ibdata/ibdata1:2G;/ibdata/ibdata2:50M:autoextend:max:2G > > innodb_log_group_home_dir = /DATA/dbdata/ibdata/iblogs > > > > innodb_buffer_pool_size = 1G > > innodb_additional_mem_pool_size = 20M innodb_log_files_in_group = 3 > > innodb_log_file_size = 500M > > innodb_log_buffer_size = 8M > > innodb_buffer_pool_size = 1.5G > ((( duplicate setting, later-occurring one will take precedence ))) > > innodb_additional_mem_pool_size = 2M > > innodb_file_io_threads = 4 > > > > </snip> > > > > But what happens if the ibdata2 fills up to the max of 2G? > I've got 50 > > gig available on the partition where the db data is stored. > > > > Is there anything else here that looks incorrect? > > > > Thanks, > > > > Jeff > > > > I agree with what Sujay suggested: you can set the > innodb_log_file_size > much smaller, and will get the same performance with better start-up > time. 100M x 3 log_files_in_group should be fine. Also I recommend > setting up your ibdata files large enough to anticipate need > initially. > If, or once, they are full, you will not be able to write to > tables in > InnoDB, so make sure that does not happen! > > However, I see a potential problem - you said your system only has 2G > RAM. Here's the formula for how much RAM MySQL can (worst case) use, > taken from http://dev.mysql.com/doc/mysql/en/innodb-configuration.html > > innodb_buffer_pool_size > + key_buffer_size > + > max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) > + max_connections*2MB > > 1024M ((( assuming you meant 1G and not 1.5G ))) > + 384M > + 500 * (2M + 2M + ??) > + 500 * 2M > > According to your config, this results in a minimum of 1408M + 6M * > current_connections. That doesn't leave much RAM for the > underlying OS > and any other processes running. And, far worse, if your application > servers attempted to establish more than 100 connections, MySQL could > not allocate enough memory for them, and would either crash > or deny new > connections. > > You need to adjust something in the formula - reduce > max_connections if > that is possible, or reduce the key_buffer_size if you do not > need to be > working with MyISAM tables on this server, or allocate less memory to > innodb_buffer_pool_size. > > > Best Regards, > Devananda >
Since this server will have InnoDB and MyISAM tables, 100+ connections, I'll need to reduce the innodb_buffer_pool_size. I can possibly drop the max_connections to 250 as well. Thanks! Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
