When running MySQL on Windows Server, you might notice it's surprisingly conservative with memory allocation. By default, MySQL 5.7+ on Windows typically uses only about 25% of available system memory. This is why you see your 8GB server only utilizing 250MB while disk I/O remains high.

These are the primary my.ini settings you need to modify:

[mysqld]
# Set buffer pool size to 4GB (50% of 8GB system)
innodb_buffer_pool_size=4G

# Set key buffer for MyISAM (if used)
key_buffer_size=256M

# Query cache settings (MySQL 5.7+)
query_cache_size=256M
query_cache_type=1

# Connection-related buffers
sort_buffer_size=4M
read_buffer_size=2M
read_rnd_buffer_size=4M
join_buffer_size=4M

# Temporary tables in memory
tmp_table_size=64M
max_heap_table_size=64M

# Thread-specific buffers
thread_stack=256K
thread_cache_size=8

To apply these changes:

1. Locate your my.ini file (typically in C:\ProgramData\MySQL\MySQL Server X.X)
2. Make a backup copy before editing
3. Add or modify the above parameters under the [mysqld] section
4. Restart the MySQL service

After restarting, check memory usage with these SQL commands:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW ENGINE INNODB STATUS\G

Look for these key metrics:

• Buffer pool hit rate should be > 98%
• Physical reads should decrease significantly
• Query response times should improve

For systems running both IIS and MySQL:

# Reserve memory for Windows and IIS
innodb_buffer_pool_size=3G  # Instead of 4G

# Optimize for concurrent connections
table_open_cache=2000
table_definition_cache=1400

Use Windows Performance Monitor to track:

• MySQL Memory Usage counters
• Disk Queue Length
• Processor Time
• Available MBytes

When MySQL underutilizes available RAM (like using only 250MB out of 8GB), it forces excessive disk I/O operations. The key solution lies in configuring MySQL's buffer pool - its primary memory area for caching data and indexes.

Edit your my.ini or my.cnf configuration file (typically located in C:\ProgramData\MySQL\MySQL Server X.Y\) and adjust these parameters:

[mysqld]
# Set buffer pool to 50-70% of available RAM (4-5.6GB in 8GB system)
innodb_buffer_pool_size = 4G

# Buffer pool instances for better concurrency (set to 4-8)
innodb_buffer_pool_instances = 4 

# Log file size (25% of buffer pool size)
innodb_log_file_size = 1G
innodb_log_files_in_group = 2

# Additional memory optimizations
innodb_flush_method = O_DIRECT
innodb_flush_neighbors = 0
innodb_read_io_threads = 8
innodb_write_io_threads = 4
query_cache_size = 0

After restarting MySQL, monitor effectiveness with:

SHOW ENGINE INNODB STATUS\G
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

Look for these key metrics:
- Innodb_buffer_pool_read_requests: Logical reads
- Innodb_buffer_pool_reads: Physical disk reads
- Buffer pool hit rate should be > 99% (1 - (reads/requests))

For Windows Server 2008/R2:

innodb_use_native_aio = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
table_open_cache = 4000
table_definition_cache = 2000

Create a scheduled task to log memory usage:

mysqladmin -uroot -p ext -i60 | grep -E "Innodb_buffer_pool_reads|Innodb_buffer_pool_read_requests" >> mysql_memory.log

For read-heavy applications:

innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 75

For write-intensive systems:

innodb_change_buffering = all
innodb_doublewrite = 1
innodb_thread_concurrency = 0