Login Lag - High MySQL Usage During Player Login

[NinoTheMan]

Hey everyone, We've been experiencing lag spikes during player login on our server, and we've identified that the MySQL process is using a significant amount of CPU (~20-35%) during this time. We've done the following so far to investigate and optimize the issue:---###

What We've Done So Far
1. Profiled the Login Process:
- Measured the performance of the loadPlayer function and other related login operations. These only take ~8ms and don’t appear to be the bottleneck.

2. Checked Creature Login Events:
- Added profiling for all login events, and none seem to cause significant delays (most are under 1ms).

3. Monitored MySQL Queries:

• Used SHOW FULL PROCESSLIST during logins to monitor queries.
• Enabled slow query logging but didn’t capture anything over the threshold (set to 0.1 seconds).
• Analyzed specific login-related queries (e.g., loading player items) using EXPLAIN. For example:

SELECT pid, sid, itemtype, count, attributes
FROM player_items
WHERE player_id = 123
ORDER BY sid DESC;

- The query uses an index on player_id, but we noticed Using filesort due to the ORDER BY sid.

4. Optimizations Tried:

• Added a composite index on player_id and sid to reduce sorting overhead.
• Simplified queries by fetching only necessary columns.
• Temporarily adjusted MySQL settings like innodb_buffer_pool_size and sort_buffer_size for better performance.

The Issue Still Persists
Despite these efforts, we still observe lag spikes during logins when multiple players log in concurrently. MySQL’s CPU usage can spike to 20-35%, even with optimized queries.

Questions for the Community
1. Has anyone else experienced similar MySQL bottlenecks during player logins?
2. Are there other areas we should investigate (e.g., other tables, triggers, or configurations)?
3. What optimizations have worked for you in reducing MySQL usage during high-load scenarios?

Any advice or shared experiences would be greatly appreciated! Thanks in advance for your help.