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.
Easiest stats are right out of phpmyadmin, get into phpmyadmin and pick status
from the buttons across the top of the right frame.
Another decent tool is https://github.com/rackerhacker/MySQLTuner-perl/blob/master/mysqltuner.pl # (v1.2.0)
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.