MySQL setting optimizations

[Mobius]

PART 1
Use InnoDB instead on MyISAM.
Using InnoDB as the storage engine for your MySQL database offers several advantages that make it a preferred choice for many applications, including MMORPG servers:

1. ACID Transactions:
InnoDB supports ACID (Atomicity, Consistency, Isolation, Durability) transactions. This means that database transactions are processed in a way that ensures they are either fully completed or fully rolled back, maintaining data integrity and consistency.

2. Referential Integrity:
InnoDB supports foreign keys and enforces referential integrity between tables. This ensures that relationships between tables are maintained, preventing orphaned records and ensuring data consistency.

3. Concurrency and Multi-Versioning:
InnoDB handles concurrent transactions efficiently through multi-versioning. Multiple transactions can be executed simultaneously without locking the entire database, allowing for high levels of concurrency. InnoDB uses row-level locking, enabling multiple transactions to access different rows in the same table simultaneously.

4. Crash Recovery:
InnoDB is designed to be very resilient. In the event of a crash, InnoDB automatically performs crash recovery, bringing the database back to a consistent state without data corruption.

5. Automatic Deadlock Detection and Resolution:
InnoDB can detect deadlocks and automatically resolve them by rolling back a transaction that is causing the deadlock, allowing other transactions to proceed.

6. Storage Efficiency:
InnoDB uses a clustered index (the primary key) for data storage. This clustered index organization can lead to more efficient use of disk space and faster data retrieval for primary key lookups.

7. Online Backup and Table-level Locking:
InnoDB supports online backup using tools like mysqldump or Percona XtraBackup. Additionally, it supports both table-level and row-level locking, allowing for a balance between data integrity and performance optimization.

8. Support for Full-Text Search:
InnoDB supports full-text search capabilities, allowing you to perform complex searches on textual data efficiently.

9. Community and Documentation:
InnoDB is well-supported, widely used, and has extensive documentation and community support. If you encounter issues or need help, there are ample resources available.

In summary, InnoDB is a robust and feature-rich storage engine that provides excellent support for transactions, concurrency, data integrity, and crash recovery. These features make it a solid choice for applications like MMORPG servers where data consistency, concurrent access, and reliability are crucial. However, the choice of storage engine should be based on your specific application requirements and workload characteristics.



Query for converting all MyISAM database tables to InnoDB.

Code: [Select]

SET @DATABASE_NAME = 'l2jmobius';
SELECT  CONCAT('ALTER TABLE `', table_name, '` ENGINE=InnoDB;') AS sql_statements
FROM    information_schema.tables AS tb
WHERE   table_schema = @DATABASE_NAME
AND     `ENGINE` = 'MyISAM'
AND     `TABLE_TYPE` = 'BASE TABLE'
ORDER BY table_name DESC


Continue with Part 2 to see how to change MySQL configurations, including how to set default engine to InnoDB.

[Mobius]

PART 2
MySQL Settings Optimization.
Optimizing the MySQL configuration is crucial for ensuring optimal performance and stability of your server.
Proper configuration settings can significantly enhance the server's ability to handle a large number of concurrent players and database transactions.
This guide outlines key configuration parameters and best practices to follow.



A. Backup the Current Configuration:
Before making any changes, back up the existing my.ini configuration file and database. This allows us to revert to the previous settings if necessary.



B. Modify MySQL Configuration File:

• For Windows, the configuration file is often located in the MySQL installation directory and is named my.ini.
• For Linux-based systems, it's typically located in /etc/mysql/my.cnf or /etc/my.cnf.

1. Set Default Engine to InnoDb:
Add or modify the following line under the [mysqld] section of the configuration file.

Code: [Select]

default-storage-engine = InnoDB

2. Increase InnoDB Buffer Pool Size:
Increase the InnoDB buffer pool size to utilize more memory for caching data and indexes. Adjust the size based on the available RAM on your server.

Code: [Select]

innodb_buffer_pool_size = 2G

3. Increase Query Cache Size:
Increase the query cache size to allow for more queries to be cached. Adjust this based on your workload and available memory.

Code: [Select]

query_cache_size = 256M

4. Increase Key Buffer Size:
Increase the key buffer size for MyISAM tables. Adjust this based on the number and size of your MyISAM indexes.

Code: [Select]

key_buffer_size = 512M

5. Optimize InnoDB Log Files:
Increase the InnoDB log file size and log buffer size to improve write performance. Adjust the innodb_flush_log_at_trx_commit value to balance between performance and reliability.
Use O_DIRECT if your system supports it. It bypasses the operating system cache, which can improve performance.

Code: [Select]

innodb_log_file_size = 256M
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT

6. Adjust Sort and Read Buffer Sizes:
Increase sort and read buffer sizes to improve query performance. Adjust these values based on your query patterns.

Code: [Select]

sort_buffer_size = 16M
read_buffer_size = 8M
read_rnd_buffer_size = 16M

7. Increase Max Allowed Packet:
Increase the maximum allowed packet size to handle larger communication packets between the server and clients.

