MySQL Forge

Resources for the MySQL Community

Category: UC2006

Certification Primer II: MySQL 5.0 DBA

Contents 1 Certification Primer II: MySQL 5.0 DBA 1.1 Slide 5: Stored Procedures 1.2 Slide 6: Stored Procedures (Security) 1.3 Slide 7: Stored Procedures (Performance) 1.4 Slide 8: Questions on Stored Procedures 1.5 Slide 10: MySQL User Accounts 1.6 Slide 11: Using Host in Users 1.7 Slide 12: Privileges 1.8 Slide 13: Privilege Storage 1.9 Slide 14: SQL Statements 1.10 Slide 16: Grant 1.11 Slide 19: Drop User 1.12 Slide 21: Show Grants 1.13 Slide 22: Resource Limits 1.14 Slide 23: Client Access Control 1.15 Slide 24: Questions on privileges 1.16 Slide 25: Questions (2) on privileges 1.17 Slide 29: Network Security 1.18 Slide 31: Questions on Security 1.19 Slide 32: Upgrade-Related Security 1.20 Slide 33: Questions 1.21 Slide 35: Finding Queries 1.22 Slide 37: Explain: Sample Output 1.23 Slide 42: Types in Explain 4/5 1.24 Slide 47: Questions 1.25 Slide 48: General Table Optimization 1.26 Slide 52: MyISAM Optimizations 1.27 Slide 53: InnoDB Optimizations 1.28 Slide 54: Memory Optimizations 1.29 Slide 56: Questions 1.30 Slide 58: Server Variables/Status 1.31 Slide 59: Global Variables 1.31.1 MyISAM-specific 1.31.2 InnoDB-specific 1.32 Slide 60: Local Variables 1.33 Slide 61: Query Cache 1.34 Slide 62: Questions 2 See Also

[edit] Certification Primer II: MySQL 5.0 DBA

[edit] Slide 5: Stored Procedures

• A set of SQL statements that can be stored on the server

• Can be used to ensure a consistent operation by clients written in different programming languages

• Invoked by the CALL statement and passes back values using output variables
  • CREATE PROCEDURE sp_test (OUT output1 INT) -- the parameter output1 is used to store a value generated from within the stored procedure
  • See http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html for syntax

[edit] Slide 6: Stored Procedures (Security)

• Access to stored procedures has to be explicitly granted for other users to use them

• Super-users can create stored procedures with a different users as its definer

• Specified during stored procedure creation

[edit] Slide 7: Stored Procedures (Performance)

• Combine queries to increase performance by:
  • reducing network communication/traffic
  • reducing the amount of database calls

• Server-side (vs client-side processing)
  • The DBMS _may_ be faster in doing some operations (e.g. sorting, filtering)
  • Scalability can become an issue -- DBMS will be doing more work
  • Depends on the application

[edit] Slide 8: Questions on Stored Procedures

• Correction: The line containing INVOKER=DEFINER should read SQL SECURITY = INVOKER instead

• Answer: B. 'fred'@'%'

The USER() function returns the current MySQL username and hostname used to connect to the server. Because the security mode used INVOKER, the stored procedure is run as the user executing it, causing the USER() function to return the calling user's credentials.

[edit] Slide 10: MySQL User Accounts

• User and Host information are combined to make up a key

• Order of creation of accounts does not matter

[edit] Slide 11: Using Host in Users

• The '%' wildcard matches zero (0) or more characters (e.g. 'foo%' will match 'foo' and 'food')

• The '_' wildcard matches a single character (e.g. 'h_' will match 'ha', 'he', 'hi', 'ho', etc.)

• Note that CIDR Notation does NOT work (i.e. '192.168.0.0/16')

[edit] Slide 12: Privileges

• Privileges are evaluated from most specific to most general (from column, table, ... to global)

• Administrative privileges work on the global scope, affecting ALL databases and their underlying members

[edit] Slide 13: Privilege Storage

