This week I was preparing the exercises for our MySQL/MariaDB for Beginners training. One of the exercises of the training is about MySQL spatial (GIS) features. I always tell customers: “With these features you can answer questions like: Give me all points of interest around me!”
A customer reported last month that MySQL does a full table scan (FTS) if a query was filtered by a INT value on a VARCHAR column. First I told him that this is not true any more because MySQL has fixed this behaviour long time ago. He showed me that I was wrong:
Since MySQL 5.6.2 there is a new MySQL variable called binlog_rows_query_log_events. If you enable this variable MySQL will write the original SQL statements into the binary logs. More information about this variable you can find here:
The InnoDB Log File (innodb_log_file_size) size is possibly too small. Choosing a too small InnoDB Log File size can have significant write performance impacts.
MySQL (InnoDB, PBXT, NDB, TokuDB) support Foreign Keys to show relatations between tables. Those relations can be enforced through Foreign Key Constraints.
InnoDB Flush Log at Transaction Commit is set to a values != 1. This can lead to a loss of committed transactions in case of a power failure or an unclean shutdown of your database.
If you are using MariaDB/MySQL 5.5 and newer you should use several InnoDB Buffer Pool Instances for performance reasons. Some rules to size InnoDB Buffer Pool instances are:
Binlog format MIXED changes the binary log format (ROW or STATEMENT) depending on the queries (deterministic or not). This makes it impossible to define 100% correctly working binary log filter rules.
If the variable flush_time is set to a non-zero value, all tables are closed (and flushed) every flush_time seconds to disk. This can cause unnecessary and high I/O spikes.
The number of table definitions (SHOW CREATE TABLE<br>G) that can be stored in the table definition cache (table_definition_cache). If you have a large number of tables ( > 400) in your database instance, you should consider a larger table definition cache to increase your database throughput and decrease your query latency. The command SELECT COUNT(*) FROM information_schema.tables; shows you how many tables and thus table definitions you have. The global status Open_table_definitions is the current amount of open table definitions.
The Table Open Cache (table_open_cache or old name table_cache) is a cache to store file handles for all threads. The actual value of cache entries can be seen with the global status of open tables (Open_tables). Increasing table_open_cache increases the number of file descriptors (open_files_limit) that MySQL requires. You can check whether you need to increase the Table Open Cache by checking Open_tables and Opened_tables. If the value of Opened_tables is large and you do not use FLUSH TABLES often (which just forces all tables to be closed and reopened), then you should increase the value of the table_open_cache variable.
