MySQL Brief introduction and some limitations

MySQL version
Alfa-Beta->ga
GA represents production environment use

MySQL Limitations

1, do not complex transaction support, RR level, auxiliary next-key lock, you can meet the requirements of high consistency;
2, really need materialized view, can adopt the way of flip-flop to realize;
3, does not support function index, hash join, bitmap index, although is flawed, but most of the Internet applications do not need such a strong functional requirements, or can be modified product requirements;
4, the query optimizer in the continuous improvement, the need for a process, and the vast majority are simple applications, the overall is OK;

MySQL introduce some advantages and disadvantages of using hardware

CPU Utilization Features

5.5 can take advantage of up to 24 cores
5.6 can take advantage of up to 64 cores
Each connection corresponds to one thread, and each concurrent query can use only one core

Memory utilization Features
Memory management is simple, effective, high TPS (high transaction count), high concurrency environment, need to rely on more physical memory to reduce physical IO, improve concurrency performance
There is a query cache, but the effect is poor, it is recommended to close
Execution plan is not cached (similar to Oracle's library cache)
Usually memory recommendations are based on the 15%-20% of the actual data (the amount of hotspot data), and the dedicated single instance can allocate 50~80% around the physical memory (innodb_buffer_pool_size)
Similar to k-v simple data, using memcached, Redis and other NoSQL to cache

Features of HDD utilization

Characteristics of the use of disks
Binlog, redo log, undo log are sequential IO
DataFile is both random io and sequential io have
OLTP services require more random IO, which can increase memory to reduce random IO
OLAP Services need sequential IO More, memory cache does little, and improving IOPS performance is more important
MyISAM is the heap organization table (hot), InnoDB is the Index organization table (IOT)

This article is from "DBA Sky" blog, declined reprint!

MySQL Brief introduction and some limitations