3

Since MySQL 5.5 performance_schema database has been added likewise information_schema. As we know that tables in information_schema contains statistical information like tables, plugins, partitions, processlist, status and global variables etc. Just like, performance_schema contains a lot of tables.

  • But, how can we use these tables to improve performance of an application based on MySQL 5.6?
Improve this question
1
  • 2
    Actually, performance schema may worsen your performance :-) (it is accepted that with the default configuration, it takes a 5-10% penalty) and considerable memory footprint with bad max_connections configuration. But check Rolando's links at the bottom for ways it can help profile yourself MySQL by providing runtime statistics.
    – jynus
    Commented Jul 11, 2014 at 17:44

1 Answer 1

Reset to default
7

Please note the difference between the information_schema and performance_schema databases

INFORMATION_SCHEMA

The information_schema database is an inventory of all objects within the MySQL instance

Such objects include:

  • databases
  • tables
  • columns
  • constraints
  • indexes (called statistics)
  • processlist
  • locks

I wrote a nice post about this 3 years ago : How is INFORMATION_SCHEMA implemented in MySQL?

PERFORMANCE_SCHEMA

The performance_schema database is an instrumentation platform that collects statistics about phases of a running query. Such info can give you hints to tune the query or the environment. You only need to enable it when debugging and troubleshooting queries in conjunction with adding indexes (may or may not be necessary) and adjusting buffer settings (again, may or may not be necessary).

I have not written much about performance_schema since I do not use it that often (Mentioned in my old post Is there any way to get the estimate cost of executing a query in MySQL?).

Derek Downey also wrote a quick post on it : What is the usage of performance_schema in MySQL 5.5?

There is a wealth of information on how to use the performance_schema I think you will enjoy

Improve this answer
1
  • Last link happens to be dead.
    – sjas
    Commented Jun 27, 2017 at 6:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.