> -----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 >
Ugh... mysqladmin -uroot -ptelaurus processlist | grep -c "Sleep" And it returned 200 sleeping connections, all persistant connections from our app servers and 4 threads_running Also a show status gave me a max_used_connections of 236. If that's the case then I can probably only set it to about 250 which means if I set my innodb_buffer_pool_size = 100M and dropping my key_buffer_size to 250, I'll need 1884M of ram according to the formula above, which is dangerously close to the 2G limit specified in the warning on the link above. Currently the key_reads to Key_reads_requests is about 1:1970 with the key_buffer_size of 384M, so I guess I can safely drop this to 250M Even if I changed the entire DB over to InnoDB, and pushed the key_buffer_size down really low it wouldn't drop the total memory usage below 1600M. So what is this telling me? I need more ram or less connections or I should just stay with MyISAM? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
