Please review the My.INI settings of my PC. File Attached

Question

Below is the my.ini file of one of my PC running MySQL 5.5 with 1 GB RAM. It is not using concurrent connections and we are using a stand-alone PC for a single application and single user.

# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------

# CLIENT SECTION

[client]

port=3306

[mysql]

default-character-set=latin1

# SERVER SECTION
[mysqld]
log-bin=E:/CRITICAL_MYSQL_LOGGING/AUTOMATED_BINARY_LOGS/MySqlBinLog

# The TCP/IP Port the MySQL Server will listen on
port=3306

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

#Path to the database root
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=latin1

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# The maximum amount of concurrent sessions the MySQL server will
# allow. 
max_connections=02

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again. 
query_cache_size=15M

# The number of open tables for all threads. 
table_cache=256

# Maximum size for internal (in-memory) temporary tables.
tmp_table_size=9M

# How many threads we should keep in a cache for reuse. When a client
# disconnects
thread_cache_size=8

#*** INNODB Specific options ***

innodb_additional_mem_pool_size=20M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=500M

innodb_log_file_size=10M

innodb_thread_concurrency=8

log-queries-not-using-indexes

log-warnings

log=E:/CRITICAL_MYSQL_LOGGING/QUERY_LOGS/QueryLog

log-error=E:/CRITICAL_MYSQL_LOGGING/ERROR_LOGS/ErrorLog

log-slow-queries=E:/CRITICAL_MYSQL_LOGGING/SLOW_QUERY_LOGS/SlowQueryLog

log-bin-index=E:/CRITICAL_MYSQL_LOGGING/BinLogIndexFile

long_query_time=2

I have updated the original my.ini file. Please suggest better values for a 1 GB single user PC.

I also want to know how to flush logs and when.

Answer 1

MrDenny is right about your RAM. Windows by itself eats up a good chunk of your RAM without anything else going on. And then to send half of the total RAM to the innodb buffer pool is going to cause performance issues off the bat.

Other than that, I will offer up a caveat with your setting of 'tmp_table_size'. From the manual

The maximum size of internal in-memory temporary tables. (The actual limit is determined as the minimum of tmp_table_size and max_heap_table_size.)

If you ever want to increase tmp_table_size above 16M (the default of max_heap_table_size), you'll need to increase that variable as well.

As for Flushing the binary logs, you can do this by issuing the command:

FLUSH BINARY LOGS

Answer 2

You need more RAM. Windows can barely survive with a Gig of RAM. I would put at least 4 Gigs in the machine.

Answer 3

You could schedule it in the built-in Event Scheduler.

First create a stored procedure to do the flush of the binary logs:

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`sp_FlushBinaryLogs` $$
CREATE PROCEDURE `test`.`sp_FlushBinaryLogs` ()
BEGIN
    FLUSH NO_WRITE_TO_BINLOG BINARY LOGS;
END $$
DELIMITER ;

Now call sp_FlushBinaryLogs in an event that goes off every hour (or you can set it to whatever schedule you want):

DELIMITER $$
DROP EVENT IF EXISTS `test`.`ev_FlushBinaryLogs` $$
CREATE EVENT `test`.`ev_FlushBinaryLogs`
    ON SCHEDULE
      EVERY 1 HOUR
      STARTS (NOW() + INTERVAL 1 HOUR)
    DO
BEGIN
    CALL sp_FlushBinaryLogs();
END $$
DELIMITER ;

Don't forget to activate the event scheduler. You can do two things

[mysqld]
event_scheduler=1

Without restarting mysql, you can turn on the event scheduler with

SET GLOBAL event_scheduler = 1;