Example Codes for Using MariaDB 10.5 sys schema (Performance Schema)

• MariaDB is a popular open-source relational database management system (RDBMS) that's functionally compatible with MySQL.
• The sys schema (or performance schema) is a collection of views, functions, and procedures within MariaDB that provide insights into server performance and resource usage.
• It helps database administrators monitor queries, connections, engine statistics, and other valuable information.

Why it might not be installable in MariaDB 10.5:

• While the sys schema was previously a separate installable component, it has likely been integrated directly into MariaDB 10.5 and later versions.
• This means there's no separate installation process; the functionality is already included in the core MariaDB server.

What you can do:

• If you're using MariaDB 10.5 or newer, you don't need to install the sys schema separately. It's already available for use.
• To access the performance schema features, you can connect to your MariaDB server and use SQL statements to query the relevant views, functions, and procedures within the sys schema.

Additional considerations:

• If you're unsure about the presence of the sys schema in your MariaDB version, you can check the documentation or consult with a MariaDB administrator.
• In rare cases, there might be specific reasons why the sys schema functionality might not be fully enabled by default. However, this is less common in recent MariaDB versions.

Example Codes for Using MariaDB 10.5 sys schema (Performance Schema)

Check if Performance Schema is enabled:

SHOW VARIABLES LIKE 'performance_schema';

This will output a row indicating performance_schema with a value of ON if it's enabled.

View current connections:

SELECT * FROM information_schema.processlist;  -- Shows basic information
SELECT * FROM performance_schema.users;         -- More detailed user connection info

Monitor table I/O statistics:

SELECT
  object_schema, object_name, count_reads, count_writes, sum_timer_read/1000000 AS read_time_ms, sum_timer_write/1000000 AS write_time_ms
FROM performance_schema.table_io_waits_summary BY INDEX;

Analyze slow queries:

SELECT * FROM performance_schema.events_waits_summary BY EVENT_NAME
ORDER BY avg_timer_wait DESC
LIMIT 10;

This shows the top 10 events that are causing the most waiting time, helping you identify slow queries.

Track query execution details:

SELECT * FROM performance_schema.statements_summary WHERE FULL_SCAN > 0;

This identifies queries that are doing full table scans, which might be inefficient for large tables.

• MariaDB offers built-in query profiling capabilities. You can enable profiling for specific queries or globally to track their execution time, I/O operations, and other details.
• This can be helpful for identifying bottlenecks within individual queries.

Third-Party Monitoring Tools:

• Several third-party tools specialize in database performance monitoring. These tools often provide a graphical interface, historical data collection, alerting mechanisms, and deeper analysis capabilities compared to querying the sys schema directly.
• Popular options include:
  • MySQL Enterprise Monitor
  • Percona Monitoring and Management
  • Datadog
  • Prometheus with exporters like mysql_exporter

Operating System Monitoring Tools:

• The operating system (OS) you're running MariaDB on might also offer tools for monitoring resource utilization, such as CPU, memory, disk I/O, and network traffic.
• By correlating OS metrics with database activity, you can gain insights into potential resource constraints affecting performance.
• Common tools include:
  • Linux: top, iostat, vmstat
  • Windows: Task Manager, Performance Monitor

Choosing the Right Method:

The best approach depends on your specific needs and preferences. Here's a general guide:

• For basic performance analysis: Start with the sys schema. It's readily available and provides valuable insights.
• For deeper analysis or historical data: Consider third-party monitoring tools. They offer a more comprehensive view and advanced features.
• For real-time monitoring and alerting: Third-party tools or a combination of sys schema queries and OS monitoring can be effective.