Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 2 down vote accepted

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
share|improve this answer
    
Not sure how to schedule this on windows machines, so I'll let someone else provide that answer! –  Derek Downey Aug 5 '11 at 14:27
    
+1 for expanding on the RAM issues and taking advantage of that RAM with setting temp table limits. –  RolandoMySQLDBA Aug 5 '11 at 16:20
add comment

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

share|improve this answer
1  
+1 for straight up common sense. What good is a Formula One Race Car (MySQL 5.5) if it only has a pint of gasoline (1GB of RAM) ?? –  RolandoMySQLDBA Aug 5 '11 at 16:18
    
@Rolando I laughed –  Derek Downey Aug 5 '11 at 16:40
    
@Rolando: Lol. Upgraded RAM to 3GB. Does it make any sense now? –  RPK Aug 6 '11 at 16:53
add comment

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;
share|improve this answer
1  
I don't think 'NO_WRITE_TO_BINLOG' is needed when running flush logs. According to the manual: "FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK (with or without a table list) are not written to the binary log in any case because they would cause problems if replicated to a slave." –  Derek Downey Aug 5 '11 at 16:42
    
@DTest Good observation. I was just erring on the side of caution. –  RolandoMySQLDBA Aug 5 '11 at 16:50
    
Nothing wrong with that...and + 1 for the event scheduler. Can you believe I've never explored this aspect of MySQL? /sigh –  Derek Downey Aug 5 '11 at 17:04
    
I am not a big fan of the event scheduler because it can be a stressful updating InnoDB tables in it. Case in point : dba.stackexchange.com/questions/3910/mysql-event-does-not-run/… –  RolandoMySQLDBA Aug 5 '11 at 17:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.