6

I have an EC2 Large instance dedicated to MySQL.

It will be serving a Joomla/Magento combo so it has a blend of InnoDB and MyISAM tables. I have only worked with MyISAM in the past and am therefore unfamiliar with the settings InnoDB uses. Experiments so far have been less than fruitful, as I keep causing the InnoDB engine to be disabled.

My instance is running Ubuntu 10.04 64 bit server edition and has ~7.5G of ram. MySQL is currently using ~0.6% of that, with somewhat poor performance. I would like to configure it to use as much of the system RAM as is reasonable.

Testing some settings I learned that the InnoDB logs can't collectively be larger than 4G.

Would anyone be able to provide some base InnoDB and MyISAM settings to get my started.

Thank you Tim

Tim Reynolds
  • 171
  • 1
  • 6

2 Answers2

8

I'm running the same sort of Mysql server on EC2. Don't touch /etc/mysql/my.cnf, but instead put config files into /etc/mysql/conf.d/ This will let you manage your changes for specific engines and give admins after you additional clues to how things were configured. Also it make it easy to backup any changes you've made as well as track them.

/etc/mysql/conf.d/innodb_mysql.cnf

[mysqld]    
# innodb settings 
innodb_additional_mem_pool_size = 12M
innodb_buffer_pool_size         = 2G
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 2
innodb_lock_wait_timeout        = 180
innodb_log_buffer_size          = 16M
innodb_open_files               = 512
innodb_thread_concurrency       = 0

Most of these settings are pretty standard and you should definitely look at Percona's recommendations. You could got as high as 6G on your system, but I'd start small. Also with myisam tables I'd probably stop at 4G. I would not touch the innodb log file sizes, there is very little gain to be had there. Increasing the log_buffer_size and addition_mem_pool also has very little return after an initial bump.

Also the settings above are for better performance and not for transaction consistency. Assuming you're running just a web app the above is fine, but not for a banking system.

/etc/mysql/conf.d/general_mysql.cnf

[mysqld]
# general settings
key_buffer = 384M

key_buffer is the most useful for myisam and Mysql in general The default it's only 16M which is very small on an 8GB machine. Again I'd start with a good jump and see if you get improvement. Keep in mind that myisam buffers and innodb buffers are not shared so they need to be collectively less than then amount of RAM you have. Later you might look at sort and read buffers as well.

kashani
  • 3,922
  • 19
  • 18
  • Thank you. I know I need to do testing, but this is for a project in development right now. The base settings are causing a lot of slowdown when entering products into Magento, so for the sake of the data entry team I need to get it _somewhere_ I plan to fully benchmark the system once it is complete. – Tim Reynolds Mar 21 '11 at 12:14
  • What will the impact be of the innodb_flush_log_at_trx_commit = 2 setting? I know disabling flushing will increase performance but remove the 'D' from ACID. I will have to look up what a 2 does. – Tim Reynolds Mar 21 '11 at 12:20
  • You stated that you use O_DIRECT for the flush method, however I read in the documentation that if the data and logs are on a SAN that it can degrade select performance by a factor of 3. http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html#sysvar_innodb_flush_method . Do you think I should use the default instead? – Tim Reynolds Mar 21 '11 at 12:32
  • "When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues." Basically you flush after transactions, but you only flush to disk once a second(ish). – kashani Mar 21 '11 at 17:00
  • In regards to o_direct, I have not seen *anyone* address performance on EC2 with any innodb_flush_method which seems like a huge oversight. I'm going to a ping a few Mysql guys and see what they recommend. – kashani Mar 21 '11 at 17:39
  • I know you're looking for performance immediately, but starting with the settings above should increase 50x if you've been using the defaults (according to Morgan Tocker @ Percona). After that messing around in the my.cnf gets a lot less exciting. You might squeeze another 5-10%, but not large gains unless you've got a unique situation that the rest of the defaults don't address. From what you've explained, you probably don't have this. There is some value in increasing certain settings for high traffic, but your metrics gathering should point you towards those. – kashani Mar 21 '11 at 17:59
  • And finally I was pointed to this bug, http://bugs.mysql.com/bug.php?id=54306 which has better explanation of innodb_flush_method. Additionally it was recommended to skip setting innodb_flush_method to O_DIRECT for EC2 and I've removed it from above. Thanks for questioning my configs, I've learned a crap load double checking my work. – kashani Mar 21 '11 at 19:13
  • Ha! Not questioning so much as wanting to better understand. I have learned a bit as well. Things are moving a lot smoother, though I think my overall demand is quite low on the server as MySQL is only using around 5% of the system memory. – Tim Reynolds Mar 21 '11 at 20:31
2

I feel that you need to try some different (well documented) options and have some stats collected,so you can do comparisions w/ before and after,otherwise you could FEEL you are helping ,but might hurt.

  1. Easiest stats are right out of phpmyadmin, get into phpmyadmin and pick status from the buttons across the top of the right frame.

  2. Another decent tool is https://github.com/rackerhacker/MySQLTuner-perl/blob/master/mysqltuner.pl # (v1.2.0)

  3. lastly to enable your slow query log.

I feel it is critically important to get good metrics, before making changes, otherwise you won't be certain beyond the seat-of-your-pants,that it is helping.

I feel that it is great to add indexes to everything that is READ frequently and updated/inserted/added to rarely.

I do a optimisze table on everything every night after nightly mysqldumps.

Your innodb specific options are:

spended #  
[ 14:31. diane@ltk-prod-1 ~]% mysqladmin -u USER -pPASSWD variables | grep -i inno |sed -e 's/                                  //g'|less

Mine are:


| innodb_autoinc_lock_mode | 1::                     |
| innodb_buffer_pool_size  | 8388608::               |
| innodb_checksums         | ON::                    |
| innodb_commit_concurrency| 0::                     |
| innodb_concurrency_tickets              | 500::    |
| innodb_data_file_path    | ibdata1:10M:autoextend::|
| innodb_data_home_dir     |::                       |
| innodb_doublewrite       | ON::                    |
| innodb_fast_shutdown     | 1::                     |
| innodb_file_io_threads   | 4::                     |
| innodb_file_per_table    | ON::                    |
| innodb_flush_log_at_trx_commit          | 1::      |
| innodb_flush_method      |::                       |
| innodb_force_recovery    | 0::                     |
| innodb_lock_wait_timeout | 50::                    |
| innodb_locks_unsafe_for_binlog          | OFF::    |
| innodb_log_buffer_size   | 1048576::               |
| innodb_log_file_size     | 5242880::               |
| innodb_log_files_in_group| 2::                     |
| innodb_log_group_home_dir| ./::                    |
| innodb_max_dirty_pages_pct              | 90::     |
| innodb_max_purge_lag     | 0::                     |
| innodb_mirrored_log_groups              | 1::      |
| innodb_open_files        | 300::                   |
| innodb_rollback_on_timeout              | OFF::    |
| innodb_stats_on_metadata | ON::                    |
| innodb_support_xa        | ON::                    |
| innodb_sync_spin_loops   | 20::                    |
| innodb_table_locks       | ON::                    |
| innodb_thread_concurrency| 8::                     |
| innodb_thread_sleep_delay| 10000::                 |
| innodb_use_legacy_cardinality_algorithm | ON::     |

    enter code here

I suggest bump up buffers 2x at a time, and try test queries,

BUT. also, for some machines, I run out of /dev/shm (ramdisk) (16gb ram helps)

and then just do mysqldumps 3 or 4x a day.

mysqltuner for me, says that I run dangerously OVER physical ram,but I aways seem to have half a gig free.

dianevm
  • 132
  • 3