Module

About

Time

Introduction / Preface

A high-level overview of the performance part of the workshop.

10 minutes

MySQL storage engines – high level overview

InnoDB, MyISAM, MEMORY, CSV, ARCHIVE, BLACKHOLE, MERGE, MRG_MyISAM, FEDERATED, EXAMPLE

30 minutes

InnoDB

A storage engine that balances high reliability and high performance: supports row-level locking, foreign keys, follows the ACID model. Replaces MyISAM as the default storage engine since MySQL 5.5, which was released in 2010.

20 minutes

MyISAM

The default storage engine for MySQL up to version 5.5. Based on ISAM (Indexed Sequential Access Method) – does not do very well when simultaneously reading from and writing to one table due to its table locking. It also does not support transactions, but has a small footprint on the disk.

20 minutes

MEMORY (formerly known as HEAP)

Stores all data in the RAM for fast access in environments that require quick lookups of non-critical data. Its use cases are decreasing; InnoDB with its buffer pool memory area provides a general-purpose and durable way to keep most or all data in memory, and NDBCLUSTER provides fast key-value lookups for huge distributed data sets.

20 minutes

CSV

Tables are text files with comma-separated values. CSV tables let you import or dump data in CSV format, to exchange data with scripts and applications that read and write that same format. Because CSV tables are not indexed, you typically keep the data in InnoDB tables during normal operation, and only use CSV tables during the import or export stage.

20 minutes

ARCHIVE

Indended for storing and retrieving large amounts of historical, archived or security audit information.

20 minutes

BLACKHOLE

The Blackhole storage engine accepts but does not store data, similar to the Unix /dev/null device. Queries always return an empty set. These tables can be used in replication configurations where DML statements are sent to replica servers, but the source server does not keep its own copy of the data.

20 minutes

NDB (NDBCLUSTER)

Suited for applications that require the highest possible degree of uptime and availability.

20 minutes

MERGE & MRG_MyISAM

Enables a MySQL DBA or developer to logically group a series of identical MyISAM tables and reference them as one object.

20 minutes

FEDERATED

Offers the ability to link separate MySQL servers to create one logical database from many physical servers.

20 minutes

EXAMPLE

This storage engine should serve as an example in the MySQL source code. Its purpose is to illustrate how to begin writing new storage engines.

20 minutes

Choosing a MySQL storage engine

InnoDB vs. MyISAM – if you need high performance and high reliability, use InnoDB, because as MySQL advanced, they added the majority of the features that could only previously be seen in MyISAM into InnoDB effectively rendering MyISAM obsolete:

·         Full-text indexes have been available in InnoDB since version 5.6.

·         Portable tablespaces became available in InnoDB since version 5.6.

·         Spatial indexes became available in InnoDB since version 5.7.

·         Last update for table became available in InnoDB since version 5.7.

There is one caveat though - keep in mind that simple COUNT(*) queries will probably perform faster on MyISAM than they will on InnoDB – MyISAM stores the number in the table metadata, InnoDB does not.

30 minutes

MySQL storage engine optimization (InnoDB): ACID, High Performance, Reliability

my.cnf:
innodb_data_file_path
innodb_buffer_pool_size
innodb_log_file_size
innodb_log_buffer_size
innodb_flush_log_at_trx_commit
innodb_lock_wait_timeout
innodb_flush_method
innodb_buffer_pool_instances

30 minutes

InnoDB – ibdata1 and the log files

InnoDB also stores the data from its tables in a file called ibdata1 – the logs however are stored in two separate files named ib_logfile0 and ib_logfile1: all of those three files reside in the /var/lib/mysql directory.

30 minutes

Profiling server performance

1.       SET SESSION prolifing = 1;

2.       USE database_name;

3.       SELECT * FROM demo_table WHERE demo_column = ‘value’;

4.       SHOW PROFILES;

PHP profiling tools (xhprof etc.)

Profiling and routine monitoring add overhead. The important questions are how much overhead they add and whether the extra work is worth the benefit.

20 minutes

Optimizing schema and data types: character sets & collations

Character sets in MySQL are sets of symbols and encodings - collations are sets of rules for comparing characters in a character set. In other words, character sets are sets of characters that are legal in a string, while collations are a set of rules used to compare characters in a particular character set.

·         If you’re using MySQL 5.7, the default MySQL collation is generally latin1_swedish_ci because MySQL uses latin1 as its default character set. If you’re using MySQL 8.0, the default charset is utf8mb4.

·         If you elect to use UTF-8 as your collation, always use utf8mb4 (specifically utf8mb4_unicode_ci). You should not use UTF-8 because MySQL’s UTF-8 is different from proper UTF-8 encoding. This is the case because it doesn’t offer full unicode support which can lead to data loss or security issues. Keep in mind that utf8mb4_general_ci is a simplified set of sorting rules which takes shortcuts designed to improve speed while utf8mb4_unicode_ci sorts accurately in a wide range of languages. In general, utf8mb4 is the “safest” character set as it also supports 4-byte unicode while utf8 only supports up to 3.

