Optimizing query performance Flashcards
What are typical causes of MySQL becoming suddenly slow and unavailable
a) a configuration change was made
b) monitoring has not enabled all performance schema instruments
c) The MySQL query cache is disabled
d) Optimize table was not run for Innodb tables
e) the application executed a new untested query
c), d)
Not sure I like this question from udemy - it seems wrong
Udemy said b,c and d
The optimizer_switch system variable enables control over optimizer behavior. Its value is a set of flags, each of which has a value of on or off to indicate whether the corresponding optimizer behavior is enabled or disabled. This variable has global and session values and can be changed at runtime. The global default can be set at server startup.
mysql> SELECT @@optimizer_switch\G
******* 1. row *******
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,prefer_ordering_index=on
1 row in set (0.00 sec)
What is the order of tables shown in an EXPLAIN output?
A. It lists tables from the smallest to the largest.
B. It lists tables in the order in which their data will be read.
C. It lists tables from the most optimized to the least optimized.
D. It lists tables in the order in which they are specified in the statement that is being explained.
Answer: B
EXPLAIN returns a row of information for each table used in the SELECT statement. It lists the tables in the output in the order that MySQL would read them while processing the statement.
Reference: https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
The MySQL error log shows:.
InnoDB: Warning: a long semaphore wait:.
InnoDB: Warning: a long semaphore wait: –Thread 8676 has waited at dict0boot.ic line 36 for 241.00 seconds the semaphore: Mutex at 0000000053B0C1E8 created file dict0dict.cc line 887, lock var 1 waiters flag 1 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0.
Which two options would help avoid the long wait in the future? (Choose two.)
a) Increate the value of the innodb_read_io_threads option **
b) Increate the value of the innodb_lock_wait_timeout option
c) deactivate the query cache
d) set the value of the innodb_adaptive_hash_index to zero **
e) Change the table to use HASH indexes instead of BTREE indexes
f) increase the size of the innodb_buffer_pool
A and D
For a complex join between several tables for which indexes are not used, which of the following is true regarding the join_buffer_size?
A. One join buffer is allocated
B. allocated per-thread
C. multiple join buffers might be necessary
C
One join buffer is allocated for each full join between two tables. For a complex join between several tables for which indexes are not used, multiple join buffers might be necessary.
Unlike many of the buffers that are allocated per-thread (i.e. per-connection), this one is allocated per-join-per-thread.
What is the maximum size for the join_buffer_size
A. there is no maximum
B. Maximum is based on the amount of RAM you have on the OS
C. recommended to be not greater than 4GB
D. recommended to be not greater than 64GB
C.
The maximum permissible setting for join_buffer_size and sort_buffer_size is 4GB–1. Larger values are permitted for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB–1 with a warning). However, such large values should never be used.
Which is true about SORT_BUFFER_SIZE?
A. memory is assigned per connection/thread
B. memory is assigned GLOBALLY
A
Each session that needs to do a sort allocates a buffer of this size. This means that whenever a query needs to sort the rows, the value of this variable is used to limit the size that needs to be allocated. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization.
The first thing you need to know is that sort_buffer_size is a per session buffer. That is this memory is assigned per connection/thread.
In a general purpose MySQL installation on a 64-bit OS and configured with 128G RAM. which is the optimal setting for SORT_BUFFER_SIZE?
A. 12.8 G - 10% of RAM
B. 256K
C. 4G
D. set 1M for each Gigabyte of used memory in MySQL
B.
internally in the OS usage independently of MySQL, there is a threshold > 256K. If the buffer is set to a value higher than 256K, it uses mmap() instead of malloc() for memory allocation. Actually – this is a libc malloc thing and is tunable, but defaults to 256k. A buffer larger than 256K causes a 37x slowdown.
Which of the following storage engines use READ_BUFFER_SIZE?
A. Innodb
B. MyISAM
C. Memory
D. Archive
A and C
Each thread that does a sequential scan for a MyISAM table allocates a buffer of this size (in bytes) for each table it scans. If you do many sequential scans, you might want to increase this value, which defaults to 131072. The value of this variable should be a multiple of 4KB. If it is set to a value that is not a multiple of 4KB, its value will be rounded down to the nearest multiple of 4KB.
This option is also used in the following context for all search engines:
For caching the indexes in a temporary file (not a temporary table), when sorting rows for ORDER BY.
For bulk insert into partitions.
For caching results of nested queries.
and in one other storage engine-specific way: to determine the memory block size for MEMORY tables.
The maximum permissible setting for read_buffer_size is 2GB.
