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