• FLUSH PRIVILEGES syncs the in-memory cache of the privileges and authentication information from the disk copy

[edit] Slide 14: SQL Statements

• Account management SQL statements automatically update both the disk and memory copies (think of it has having implicit FLUSH PRIVILEGES)

[edit] Slide 16: Grant

• NO_AUTO_CREATE_USER can be used to prevent GRANT from creating an account IF no password is specified
  • --sql-mode=NO_AUTO_CREATE_USER[,...]
  • SET [GLOBAL|SESSION] sql_mode='NO_AUTO_CREATE_USER'

• Can be fine-grained to the column level, allowing only certain columns to be accessed

[edit] Slide 19: Drop User

• In MySQL 4.1, can only be used on users with no privileges remaining

• In MySQL 5.0, can be used to completely remove a user account and all its privileges

[edit] Slide 21: Show Grants

• USER() -- returns the username/hostname credentials the client provided the server to connect

• CURRENT_USER() -- returns the actual username/hostname credentials the server authenticated the user

• Example: A client logs in with username 'john' from the localhost to database 'payroll'

- USER() will return 'john'@'localhost' - CURRENT_USER() may return @'localhost' IF there is no 'john'@'localhost' or 'john'@'%' accounts defined, but an anonymous one from the localhost is.

• See http://dev.mysql.com/doc/refman/5.0/en/information-functions.html

[edit] Slide 22: Resource Limits

• MAX_USER_CONNECTIONS restricts the number of concurrent connections allowed for a given account. It is global to all the databases in the DBMS.

[edit] Slide 23: Client Access Control

• Query access control is checked after the parser succeeds

• FLUSH PRIVILEGES will re-enable the grant tables that were disabled by --skip-grant-tables

[edit] Slide 24: Questions on privileges

Answer: B. GRANT SELECT, UPDATE ON WORLD.* TO bob@'%'

The first SQL statement creates the USAGE privilege for user 'bob'@'%'. The second SQL statement adds the SELECT privilege. The third SQL statement adds the UPDATE privilege.

[edit] Slide 25: Questions (2) on privileges

A. Yes

WITH GRANT OPTION allows the user to impart all or a subset of its privileges to other users.

[edit] Slide 29: Network Security

• --bind-address will cause MySQL to listen on only the specified IP address

• There is NO network security for MySQL Cluster (use only on secure network!)

• SHOW CREATE TABLE will reveal the username/password information used for FEDERATED tables

[edit] Slide 31: Questions on Security

• Answers:
  • C. Sets a root password
  • D. Removes the anonymous user

• Answer:
  • A. Allows MySQL to only listen on a single address

[edit] Slide 32: Upgrade-Related Security

• Safe to run mysql_fix_privilege_tables several times (subsequent calls shouldn't do anything bad)

[edit] Slide 33: Questions

• Answer: C. Whether invalid dates are allowed

See: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

• Answer: C. When upgrading a major release of MySQL

[edit] Slide 35: Finding Queries

• Slow log has a minimum threshold of 1 second, default is 10 seconds

• mysqldumpslow can be run on the slow query log file to summarize the queries that appear in the log

[edit] Slide 37: Explain: Sample Output

• Per-row stats:
  • possible_keys: which key/index may be used in the query
  • key: which key/index was actually used
  • rows: number of rows used

• For statements with joins, EXPLAIN returns each row equal to the number of conditions specified (e.g. WHERE A='foo' AND B='bar' will generate 2 rows: one for A='foo' and another for B='bar')
  • Each subsequent 'rows' stat that appears in the output represents the number of times it matched the previous row's results

[edit] Slide 42: Types in Explain 4/5

• The 'index' appears in type also if selecting from same index as column

[edit] Slide 47: Questions

• Answer: C. MySQL has to do a full index scan
  • 'index' is second-to-last-resort in finding out which rows to include

• Answer: B. SELECT
  • EXPLAIN only works with SELECT statements and shows the query execution plan

[edit] Slide 48: General Table Optimization

• SELECT ... FROM ... WHERE ... PROCEDURE ANALYSE(MAX_ELEM[,MAX_MEM])
  • examines result of query
  • (16,256) = good value to use for most cases

[edit] Slide 52: MyISAM Optimizations

• Compressed tables
  • Faster on full table scans (since its smaller)
  • Slower for key cached look-ups

• Default data pointer size is 6-bytes
  • Define MAX_ROWS / AVG_ROW_LENGTH per table and MySQL will auto-compute data pointer size
  • Keep low to get a smaller size and speed things up

[edit] Slide 53: InnoDB Optimizations

• Use VARCHAR instead of CHAR unless a fixed-size is determined/desired

[edit] Slide 54: Memory Optimizations

• Use HASH only for equalities, since its faster and smaller

• No defragmenting is needed

[edit] Slide 56: Questions

• Answer: C. Variable-Width
  • VARCHAR = variable characters ;-)

