How to set "performance_schema on" in mysql?

Question

I want to turn performance_schema ON in mysql to collect statistics.

How can I achieve this?

Answer 1

The following guide is linux-specific, but should easily be adaptable to windows.

You have to check wether the mysql server binary was compiled with support for it:

mysqld --verbose --help|grep performance-schema

If the last command returned anything, you are good to go.

Enable it on server startup by putting into /etc/mysql/my.cnf

[mysqld]
performance_schema

Afterwards do a systemctl restart mysqld (redhat) (or systemctl restart mysql on debian/ubuntu) to restart the mysql server.

On how to use the performance schema engine and its output, see the manual:
https://dev.mysql.com/doc/refman/5.5/en/performance-schema-quick-start.html

Answer 2

See the documentation article about it.

"To change the value of Performance Schema system variables, set them at server startup. For example, put the following lines in a my.cnf file to change the sizes of the history tables:"

[mysqld] performance_schema performance_schema_events_waits_history_size=20 performance_schema_events_waits_history_long_size=15000

Remember you have to restart you mysql server after editing the config file.