OurSQL Episode 141: Performance Enhancements

This week we talk about server and status variables relating to the performance schema and the ps_helper tool. Ear Candy is an sql_mode bug, and At the Movies is a performance_schema and ps_helper webinar.

Performance Schema Variables
MySQL 5.5 performance schema variables
MySQL 5.6 performance schema variables - many options are auto-sized

SHOW GLOBAL VARIABLES LIKE 'performance_schema%';
SHOW GLOBAL STATUS LIKE 'performance_schema%';

performance_schema_max_file_handles

performance_schema_max_thread_instances in MySQL 5.5 - should be just larger than max_connections + max_delayed_threads

performance_schema_max_thread_instances in MySQL 5.6 - autosized by default
Bug 69318, about documentation for performance_schema_max_thread_instances

Episode 130, where we talked about deprecated features including INSERT DELAYED

Episode 133, where we talked about autosized variables

performance_schema_max_statement_classes

ps_helper
ps_helper web page including downloads, explanations and examples

dump_thread_stack

latest_file_io

SELECT * FROM ps_helper.statements_with_full_table_scans LIMIT 10;

Ear Candy
sql_mode

SET sql_mode='';
-- create a table using the following definition:
CREATE TABLE date_table (
   id int(11) not null auto_increment,
   txt varchar(8),
   dt timestamp default '0000-00-00 00:00:00'
) ENGINE=InnoDB;
SET sql_mode=traditional; -- this mode implies "no zero in date"
INSERT INTO date_table(txt) VALUES (‘1st line’);
-- this will set the default value for the dt column to '0000-00-00 00:00:00' even though the sql_mode is set to 'no zero in date'
INSERT INTO date_table(txt, dt) VALUES (‘2nd line’, ‘0000-00-00 00:00:00’);
-- this will abort with an error:
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'dt' at row 1

At the Movies
This week in at the movies we present a webinar that Peter Zaitsev of Percona gave called Using MySQL 5.6 Performance Schema to Troubleshoot Typical Workload Bottlenecks. You will find the beginning content to be a review since we covered it in episodes 139 and 140. However, the examples are pretty rich, especially the ps_helper ones, and you can see real-world examples of the very detailed views for schema_table_statistics and schema_index_statistics.

Timings:
25:00 - talking about performance schema overhead
- bug 68413 by Mark Callaghan about performance schema overhead
- Dimitri K's blog post about performance schema overhead
29:20 - ps_helper tool information starts
30:00 - Peter shows a slide about the statements_analysis table that says "Can't order by total latency"
32:40 - schema_table_statistics example
34:00 - schema_index_statistics example
52:40 - Marc starts to answer some good questions about performance schema.

Where you can see us
Gerry will be at the June Seattle MySQL Meetup on Monday June 3rd when Rob Smith is going to talk and show us 2 of his projects: Diamond, a flexible light monitoring tool similar to collectd and his query digest UI, a tool to create reports from the slow query log.

Sheeri will be at the June Boston MySQL User Group meeting on Monday June 10th at MIT, where Hiromichi of Parallel Universe, Inc. will talk about the industry's only SQL server with fast parallel query engine. It is created by extending MySQL server architecture and runs on commodity server hardware. Speed is achieved by processing tables in parallel utilizing multiple core/CPU of server hardware and scalable to large joins.

Sheeri will be speaking and assisting at Wise Bootcamp for women in science and engineering, June 23-24 at the microsoft NERD Center in Cambridge, MA.

Feedback
Facebook group
Google+ page
e-mail: podcast at technocation.org
voicemail using phone/Skype: +1-617-674-2369
twitter: @oursqlcast
or Tweet about @oursqlcast