centos mysql资源占用

Monitoring MySQL Resource Usage in CentOS

To effectively manage MySQL resource usage on CentOS, you first need to establish a baseline of its current consumption. Key tools for this include:

• System-Level Monitoring: Use top (press Shift+M to sort by memory) or htop to view real-time CPU, memory, and process usage. The mysqld process is the main MySQL daemon—monitor its resource consumption here.
• MySQL Internal Commands: Log in to MySQL (mysql -u root -p) and run:
  • SHOW PROCESSLIST; to list all active connections and their executing queries. Look for long-running queries (high Time values) or queries stuck in states like “Copying to tmp table.”
  • SHOW STATUS LIKE 'Threads_connected'; to check the number of active connections (high values may indicate connection leaks).
  • SHOW STATUS LIKE 'Innodb_buffer_pool_read%'; to assess InnoDB buffer pool efficiency (high read_requests vs. reads indicates effective caching).

Identifying Resource Bottlenecks

Once you’ve gathered baseline data, pinpoint the specific bottleneck:

• High CPU Usage: Often caused by inefficient queries (e.g., missing indexes leading to full table scans) or excessive connection switching. Use SHOW PROCESSLIST; to identify queries with high CPU time, then analyze them with EXPLAIN to check index usage.
• High Memory Usage: Typically from oversized buffers (e.g., innodb_buffer_pool_size) or too many concurrent connections. Review MySQL’s memory configuration and connection count.

Optimizing MySQL Configuration for Resource Efficiency

Adjusting MySQL’s configuration file (/etc/my.cnf or /etc/mysql/my.cnf) is critical to balancing performance and resource usage. Focus on these key parameters:

• InnoDB Buffer Pool Size: The most impactful setting for InnoDB performance. It should be set to 50%-70% of available system memory (e.g., innodb_buffer_pool_size=8G for a 16GB server). This buffer caches table and index data, reducing disk I/O.
• Maximum Connections: Limit concurrent connections to prevent memory exhaustion. Set max_connections based on your application’s needs and available memory (e.g., max_connections=200). Use connection pooling to reduce overhead.
• Query Cache: Disable the query cache (query_cache_size=0, query_cache_type=0) for write-heavy workloads, as it can cause contention. For read-heavy workloads, keep it small (e.g., query_cache_size=64M).
• Temporary Tables: Adjust tmp_table_size and max_heap_table_size (e.g., tmp_table_size=64M, max_heap_table_size=64M) to limit the size of in-memory temporary tables. If these values are too small, MySQL will create on-disk temporary tables, increasing I/O.
• Sort/Join Buffers: Set sort_buffer_size and join_buffer_size to reasonable values (e.g., 2M each). Larger values help with complex sorts/joins but increase memory usage per connection.

Advanced Troubleshooting for High Resource Usage

For persistent issues, use advanced tools to drill deeper:

• Slow Query Log: Enable the slow query log to identify queries taking longer than a threshold (e.g., long_query_time=2). Add these lines to my.cnf:

  slow_query_log = 1
  slow_query_log_file = /var/log/mysql/slow.log
  long_query_time = 2
  

  Analyze the log with pt-query-digest (from Percona Toolkit) to find the worst offenders.
• Performance Schema: Enable the performance schema in my.cnf (performance_schema=ON) to track detailed query execution metrics. Use tools like mysqlsla or MySQL Workbench to visualize the data.
• Process Analysis: Use perf to profile MySQL processes and identify CPU hotspots. Install perf (yum install perf), then record and analyze a MySQL process:

  perf record -g -p [MySQL_PID]
  perf report
  

  This shows which functions or queries are consuming the most CPU.

Ongoing Maintenance and Monitoring

Regular maintenance is essential to sustain optimal resource usage:

• Index Optimization: Regularly check for missing indexes using EXPLAIN on slow queries. Add indexes to columns used in WHERE, JOIN, and ORDER BY clauses.
• Table Maintenance: Run OPTIMIZE TABLE on fragmented tables (identified via SHOW TABLE STATUS) to reclaim space and improve performance.
• Backup and Restore: Regularly back up databases (e.g., using mysqldump or Percona XtraBackup) and test restores to ensure data integrity.
• Monitoring Tools: Use tools like mysqltuner (run sudo yum install mysqltuner && mysqltuner) to get automated recommendations for buffer sizes, connection limits, and more. Implement a monitoring solution (e.g., Prometheus + Grafana) to track long-term resource trends.