MySQL Database Management and Optimization Techniques

Installation Methods

System Preparation

Before installing MySQL, verify existing installations using:

rpm -qa | grep -i mysql

Stop services and remove previous installations:

ps -ef | grep mysql
rpm -e --nodeps package_name

For dependency conflicts:

rpm -ev package_name --nodeps
rpm -e --noscripts package_name

Clean remaining directories:

find / -name mysql
rm -rf /path/to/mysql/directories

Note: Manual removal of /etc/my.cnf may be required.

YUM Installation Process

Check for existing MySQL installations:

rpm -qa | grep mysql

Install MySQL server:

yum list mysql-server
wget http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
yum install mysql-server

Start the service and set passwrods:

service mysqld start
mysqladmin -u root -p password 'new_password'

Configure remote access permissions:

GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'%' IDENTIFIED BY 'secure_password';
FLUSH PRIVILEGES;

Source Package Installation

Extract and relocate MySQL packages:

tar -xvf mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
cd /usr/local
mv mysql-5.6.21-linux-glibc2.5-x86_64 mysql

Initialize database:

./scripts/mysql_install_db --user=mysql
service mysql start

Set permissions:

chown -R mysql:mysql ./

Diagnostic Commands

Connection Analysis

View active connections:

SHOW FULL PROCESSLIST;

Configuration Inspection

Display InnoDB settings:

SHOW VARIABLES LIKE "%innodb%";

Check event scheduler status:

SHOW VARIABLES LIKE 'event_scheduler';

Lock Status Monitoring

Identify locked tables:

SHOW OPEN TABLES WHERE In_use > 0;
SHOW INNODB STATUS\G;

Storage Location Verification

Find data directory:

SHOW VARIABLES LIKE '%datadir%';

Timeout Configuration

Display timeout settings:

SHOW VARIABLES LIKE '%timeout%';

Performance Metrics

Buffer pool size inspection:

SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';

Calculate dirty page ratio:

USE performance_schema;
SELECT VARIABLE_VALUE INTO @dirty FROM global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
SELECT VARIABLE_VALUE INTO @total FROM global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
SELECT @dirty/@total;

Maintain dirty page ratio below 75% through proper innodb_io_capacity configuration.

Logging Configuration

Enable slow query logging:

SHOW VARIABLES LIKE 'slow_query%';
SET GLOBAL slow_query_log=ON;
SET GLOBAL long_query_time=1;

Permanent configuration:

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 1

Enable general query logging:

SHOW VARIABLES LIKE '%general_log%';
SET GLOBAL general_log = ON;
SET GLOBAL log_output='table';
SELECT * FROM mysql.general_log;

Troubleshooting Queries

Client connection analysis:

SELECT client_ip, COUNT(client_ip) AS client_num FROM (
    SELECT SUBSTRING_INDEX(HOST,':',1) AS client_ip FROM PROCESSLIST
) AS connect_info 
GROUP BY client_ip 
ORDER BY client_num DESC;

Long-running process identification:

SELECT * FROM information_schema.processlist 
WHERE Command != 'Sleep' 
ORDER BY TIME DESC;

Generate kill statements for problematic processes:

SELECT CONCAT('KILL ', id, ';') FROM information_schema.processlist 
WHERE Command != 'Sleep' AND TIME > 300 
ORDER BY TIME DESC;

Optimization Strategies

Schema Design Principles

Data Type Selection

  • Use smallest appropriate data types
  • Prefer integer over character types
  • Avoid NULL values when possible
  • Limit indexes to fewer than 6 per table

Primary Key Selection

Integer primary keys offer optimal performance with AUTO_INCREMENT support. String identifiers consume more space and create random index placement.

Index Types

Common index types:

  • PRIMARY: ALTER TABLE table_name ADD PRIMARY KEY (col)
  • UNIQUE: ALTER TABLE table_name ADD UNIQUE (col)
  • INDEX: ALTER TABLE table_name ADD INDEX idx_name (col)
  • FULLTEXT: ALTER TABLE table_name ADD FULLTEXT (col)
  • COMPOSITE: ALTER TABLE table_name ADD INDEX idx_name (col1, col2, col3)

Composite indexes follow leftmost prefix principles for optimal utilization.

Algorithm Comparison

Hash vs B-Tree Indexes

Hash indexes provide O(1) lookups for equality operations but don't support range queries or LIKE patterns. B-Tree indexes support range operations and pattern matching with non-leading wildcards.

Hash limitations:

  • No range query support
  • Cannot utilize partial composite index keys
  • No sort avoidance capabilities
  • Always requires table scans

Query Execution Plans

Execution efficiency ranking (best to worst):

  • NULL: No table access required
  • CONST/SYSTEM: Single row lookup
  • EQ_REF: Unique index match
  • REF: Non-unique index lookup
  • RANGE: Index range scan
  • INDEX: Full index scan
  • ALL: Full table scan

Performance Guidelines

  • JOIN conditions should use indexed columns
  • Avoid OR operators
  • Use COUNT(*) instead of COUNT(id)
  • Specify NOT NULL constraints
  • Avoid SELECT * operations
  • Minimize calculasions within SQL statements

Count Optimization

For improved counting performance:

  1. Create indexes on integer fields if no primary key exists
  2. Maintain separate count tracking tables with triggers
  3. Separate pagination and total count queries

Trigger example:

DELIMITER $$
CREATE TRIGGER `row_counter` BEFORE INSERT ON `data_table`
FOR EACH ROW BEGIN
    UPDATE counter_table SET total_rows = total_rows + 1 
    WHERE table_name = 'data_table';
END$$
DELIMITER ;

Write Performance Tuning

Key InnoDB parameters:

  • innodb_buffer_pool_size: Set to 70-80% of available memory
  • innodb_log_file_size: 64MB-512MB depending on workload
  • innodb_log_buffer_size: 8MB-16MB for typical workloads
  • innodb_flush_log_at_trx_commit: Adjust based on durability requirements

Values: 0 (fastest, least safe), 1 (default, safe), 2 (balanced)

Common Issue Resolution

User Account Issues

Create MySQL system user:

groupadd -g 315 mysql
useradd -u 315 -g mysql -d /usr/local/mysql -M mysql
./scripts/mysql_install_db --user=mysql

Service Management Problems

Locate and copy startup script:

find / -name mysql.server
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql

Storage Engine Errors

Error 28 indicates insufficient disk space. Clear unnecessary files from the storage partition.

Character Encoding Issues

For "Data too long" errors, increase field length or standardize on UTF-8 encoding.

Socket connection problems require verifying /tmp/mysql.sock path or creating symbolic links:

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

Command Path Issues

Create symbolic links for MySQL binaries:

ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

Initialization Conflicts

Remove existing data directory contents or adjust configuration files when encountering initialization erors.

Startup Failures

Verify PID file permissions, check for existing processes, remove binary log index files, ensure proper data directory configuration, and consider SELinux settings.

Special Character Support

For emoji and special character support, configure UTF8MB4 encoding:

[client]
default-character-set = utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
[mysql]
default-character-set = utf8mb4

Tags: MySQL Database Optimization Performance Tuning troubleshooting indexing strategies

Posted on Sat, 23 May 2026 23:20:43 +0000 by njm

Hot Tags

©2026 Freaks City