Good/Better config for MySQL on an EC2 Large Instance

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.

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.