List:General Discussion« Previous MessageNext Message »
From:Jeff Date:September 28 2005 6:24pm
Subject:RE: MyISAM to InnoDB
View as plain text  
> -----Original Message-----
> From: Devananda [mailto:karnah805@stripped] 
> Sent: Wednesday, September 28, 2005 13:06
> To: Jeff
> Cc: mysql@stripped
> 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


Thread
MyISAM to InnoDBJeff22 Sep
  • Re: MyISAM to InnoDBBruce Dembecki22 Sep
RE: MyISAM to InnoDBJeff22 Sep
  • Re: MyISAM to InnoDBBruce Dembecki22 Sep
  • Re: MyISAM to InnoDBDevananda22 Sep
RE: MyISAM to InnoDBJeff22 Sep
  • Re: MyISAM to InnoDBDevananda23 Sep
    • RE: MyISAM to InnoDBJeff23 Sep
      • RE: MyISAM to InnoDBSGreen23 Sep
        • RE: MyISAM to InnoDBJeff23 Sep
          • RE: MyISAM to InnoDBSGreen23 Sep
            • RE: MyISAM to InnoDBJeff23 Sep
              • RE: MyISAM to InnoDBSGreen23 Sep
              • Re: MyISAM to InnoDBDevananda23 Sep
            • RE: MyISAM to InnoDBJeff28 Sep
              • Re: MyISAM to InnoDBDevananda28 Sep
            • Re: MyISAM to InnoDBPooly29 Sep
RE: MyISAM to InnoDBSujay Koduri28 Sep
RE: MyISAM to InnoDBJeff28 Sep
RE: MyISAM to InnoDBSujay Koduri28 Sep
RE: MyISAM to InnoDBJeff28 Sep
RE: MyISAM to InnoDBJeff28 Sep
  • RE: MyISAM to InnoDBSGreen28 Sep
  • Re: MyISAM to InnoDBDevananda29 Sep
    • Re: MyISAM to InnoDBBruce Dembecki29 Sep
RE: MyISAM to InnoDBJeff29 Sep
  • RE: MyISAM to InnoDBSGreen29 Sep
  • Re: MyISAM to InnoDBDevananda29 Sep
RE: MyISAM to InnoDBJeff McKeon29 Sep
RE: MyISAM to InnoDBJeff29 Sep