MySQL Scaling technique.

Global Configuraiton Level:

1. thread_cache_size
   Change if you do a lot of new connections.
2. table_cache
   Change if you have many tables or simultaneous connections
3. delay_key_write
   Set if you need to buffer all key writes
4. max_heap_table_size
   Used with GROUP BY
5. sort_buffer
   Used with ORDER BY and GROUP BY
6. query_cache_type
   Set this ON if you are repeating the sql queries default OFF
7. query_cache_size
   Set this to any perticuler value >= query_cache_limit. To disabled query_cache_size set the value to "0".

MyISAM

1. key_buffer_size
   Change if you have enough RAM to store available MyISAM table Index
2. myisam_sort_buffer_size
   Useful when Repairing tables.
3. myisam_use_mmap
   Use memory mapping for reading and writing MyISAM tables.

InnoDB

1. innodb_buffer_pool_size
   Change if you have enough RAM to store available InnoDB table Index.
2. innodb_support_xa
   Turn off if you don't need it for safe binary logging or replication
3. innodb_doublewrite
   If enable then 5-10% performance loss due to use of doublewrite
4. innodb_lock_wait_timeout
   To remove the deadlock process after certain timeout value.
5. innodb_thread_concurrency
   A recommended value is 2 times the number of CPUs plus the number of disks.
6. innodb_flush_method
   On some systems where InnoDB data and log files are located on a SAN, it has been found that setting innodb_flush_method to O_DIRECT can degrade performance of simple SELECT statements by a factor of three.
7. innodb_flush_log_at_trx_commit
   For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1, sync_binlog=1
8. innodb_file_per_table
   This will create the file per table same as MyISAM

System Level:

1. Disable DNS Hostname Lookup
3. RAID 10 is must for high I/O performance.
4. ResierFS is recomended filesystem by most of the blog posts but xfs is doing good for us over RAID 10.

Architectural Level:

1. Use VARCHAR datatype instead of CHAR.
2. AUTO_INCREMENT should be BIGINT if there are Million Row insert/delete.