When you see MySQL consuming 150-200% CPU in top, this indicates the process is utilizing multiple cores. On a quad-core system, 400% would represent full utilization. Your MySQL instance is heavily taxing 2 cores.

First, identify the problematic queries:

# Show running processes
SHOW FULL PROCESSLIST;

# Identify slow queries
SELECT * FROM mysql.slow_log 
ORDER BY start_time DESC 
LIMIT 10;

# Check current locks
SHOW ENGINE INNODB STATUS\G

Your my.cnf reveals several potential bottlenecks:

# Problematic settings in current config
thread_concurrency = 2  # Too low for modern multi-core systems
join_buffer_size = 1M   # May cause excessive memory usage
table_cache = 512       # Potentially too high for your RAM

# Recommended adjustments for 1.5GB RAM server
key_buffer_size = 256M
thread_cache_size = 16
table_open_cache = 200
innodb_buffer_pool_size = 512M
innodb_log_file_size = 64M
query_cache_type = 0    # Disable for high-write environments

Example of optimizing a slow query:

# Before optimization (no index usage)
SELECT * FROM orders WHERE customer_id = 100 
AND order_date > '2023-01-01';

# After adding composite index
ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, order_date);

# Optimized query
EXPLAIN SELECT * FROM orders USE INDEX (idx_customer_date) 
WHERE customer_id = 100 AND order_date > '2023-01-01';

Configure MySQL performance schema:

# Enable performance monitoring
UPDATE performance_schema.setup_instruments 
SET ENABLED = 'YES', TIMED = 'YES' 
WHERE NAME LIKE '%statement/%';

UPDATE performance_schema.setup_consumers 
SET ENABLED = 'YES' 
WHERE NAME LIKE '%events_statements%';

For persistent high CPU usage:

# Create a diagnostic snapshot
pt-mysql-summary --user=root --password=yourpassword > mysql_status.txt
pt-query-digest /var/log/mysql/mysql-slow.log > slow_query_analysis.txt

When you see MySQL consuming 150-200% CPU in top, this indicates the process is utilizing 1.5 to 2 full CPU cores. On multi-core systems, the percentage represents cumulative usage across all cores (100% = 1 core fully utilized).

First, identify the problematic queries:

# Show running queries
SHOW FULL PROCESSLIST;

# Identify slow queries (requires slow query log enabled)
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

# Alternative for current heavy queries
SELECT * FROM sys.processlist WHERE command != 'Sleep' ORDER BY time DESC;

Your my.cnf reveals several optimization opportunities:

# Key adjustments needed:
innodb_buffer_pool_size = 1G  # For InnoDB tables (adjust based on available RAM)
query_cache_size = 0          # Disable query cache (often causes contention)
thread_cache_size = 16        # Better for your max_connections
table_open_cache = 2000       # Should be greater than table_cache

Common performance killers:

# Find tables needing indexes
SELECT table_schema, table_name, index_name
FROM information_schema.statistics
GROUP BY table_schema, table_name
HAVING COUNT(*) = 1;

# Check for full table scans
SELECT * FROM sys.schema_tables_with_full_table_scans;

When the server becomes unresponsive:

# Create emergency configuration
[mysqld]
skip_name_resolve
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 200
innodb_read_io_threads = 8
innodb_write_io_threads = 8

Set up proactive monitoring:

# Sample mytop installation
sudo apt install mytop
mytop --prompt --delay=5 --user=root

# Percona Toolkit for analysis
pt-query-digest /var/log/mysql/mysql-slow.log

For a 1.5GB RAM server:

• Reduce max_connections to 100
• Set innodb_buffer_pool_size to 512M
• Consider adding swap space if not present