Bug #45956 Performance schema: exotic mysqld startup, bad results
Submitted: 6 Jul 2009 0:03 Modified: 14 Jan 2010 18:07
Reporter: Trudy Pelzer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:6.0.12-alpha OS:Any
Assigned to: Marc Alff CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[6 Jul 2009 0:03] Trudy Pelzer
Description:
I'm using mysql-6.0-perfschema.

I see bad results with some exotic mysqld startup methods.

How to repeat:
(1) If I forget to say bin/mysql_install_db,
so that datadir/var isn't set up right, I see crash on startup.
"
libexec/mysqld --user=root
 --performance_schema=1
090705 14:24:03 [Warning] Can't create test file /home/tpelzer/mysql-6.0-perfsch
ema-debug/var/linux-90b4.lower-test
090705 14:24:03 [Warning] Can't create test file /home/tpelzer/mysql-6.0-perfsch
ema-debug/var/linux-90b4.lower-test
libexec/mysqld: Can't change dir to '/home/tpelzer/mysql-6.0-perfschema-debug/va
r/' (Errcode: 2)
090705 14:24:03 [ERROR] Aborting

safe_mutex: Trying to destroy unitialized mutex at sql_repl.cc, line 1027
090705 14:24:03 - mysqld got signal 6 ;
"
etc.

So, okay, I ran mysql_install_db before doing tests (2) and (3).

(2) If I start with a big long history size, and shut down immediately,
I see crash on shutdown.

Start with
libexec/mysqld --performance_schema_events_waits_history_long_size=1000000000000000
(This causes a message
"090705 14:28:38 [Warning] Buffered warnings:
option 'performance_schema_events_waits_history_long_size':
unsigned value 1000000000000000 adjusted to 4294967295".)

Shut down with
bin/mysqladmin --user=root shutdown

This causes a message
"
...
090705 14:35:55 [Warning] Forcing shutdown of 2 plugins
090705 14:35:55 [Note] libexec/mysqld: Shutdown complete

safe_mutex: Trying to lock unitialized mutex at safemalloc.c, line 520
090705 14:35:55 - mysqld got signal 6 ;
"
etc.

(3) If I start with a big cond classes size, I see a segmentation fault:
"
xxx@linux-90b4:~/mysql-6.0-perfschema-debug> libexec/mysqld --performance_schema=1 --performance_schema_max_cond_classes=1000000
Segmentation fault
"

(4) If I start with a big file classes size, EVENTS_WAITS_HISTORY is empty:

Start server with
mysqld --performance_schema=1 --performance_schema_max_file_classes=100000

Start client and look at EVENTS_WAITS_HISTORY:
"
xxx@linux-90b4:~/mysql-6.0-perfschema-debug> bin/mysql --user=root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.12-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from performance_schema.EVENTS_WAITS_HISTORY;
Empty set (0.01 sec)

mysql> show variables like 'performance_schema%';
+---------------------------------------------------+--------+
| Variable_name                                     | Value  |
+---------------------------------------------------+--------+
| performance_schema                                | ON     |
| performance_schema_events_waits_history_long_size | 10000  |
| performance_schema_events_waits_history_size      | 10     |
| performance_schema_max_cond_classes               | 80     |
| performance_schema_max_cond_instances             | 1000   |
| performance_schema_max_file_classes               | 100000 |
| performance_schema_max_file_handles               | 32768  |
| performance_schema_max_file_instances             | 1000   |
| performance_schema_max_mutex_classes              | 200    |
| performance_schema_max_mutex_instances            | 1000   |
| performance_schema_max_rwlock_classes             | 20     |
| performance_schema_max_rwlock_instances           | 1000   |
| performance_schema_max_table_handles              | 100000 |
| performance_schema_max_table_instances            | 50000  |
| performance_schema_max_thread_classes             | 50     |
| performance_schema_max_thread_instances           | 1000   |
+---------------------------------------------------+--------+
16 rows in set (0.00 sec)
"
[7 Jul 2009 14:16] Marc Alff
See related Bug#43533
[15 Jul 2009 18:27] Miguel Solorzano
Thank you for the bug report. Verified as described:

miguel@lara:~/dbs/6.0pfs$ libexec/mysqld --port=3307 --performance_schema_events_waits_history_long_size=1000000000000000
090715 15:24:36 [Warning] Buffered warnings:                                                                             
option 'performance_schema_events_waits_history_long_size': unsigned value 1000000000000000 adjusted to 4294967295       

090715 15:24:36  InnoDB: Started; log sequence number 0 46409
090715 15:24:37 [Note] Event Scheduler: Loaded 0 events      
090715 15:24:37 [Note] libexec/mysqld: ready for connections.
Version: '6.0.12-alpha-debug-perfschema'  socket: '/tmp/mysql.sock'  port: 3307  Source distribution
090715 15:25:17 [Note] Got signal 15 to shutdown mysqld                                             
090715 15:25:17 [Note] libexec/mysqld: Normal shutdown                                              

090715 15:25:17 [Note] Event Scheduler: Purging the queue. 0 events
090715 15:25:18  InnoDB: Starting shutdown...                      
090715 15:25:19  InnoDB: Shutdown completed; log sequence number 0 46409
090715 15:25:19 [Warning] Forcing shutdown of 1 plugins                 
090715 15:25:19 [Note] libexec/mysqld: Shutdown complete                

safe_mutex: Trying to lock unitialized mutex at safemalloc.c, line 520
090715 15:25:19 - mysqld got signal 6 ;
[20 Oct 2009 0:10] Marc Alff
Please retest using mysql-trunk-perfschema, which is the performance schema port for 5.5.

Changing the report to 'Need Feedback'.
[20 Oct 2009 17:34] Peter Gulutzan
With mysql-trunk-perfschema pulled today ...

(1) fixed
(2) fixed, although on shutdown I see:
    Error in my_thread_global_end(): 3 threads didn't exit
(3) fixed
(4) not fixed, now mysqld won't start
[4 Nov 2009 0:02] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89235

2936 Marc Alff	2009-11-03
      Bug#45956 Performance schema: exotic mysqld startup, bad results
      
      When initialization of the performance schema failed,
      the server was internally in disabled state,
      but show variables like "performance_schema" continued to report "ON",
      which is misleading.
      
      With this fix, performance_schema=OFF when initialization fails internally.
      
      Also, more warning/information messages are printed in the server log,
      to explain if/why the performance schema is enabled or disabled at startup.
[10 Nov 2009 18:07] Marc Alff
Documenting previous findings

(4) not fixed, now mysqld won't start
In fact, the server *did* start, but starting the server took several minutes.
This is because mysqld needed to allocate 3.2 Gb of memory on a 4 Gb RAM laptop,
which caused extreme paging and swapping.
The machine was completely killed by mysqld, and unresponsive.

The reason for allocating this memory was:
- max-file-classes=100000 (set in the test)
- max-thread-instances=1000 (by default)

To maintain per thread per instrument class aggregates, the server tried to allocate 1000 * 100000 records, or 100 Millions records for file classes aggregates alone, used for the table EVENTS_WAITS_SUMMARY_BY_THREAD_BY_EVENT_NAME.

All the sizing parameters now have practical limits to avoid this misuse.
[10 Nov 2009 20:59] Marc Alff
Queued in mysql-trunk-perfschema (5.5)
[14 Jan 2010 10:05] Marc Alff
Merged in:
- mysql-next-mr (Celosia / 5.5.99-m3)
- mysql-6.0-codebase (6.0.14)
[14 Jan 2010 18:07] Paul Dubois
Not in any released version. No changelog entry needed.