> -----Original Message----- > From: Sujay Koduri [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 11:04 > To: Jeff; mysql@lists.mysql.com > Cc: [EMAIL PROTECTED] > Subject: RE: MyISAM to InnoDB > > > > If you think your storage requiremnets will increase in > future, try to estimate how much you will be needing in the > future in the worst case and try allocating that much of disk > space now itself (Any way you have good amount of disk space left). > Try creating a different partition for storing the log files. > This will increase the performance >
Well currently MySQL is set up in the default dir of /var/lib/mysql and soft links to the database data residing on another partition /DATA/<dbname>. Should I maybe specify: innodb_log_group_home_dir = /var/lib/mysql/iblogs/ I have about 9 gig available on /var so 1.5 gig of logs shouldn't be too bad. > Even if you don't do this and run out of space, you just have > to add more add data files and a restart the server. > > And for 2G RAM, its better to limit the > innodb_bufferpool_size to 1G. You can also look at the > query_cache_size parameter and try tuning that by running > some load tests. > > Apart from that everything is looking fine for me > > sujay > > -----Original Message----- > From: Jeff [mailto:[EMAIL PROTECTED] > Sent: Wednesday, September 28, 2005 8:22 PM > To: mysql@lists.mysql.com > Cc: [EMAIL PROTECTED] > Subject: RE: MyISAM to InnoDB > > > Cut orignal thread because it was too long > > Ok so I'm about to convert two tables in my database from > MyISAM to InnoDB. They are currently: > > 14K Sep 15 13:15 Table1.frm > 2.1G Sep 28 14:15 Table1.MYD > 198M Sep 28 14:15 Table1.MYI > > 11K Sep 20 08:45 Table2.frm > 424K Sep 28 14:15 Table2.MYD > 110K Sep 28 14:15 Table2.MYI > > 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 > 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 > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
