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
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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
