Mysql

Key Ideas to cover

• Scalability
• Availability
• Consistency
• Flexibility
• How to do MySQL query optimizations ?
• Analyze MySQL query performance ?
• How to analyze MySQL DB performance + Monitor performance + Reporting tools ?
• MySQL with Redis
• Can MySQL handle multi cluster / nodes ?
• Security
• Transactions
• Event Schedulers
• Views
• Procedures
• Functions
• Indexing
• Variables
• Cursors
• Flow Control
• Triggers

SQL terms

MYSQL vs MSSQL

Query Optimizations :

• The developer must consider the future usage of the tables in terms of the number of records, the table size on disk, the different variations of queries to be performed, and must create the appropriate indexes while designing the table. The developer is responsible for the proper index creation, not the system administrator, the DBA, or any other consultant.

Views :

Roles :

• Role is a collection of privileges
• Roles with certain privileges are assigned to users based on their requirement.
• For example developers are given full privilege roles.
• We can control access to users, such as
  • Read
  • Write
  • Delete
  • Update
  • Databases
  • Tables
• Detailed SQL query on creating and assigning roles

	Privilleges --> Roles --> Users 

MySQL Architecture

• Layer 1 : BASIC CONNECTION LAYER

  • Basic Client / Server tools
  • Connection handling
  • Authentication
  • Security etc

• Layer 2 : SERVER LEVEL LAYER

  • Query parsing
  • Query Analysis
  • Optimization
  • Caching
  • Built in function
  • Procedure
  • Triggers
  • Views

• Layer 3 : STORAGE ENGINE LAYER

  • Storage engines
  • Responsible for storing and retrieving all data store.
  • Server communicates with them through storage engine API
  • Example : InnoDB, MyISAM

Connection management

• Each connection is allocated a thread and all queries made by that connection executes with in that thread.
• Threads are cached / destroyed once connection ends.

Optimization & Execution

• Parse query to a tree

• Apply variety of optimization's

  • Rewrite query
  • Find the order in which to read the tables used in query
  • Choose indexes to use
  • Ask storage engines corresponding to those tables about cost of certain operations.
  • Ask storage engines on statistics of table.
  • Finally choose the best way to execute query

• Query Cache

  • Before even parsing the query sql first checks if similar SELECT query exist in cache
  • If it does, it simply sends cached results back to client.

• Analyze MySQL optimizer decisions

  • SQL Query analyzer helps to analyze the query and understand what steps the SQL optimizer took to give the result.
  • How many rows the MySQL has to inspect to get you the result.
  • Did MySQL use any available indexes to filter rows and reduce the rows it has to through.
  • We can also use EXPLAIN to understand which tables & columns are worth indexing.
  • EXPLAIN Command output is perfectly explained

Concurrency

• In Database systems there are many problems that need's to be handled to be able to concurrently run multiple queries that access the same data at the same time.

• Examples :

  • Multiple queries trying to write to same table at the same time.
  • What happens when a user tries to delete/insert data when a read is happening --> User get inconsistent stale result.

• LOCKING SYSTEM ( Solution to concurrency ) :

  • Shared Locks
  • Exclusive Locks or Read Locks
  • Write Locks

• Lock Granularity :
  • The more precise you are in locking exactly only the data that's being modified the more concurrency you can provide.
    • EXAMPLE :
      • Locking entire table blocks thousands of queries trying to access the same table.
      • Locking a row alone is better for concurrency.
  • Locking system is a comprise between **Lock Overhead ** and Data safety and Concurrency.

Transactions

• Let's say to get a job done, like a bank transaction. You need to update or insert around 3 tables.
• It requires around 3 queries.
• Now we need to be able to rollback if at least one of the query fails for any reason.
• This is why we need transactions.
• Query

	Given a set of queries in a transaction, Its either implement ALL or NONE 

• All transaction processing system must be ACID compliant --> Atomicity, Consistency, Isolation, Durability

• Example Storage engines : InnoDB, SolidDB

• Dead Locks :

  • A deadlock_ is when two or more transactions are mutually holding and requesting locks on the same resources, creating a cycle of dependencies.
  • InnoDB can detect dead locks and avoid issues.

• Mixing Engines in transactions:

  • Avoid using different storage engines in a transaction.
  • Example:

    • A transaction has 2 queries accessing 2 tables.

    • Table1 uses InnoDB , While Table2 uses MyISAM

    • When transaction fail and need rollback, InnoDB table could rollback but MyISAM couldn't.

Storage Engines

• MySQL server takes care of storing database schema in file system as shown above.
• The storage engine takes of storing data and their indexes.
• To check storage engine a table uses :

SHOW TABLE STATUS LIKE 'user' \G

Above command shows User table details

• MYISAM

• InnoDB

• Memory Engine

Selecting right engine

• We can choose storage engines on a table by table basis.

• It is important to think about which storage engine is appropriate for a given data at the design stage itself.

• Considerations

Example use cases :

• Logging every call details in real-time applications

  • Logging usually requires high write speeds and occasional read speeds to analyze logs.

  • MyISAM and Archive storage engines are perfect and can support thousands of write speeds per second.

  • What happens when you try to read logs and create summary.

    • Issues :
      • Depending on query used there's good chances that this could slow down the write speeds.
    • **Solutions **:
      • Using MySQL replication feature : Where master servers for inserting logs and you can use of the cloned slaves for reading.
      • Merge Tables : Adjust the application to log to table based on year month data etc. This way we have multiple tables like log_2020_01 , log_2020_02. This way we avoid heavy reads on table being written.

• Mostly only read data :

  • MyISAM is the best choice if you don't mind what happens when MyISAM crashes.
  • So better use MyISAM when you are sure of how to retrieve from a crash or data in those tables isn't that important like catalog data... list of countries, pin codes, zips.

• Order processing :

  • Order processing requires transactional storage.
  • InnoDB is the best choice if you need support for foreign key constraints as well.

• Stock quotes :

  • MyISAM --> Only if using for personal stock quote analysis
  • InnoDB --> When providing a web service with real time quote table where thousands of clients could be trying to read and write.

Status: #todo

Tags: #mysql

References:

• Short and concise tutorials on MYSQL + MYSQL in Python
• Oreilly MYSQL
• MySQL Connection handling

Related: