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

2 Answers 2

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.

8
  • 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. Mar 21, 2011 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. Mar 21, 2011 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. dev.mysql.com/doc/refman/4.1/en/… . Do you think I should use the default instead? Mar 21, 2011 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, 2011 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, 2011 at 17:39
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.

1
  • Dangerously? Yikes! I will implement some of these settings today. Thank you for the thorough writeup. Mar 21, 2011 at 12:15

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .