MySQL性能优化

1 - 使用ESO OAuth v2 code base
2 - API查询性能优化
3 - MySQL配置
My.ini是旧的master配置文件
My-new.ini是新的master配置文件（DBA团队提供）
Deploy doc里面是perf上master/slave配置时的配置文件变化
比较新版配置文件变化，检查innodb日志大小如有必要就转移（下面红字部分），整理出主从my.ini配置文件部署perf
其它一些优化意见
MySQL Configuration:
1. innodb_buffer_pool_size: Allocate enough innodb_buffer_pool_size to load your entire InnoDB file into memory – less reads from disk. This value would probably 60-70% of the memory.
2. innodb_log_file_size : Do not make innodb_log_file_size too big, with faster and more disks – flushing more often is good and lowers the recovery time during crashes. The recommended settings innodb_log_file_size to 25% of innodb_buffer_pool_size , Values up to 4G are quite safe. Let’s start with 512M.

Note : If you change the parameter, you need to shut down cleanly and then move the log files away and restart. InnoDB will fail on restart if existing log files don’t match the configured size.
1. innodb_thread_concurrency : Number of threads allowed inside the InnoDB kernel. A too high value may lead to thread thrashing.
2. max_connections : If you get a Too many connections error when you try to connect to the mysqld server, this means that all available connections are in use by other users/clients. The number of connections permitted is controlled by the max_connections system variable. The default value is 151. If you need to support more connections, you should set a larger value for this variable.
3. max_allowed_packet : The maximum size of a packet allowed to be sent. A packet is a single SQL state, a single row being sent to a client, or a log being sent from a master to a slave. If you know that your MySQL server is going to be processing large packets, it is best to raise this to the size of your largest packet. Should this value be set too small, you would receive an error in your error log.
4. Increase temp_table_size and max_heap_table_size – to prevent disk writes.
5. key_buffer_size : Changing the key_buffer allocates more memory to MySQL, which can substantially speed up your databases, assuming you have the memory free. According to MySQL’s documentation, for servers with 256MB of RAM (or more) with many tables, a setting of 64M is recommended, while servers with 128MB of RAM and fewer tables can be set to 16M, the default value. Websites with even fewer resources and tables can have this value set lower.
6. thread_cache_size : If thread_cache_size is “turned off” (set to 0), then all new connections being made need a new thread created for them, and when the connections disconnect the thread is destroyed. Otherwise, this value sets the number of unused threads to store in a cache until they need to be used for a connection. Generally this setting has little affect on performance, unless you are receiving hundreds of connections per minute, at which time this value should be raised so the majority of connections are being made on cached threads.
7. Do not set your sort_buffer_size too high – this is per connection and can use up memory fast.

Query Optimization :
1. Use the slow query log to find slow queries.
2. Use EXPLAIN to determine queries are functioning appropriately.
3. Avoid count(*) on entire tables, it can lock the entire table.
4. Make queries uniform so subsequent similar queries will use query cache.
5. Use GROUP BY instead of DISTINCT when appropriate.
6. Use indexed columns in WHERE, GROUP BY, and ORDER BY clauses.
7. Keep indexes simple, do not reuse a column in multiple indexes.
8. Sometimes MySQL chooses the wrong index, use USE INDEX for this case
9. Use a LIMIT on UNION instead of OR for less than 5 indexed fields.
10. Use INSERT ON DUPLICATE KEY or INSERT IGNORE instead of UPDATE to avoid the SELECT prior to update.
11. Use a indexed field and ORDER BY instead of MAX.
12. Avoid using ORDER BY RAND().
13. Use UNION instead of sub-queries in WHERE clauses.
14. For UPDATES, use SHARE MODE to prevent exclusive locks.
15. Use DROP TABLE then CREATE TABLE instead of DELETE FROM to remove all data from a table.