Slow start up due to performance_schema overhead
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.6 |
Triaged
|
Medium
|
Unassigned | |||
5.7 |
Invalid
|
Undecided
|
Unassigned |
Bug Description
Customer upgraded from 5.5 to 5.6 and start up times went up 10x or more.
Testing showed that using smaller table_definitio
- lfind
- 54.51% lf_hash_search
find_
end_
fil_
dict_
innobase_
- 45.48% lf_hash_insert
find_
end_
fil_
So we tested disabling performance_schema and left table_definitio
To repeat:
- setup 5.6
- create 700k tables
- edit my.cnf and set table_definitio
- restart the instance (and measure start up time in error log)
- edit my.cnf and set "performance_
- restart the instance (and measure start up time in error log again)
- start up times after disabling P_S should be far shorter.
○ → cat sandboxes/
# The MySQL Sandbox
# Copyright (C) 2006-2013 Giuseppe Maxia
# Contacts: http://
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
[mysql]
prompt='mysql [\h] {\u} (\d) > '
#
[client]
user = msandbox
password = msandbox
port = 6666
socket = /tmp/mysql_
[mysqld]
user = marcos.albe
port = 6666
socket = /tmp/mysql_
basedir = /home/marcos.
datadir = /home/marcos.
tmpdir = /home/marcos.
lower_case_
pid-file = /home/marcos.
bind-address = 127.0.0.1
# slow-query-log = on
# slow-query-
# general-log = on
# general-
#
# additional options passed through 'my_clause'
#
log-error=
skip-grant-tables
auto_increment_
auto_increment_
character_
connect_timeout = 30
default-
innodb_
innodb_
innodb_
innodb_
innodb_io_capacity = 200 # set to 50% of iops
innodb_
innodb_flush_method = O_DIRECT # fsync is default, but other possibilites are O_DIRECT and O_DSYNC
innodb_file_format = barracuda # necessary for compression
innodb_
innodb_
innodb_
innodb_
innodb_
innodb_
key_buffer_size = 512M
log-slave-updates
slow-query-log = 1
slow-query-log-file = mysqlSlow.log
log_slow_verbosity = full
log_bin = masterLog
log-warnings-
long_query_time = 20
expire_logs_days = 30
max_binlog_files = 40
max_allowed_packet = 32MB
max_connect_errors = 9000000
max_connections = 2048
max_heap_table_size = 128M
max_tmp_tables = 128
myisam_
query_cache_size = 64M
read_buffer_size = 4M
relay-log = relay
replicate-
replicate-
report-host = chddb1
server-id = 227
skip-name-resolve
slave-skip-errors = all
skip-slave-start
sort_buffer_size = 8M
thread_cache = 256
tmp_table_size = 128M
wait_timeout = 1800
user = mysql
table_definitio
lock_wait_timeout = 600
[mysqld_safe]
flush_caches
numa_interleave
open-files-limit = 450000
core-file-size = unlimited
Upstream?