·         To choose a good collation and character set for your MySQL data set, remember to keep it simple. A mixture of different character sets and (or) collations can be a real mess since they can be very confusing (for example, everything might work fine until certain characters appear, etc.) so it’s best to evaluate your needs upfront and choose the best collation and character set upfront.

·         Do keep in mind that some character sets might require more CPU operations, also they might consume more storage space. Using wrong character sets can even defeat indexing - for example, MySQL has to convert character sets so that it can compare them when they are not the same: the conversion might make it impossible to use an index.

40 minutes

Query performance optimization: indexing

Types of indexes, indexing strategies, isolating the column, prefix indexes, index selectivity, multicolumn indexes, choosing a good column order, clustered indexes, covering indexes, redundant and duplicate indexes, unused indexes, indexes and locking etc.

40 minutes

Query performance optimization: full-text searches

Full-text indexes, full-text searches, full-text search modes.

30 minutes

Query performance optimization: partitions, views

Using partitions and views for query performance optimization

30 minutes

Query performance optimiation: MySQL query cache

When the MySQL query cache is helpful, how MySQL checks for a cache hit, how the MySQL cache uses memory.

30 minutes

Query gotchas

Queries not using indexes, LIKE queries not using indexes, FULLTEXT search bugs etc.

30 minutes

Optimizing MySQL server settings

InnoDB & MyISAM settings @ my.cnf

20 minutes

Optimizing MySQL at the hardware level

Hardware level MySQL optimizations: servers etc.

10 minutes

Optimizing MySQL at the application level

Application level MySQL optimizations: PHP examples, dos, donts etc.

10 minutes

Backups

Types of backups (physical, logical, partial, differential, incremental, full, backup maintenance, offsite storage, encryption, compression etc.

30 minutes

MySQL with Big Data: why is MySQL an option?

In this section I would go through the reasons why developers and DBAs should consider using MySQL for their big data applications. I might include specific scenarios where using MySQL might be more feasible than using, say, MongoDB for big data sets etc.

20 minutes

MySQL Storage Engines: Which One’s a Fit for Big Data?

In this section I will provide a high-level, then an in-depth overview of MySQL / MariaDB storage engines and how they can be used to work with big data in MySQL. We should be left with two most frequently used options – MyISAM and InnoDB.

20 minutes

InnoDB vs. MyISAM for Big Data

We will go through the use cases that lets developers decide whether to use InnoDB or MyISAM when dealing with big data sets in MySQL / MariaDB. We will stick with InnoDB (the reason why is because InnoDB provides ACID compliance which might be more suited for data-intensive applications and as MySQL advanced they added the majority of the features that could previously only be seen in MyISAM into InnoDB)

30 minutes

InnoDB and Big Data

We will go through how InnoDB works, what developers and DBAs might need to optimize to push MySQL or MariaDB to the next level when dealing with big data sets etc. (might include the differences between indexes in MyISAM and InnoDB, partitions etc.)We will go through how InnoDB works, what developers and DBAs might need to optimize to push MySQL or MariaDB to the next level when dealing with big data sets etc. (might include the differences between indexes in MyISAM and InnoDB, partitions etc.)

30 minutes

InnoDB and Big Data: Gotchas

We will go through some "gotchas" (possible bugs etc.) that people might encounter when dealing with big data sets on MySQL / InnoDB and how to overcome them.

30 minutes

Module

About

Time

Introduction / Preface

A high-level overview of the security part of the workshop.

10 minutes

Security in MySQL: key considerations

·         Look into mysql_secure_installation

·         Security plugins

·         General factors – access control, preventing SQL injection etc.

·         Strong passwords

·         Backups

30 minutes

Security in MySQL: access control – accounts

·         Avoid giving accounts access to the user table.

·         Only grant necessary privileges.

·         Security through obscurity.

·         Account locking & unlocking.

30 minutes

Security in MySQL: privileges & roles

·         LOAD DATA INFILE

·         LOAD DATA & INTO OUTFILE

·         local_infile

20 minutes

Security in MySQL: password management & security

·         Periodic password changes.

·         Disallow the use of previously used passwords, generate them, use the ability to check their strength.

·         You can lock out an account from accessing a DB.

20 minutes

MySQL security plugins

·         Authentication & connection control plugins.

·         The password validation plugin.

·         Audit & firewall plugins.

20 minutes

MySQL backup security

·         Encrypt your backups.

·         Use mysqldump-secure.

·         Avoid putting your password in the console – use my.cnf instead.

20 minutes

Summary

·         If you want high performance with high reliability, you should probably use InnoDB.

·         If you use InnoDB, you can either make it ACID compliant or get a very fast write speed, but lose up to one second’s worth of transactions.

·         Keep an eye on security – account security, privileges & roles, backups, look into MySQL security plugins.

·         Don’t neglect performance.

10 minutes