Code: [Select]

max_allowed_packet = 128M

8. Increase Max Allowed Connections:

• max_connections: Maximum number of simultaneous client connections. Adjust based on your server's capacity.
• max_user_connections: Maximum connections allowed per user. Adjust according to your application requirements and user concurrency.

Code: [Select]

max_connections = 700
max_user_connections = 500


*Configurations may differ between database versions.
In any case, if a configuration is not found, it can be added manually.

*Restart MySQL service, or your server machine, to ensure your changes are applied.

[Mobius]

Part 3
Increase Database Connections.

Configure MySQL Maximum Connection Values (see Part 2 above)
Inside the [mysqld] section, add or modify the max_connections variable to set it to 700 and max_user_connections to 500.

Code: [Select]

[mysqld]
max_connections = 700
max_user_connections = 500
The above values set limits for total dedicated machine connection count.
500 (game) + 10 to 50 (login) + 150 (site and everything else)


GameServer configuration files.
game\config\Server.ini

Code: [Select]

MaximumDbConnections = 500

LoginServer configuration files.
login\config\LoginServer.ini

Code: [Select]

MaximumDbConnections = 50

*Make sure your server has enough RAM to handle the increased number of connections. Each connection consumes some amount of memory.
If you allow too many connections and your server runs out of memory, it can lead to performance issues and crashes.

[Mobius]

PART 4.
Performance Schema Logging.


1. Check if Performance Schema is Enabled:
Ensure that the Performance Schema is enabled in your MySQL configuration file (usually my.cnf or my.ini). Look for the line performance_schema=ON.
If it's set to OFF, change it to ON and restart MySQL.


2. Check if the Performance Schema Tables are Populated:
Run the following query to check if there is any data in the events_statements_summary_by_digest table:

Code: [Select]

SELECT * FROM performance_schema.events_statements_summary_by_digest LIMIT 10;If this query returns results, the Performance Schema is working correctly, and there should be data available for analysis.


3. Check the Time Window for Data:
If your MySQL server was recently restarted or if the Performance Schema tables were reset, there might not be enough data to show meaningful results. Data in the Performance Schema can be limited based on the time window of your server's uptime or the last reset time.



You can reset the Performance Schema data with the following command:

Code: [Select]

TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;After resetting the table, wait for some time and then re-run your query to see if it captures any queries.



*When using any of these methods, it's important to be mindful of the potential performance impact, especially in a production environment.

[Mobius]

PART 5
Adding Table Indexes.
Adding indexes to tables in a database can significantly improve query performance and speed up data retrieval. Indexes are data structures that provide a faster way to look up rows based on the values in specific columns.


Here's how adding indexes can help:
1. Faster Data Retrieval:
Selective Queries: Indexes speed up SELECT statements that have conditions in the WHERE clause by allowing the database engine to quickly locate the rows that satisfy the conditions.
Sorting and Grouping: Indexes enhance the performance of ORDER BY and GROUP BY clauses by allowing the database to retrieve sorted or grouped data more efficiently.


2. Efficient Joins:
When you join tables using columns with indexes, the database engine can use these indexes to efficiently match rows, reducing the time it takes to execute complex JOIN operations.


3. Accelerated Aggregations:
Indexes can speed up aggregate functions (SUM, AVG, COUNT, etc.) by allowing the database to quickly find the relevant rows for computation.


4. Optimized Searching:
Indexes help in speeding up searches, especially for columns that are frequently searched or used in WHERE clauses. This is particularly helpful for large tables.


5. Enhanced Uniqueness and Constraints:
Indexes can enforce uniqueness in columns by creating unique indexes. This ensures that values in the indexed columns are unique across the table.
Indexes are also used for foreign key constraints, ensuring referential integrity between tables.


6. Reduced Disk I/O:
Indexes reduce the amount of data that needs to be read from the disk by providing a shortcut to the relevant data. This can significantly reduce disk I/O operations, leading to faster query execution times.


7. Query Optimization:
The database optimizer uses indexes to generate efficient execution plans for queries, ensuring that the most optimal paths are taken to retrieve data.


8. Impact on Write Operations:
While indexes speed up read operations, they can slightly slow down write operations (INSERT, UPDATE, DELETE) because the indexes need to be updated whenever the data in the indexed columns changes. However, the impact is often negligible, especially for small to medium-sized databases.



Tips for Effective Indexing:

• Identify High-Volume Queries: Analyze which queries are frequently used and optimize the indexes for those specific queries.
• Covering Indexes: Create covering indexes that include all columns needed for a query. This can eliminate the need to access the actual table data, further improving performance.
• Avoid Over-Indexing: Index only the columns that are frequently used in WHERE clauses, JOIN conditions, or ORDER BY clauses. Over-indexing can lead to unnecessary overhead.
• Regular Maintenance: Regularly monitor and maintain indexes. Unused or redundant indexes should be removed, and missing indexes for frequently used queries should be added.

By strategically adding indexes to your database tables based on the specific queries your server uses, you can significantly enhance the overall performance and responsiveness of your application.