UFRAGG

Create a custom module to enable and optimize innodb usage.
MySQL on Debian will first read the default MySQL config file at /etc/mysql/my.cnf
and then overwrite and add config options from /etc/mysql/conf.d/*
select code
1	vi /etc/mysql/conf.d/custom.cnf
MySQL config with comments derived from google, mysql.com, reading, prior MySQL experience, and educated guessing
This config seems to work well for a server with 2GB ram and low traffic.
Adjust as needed.
select code
 
[mysqld]
 
#
# Skip options
skip_bdb
#skip_innodb
skip_locking
#skip_networking
 
#
# Server ID must be unique to allow for replication
server_id = 1
 
#
# User name to run as
user = mysql
 
#
# Port and the socket
port = 3306
# socket = /tmp/mysqld.sock
socket          = /var/run/mysqld/mysqld.sock
pid-file        = /var/run/mysqld/mysqld.pid
 
#
# Bind to a specific address, otherwise listen to all addresses
bind_address = 127.0.0.1 
 
#
# Maximum number of connections
#
max_connections = 100
 
#
# Connection backlog, raise this if we run out of
# connections (128 is the linux default)
back_log = 128
 
#
# Maximum number of connection error per host
max_connect_errors = 100
 
#
# Connection timeout
connect_timeout = 4
#
 
#
# Timeout for inactive connections
wait_timeout = 30
 
#
# Maximum packet length, no single MySQL statement can be longer than this
max_allowed_packet = 16M
 
#
# Network buffer length (I think this is the linux default)
net_buffer_length = 8K
 
#
# Set the default character set to utf8
default_character_set = utf8
 
#
# Set the server character set
character_set_server = utf8
 
#
# Set the default collation to utf8_general_ci
default_collation = utf8_general_ci
 
#
# Set the names to utf8 when a client connects
init_connect = 'SET NAMES utf8; SET sql_mode = STRICT_TRANS_TABLES'
 
 
skip-external-locking
 
#
# Server directories
#basedir = /usr/local/mysql
basedir  = /usr
#datadir = /var/lib/mysql
datadir  = /home/mysql/data
tmpdir   = /tmp
 
#
# Language file location
#language = /usr/local/mysql/share/english 
language        = /usr/share/mysql/english
 
# Error log file (need dash in variable name)
log-error = /var/log/mysql/mysqld.err
  
#
# Log slow queries, time threshold set by 'long_query_time',
log_slow_queries = /var/log/mysql/slow-queries.log
# log_output = FILE # 5.1 only
long_query_time = 10
 
#
# Log queries which don't use indices in the slow query log
# log_long_format
 
# Enable this to get a log of all the statements coming from a client,
# this should be used for debugging only as it generates a lot of stuff
# very quickly
#log = /var/log/mysql/queries.log
#
 
#
# Binary log and replication log file names prefix
log_bin = /home/mysql/binary-log/server1-bin
# relay_log = /home/poplar/mysql/binary-logs/server1-relay-bin
expire_logs_days        = 10
max_binlog_size   = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
 
#
# Binary log format, see:
# http://dev.mysql.com/doc/refman/5.1/en/replication-row-based.html
# http://dev.mysql.com/doc/refman/5.1/en/replication-sbr-rbr.html
#binlog_format = row # 5.1 only
 
#
# Binary log cache size
binlog_cache_size = 1M
 
#
# Skip automatic replication start up, replication will have to be
# started manually with 'start slave' once the server is started
skip_slave_start
 
#
# Make the slave read-only
#read_only
 
#
# Select which replication errors to skip, see
# http://dev.mysql.com/doc/refman/5.1/en/replication-options.html
#slave_skip_errors = 1062
 
 
#
# Select which databases/tables to ignore during replication, see
# http://dev.mysql.com/doc/refman/5.1/en/replication-options.html
#replicate_ignore_db =
#replicate_ignore_table =
#replicate_do_db =
#replicate_do_table =
 
#
# Number of open tables at any one time
table_cache = 256
 
 
#
# Join buffer size for index-less joins
join_buffer_size = 8M
 
 
#
# Maximum size for in memory temporary tables, anything
# larger gets spun out to disc
tmp_table_size = 128M
 
#
# Sort buffer size for ORDER BY and GROUP BY queries, data
# gets spun out to disc if it does not fit
sort_buffer_size = 8M
 
#
# Thread cache size, concurrency and stack
thread_cache_size = 64
thread_concurrency = 8
thread_stack = 192K
 
 
#
# Query cache
#
query_cache_size = 64M
query_cache_type = 1
query_cache_limit = 1M
 
 
#
# Default table storage engine when creating new tables
# (comment out when running mysql_install_db)
default_storage_engine = InnoDB
 
 
#
# Default transaction isolation level, levels available are:
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE
# see: http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html
transaction_isolation = REPEATABLE-READ
 
 
#
# MyISAM options, see:
#
# http://dev.mysql.com/doc/refman/5.1/en/myisam-start.html
key_buffer_size = 128M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 128M
bulk_insert_buffer_size = 64M
myisam_max_sort_file_size = 512M
myisam_repair_threads = 2
#myisam_recover_options = DEFAULT
 
 
#
# InnoDB options, see:
 
# http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html
# Data directory, and data file
innodb_data_home_dir = /home/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
 
#
# Use one file per table
innodb_file_per_table
 
#
# Buffer pool size
innodb_buffer_pool_size = 384M # 2GB RAM
#innodb_buffer_pool_size = 2G # 4GB RAM
#innodb_buffer_pool_size = 10G # 16GB RAM
innodb_additional_mem_pool_size = 32M
 
 
#
# Transaction log location and sizes
innodb_log_group_home_dir = /home/mysql/innodb-log
innodb_log_files_in_group = 4
innodb_log_file_size = 64M # 2GB RAM
#innodb_log_file_size = 128M # 4GB RAM
#innodb_log_file_size = 512M # 16GB RAM
innodb_log_buffer_size = 8M
 
 
#
# Percentage of unwritten dirty pages not to exceed
innodb_max_dirty_pages_pct = 80
 
#
# Transaction commit policy
innodb_flush_log_at_trx_commit = 1
 
#
# Timeout to wait for a lock before rolling back a transaction
innodb_lock_wait_timeout = 50
 
#
# Flush method
innodb_flush_method = O_DIRECT
 
#
# Number of concurrent threads to run
innodb_thread_concurrency = 32
 
 
#
# Autoinc lock mode ('consecutive' lock mode), see:
# http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html
#innodb_autoinc_lock_mode = 1 # 5.1 only
 
#
# Prevent extra locking, we can only use this if we have row
# level replication, see 'binlog_format'
#innodb_locks_unsafe_for_binlog # 5.1 only
 
#
# Enable fast innodb shutdown (skip full purge and insert buffer merge)
innodb_fast_shutdown = 1
#innodb_fast_shutdown = 0
 
#
# Dont delay insert, update and delete operations when purge
# operations are lagging
innodb_max_purge_lag = 0
#innodb_max_purge_lag = 1
 
#
# Force recovery mode, do not mess with unless you really know
# what you are doing, see:
# http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
#innodb_force_recovery = 4