• Answer: D. INT
  • Integers are very light, sortable, and make good indexes

[edit] Slide 58: Server Variables/Status

• variables can be referred to in SELECT SQL statements by prefixing them with @@global. for global variables and @@local. for session variables (e.g. @@global.var for var in the global context and @@local.foo for foo in the session context)

[edit] Slide 59: Global Variables

• max_connections
  • number of concurrent connections that can be serviced

• table_cache
  • the number of open tables for all threads
  • Default is 64, compare to open_tables to see how much is actually used
  • Normally, set it to <concurrent connections> * <avg_tables_to_join_in_most_queries>
  • the higher the value, the more file descriptors are kept open (may be an issue with the OS)
    • Take note that MyISAM requires 2 file descriptors for each open table

• Remember: both max_connections and table_cache affect the number of open file descriptor used!

[edit] MyISAM-specific

• key_buffer_size
  • global cache for MyISAM indexes
  • Size Strategy 1: Set to 1/4 <= x < 1/2 of RAM
  • Size Strategy 2: Look at key_reads (mem+disk) and key_read_requests (mem). Keep ratio at least 100:1.

[edit] InnoDB-specific

• innodb_buffer_pool_size
  • equivalent of key_buffer_size for InnoDB
  • Set as high as possible to minimize disk access
  • In InnoDB-exclusive environments, can be set up to 50%-80% of RAM

• innodb_log_buffer_size
  • Keep small, do not increase with system memory
  • Keep between 1M-8M

[edit] Slide 60: Local Variables

• read_buffer_size
  • used when doing full table scan

• sort_buffer_size
  • using filesort in EXPLAIN, manually sort

• join_buffer_size
  • Joins that don't use indexes

• max_allowed_packet
  • how large a single SQL statement can be
  • how large a single row of result set can be

[edit] Slide 61: Query Cache

• Exact same queries are cached

• query_cache_size
  • Sets aside memory to be used to cache frequently used queries
  • Setting too large will deprive memory for uncached queries and may affect performance
  • See qcache_% variables which will help find a good value for the size

• query_cache_type
  • ON will cache all SELECT queries (no change needed to your app)
  • DEMAND is good for apps with most queries NOT benefiting from the cache (results are invalidated after the query)

• qcache_inserts
  • how many queries are inserted into the cache

• qcache_hits
  • how much of requests hitting the cache are hits

• qcache_lowmem_prunes
  • the number of cached queries removed from the query cache
  • if rate increases quickly, consider raising the value of query_cache_size

[edit] Slide 62: Questions

• Answer: 2. Very read intensive
  • The query cache only caches SELECT statements

Answer: C. max_connections

• • max_connections affects the number of concurrent connections to the DBMS

[edit] See Also

• Certification Primer I: MySQL 5.0 DBA
• Optimizing MySQL: Hardware and mysqld variables
• Optimizing mysqld variables