"Jeff" <[EMAIL PROTECTED]> wrote on 09/28/2005 02:24:48 PM:
> > -----Original Message----- > > From: Devananda [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, September 28, 2005 13:06 > > To: Jeff > > Cc: mysql@lists.mysql.com > > 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 > > 236 concurrent connections is not that bad, however the fact that 200 of them are currently sleeping made me wince. During this changeover, I would also think about your application(s) design(s). Can you somehow reduce the number of concurrent connections from the application side? Do all of those connections really need to be persistent? Making and breaking a MySQL connection is a much "lighter" activity (faster, fewer resources) than doing the same thing with Oracle or some other database servers. You might see improved performance if you only make a connection when you need one and drop it as soon as your are through. For instance, many developers of most stand-alone (desktop) applications (like Java or VB apps) and some web-based applications might think it to be more efficient to open a connection once when the user starts the app (logs into the site) and close it only as the app is shutting down (leaves the site/logs out). Yes, it limits connection "churn" in the application but if the majority of the time is spent waiting on user response (filling in forms, clicking on buttons, analyzing data, etc) then you are not really making full use of your database server's capacity, are you? Those connections are really only necessary when the application needs to communicate with the server and it may work better for you if you design you app to connect only when needed. "Connect last - disconnect early" is a design philosophy I frequently follow. Now, if you have a block of code that does several separate database actions in a row, DO NOT make and break connections between each action. Make as few connections as you need at the beginning of the block and close them at the end of the block. It's when the application is waiting on the user that it is usually advantageous to be disconnected. I know I made a fairly generalized statement. I also know that my advice will not work better for all application designs. However, it is still a design option to consider in order to optimize the availability of database server-side resources. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
