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
