Ramblings on MYSQL Administration

06 Nov

MY.CNF Configuration file

The configuration file supports options for multiple programs. Each program has its own directive, which is a keyword in square brackets, such as [mysqld] or [client]. Here is a sample configuration file from a production system:

# The following options will be passed to all MySQL clients


port = 3306

socket = /var/lib/mysql/mysql.sock


#this will prevent mysql from starting

port = 3306

socket = /var/lib/mysql/mysql.sock

datadir =/var/lib/mysql

log_slow_queries = /var/log/mysql/mysqld_slow-queries.log

long_query_time = 4


max_connect_errors = 400



key_buffer = 512M

tmp_table_size = 32M

max_heap_table_size = 32M

max_allowed_packet = 32M

table_cache = 1800

join_buffer_size = 8M

sort_buffer_size = 16M

read_buffer_size = 8M

read_rnd_buffer_size = 524288

myisam_sort_buffer_size = 256M

thread_cache_size = 384

bulk_insert_buffer_size = 8M

query_cache_limit = 4M

query_cache_size = 128M

query_cache_type = 1


MySQL Server Tuning 10

query_prealloc_size = 65536

query_alloc_block_size = 131072

# Try number of CPU’s*2 for thread_concurrency

thread_concurrency = 4

#innodb configuration

innodb_data_home_dir = /mysql/data


innodb_log_group_home_dir = /mysql/data/data

innodb_log_arch_dir = /mysql/data/data

innodb_buffer_pool_size = 24576M

innodb_additional_mem_pool_size = 32M

innodb_log_file_size = 1024M

innodb_log_files_in_group = 2

innodb_log_buffer_size = 16M

innodb_flush_log_at_trx_commit = 1

innodb_lock_wait_timeout = 120

sync-binlog = 1

innodb_support_xa = 0

innodb_thread_concurrency = 128


# binary logging is required for replication


max_binlog_size = 1024M

server-id = 4

slave-skip-errors = 1062

expire-logs-days = 7



max_allowed_packet = 16M






nice = -5

open_files_limit = 8192



(Redundant Array of Independent Disks)

Raid 0 (zero): Only One Copy of Data spread out into Multiple Disks. if One copy Lost all the Data is Lost.

Raid 1 : Two Disks Mirrored if one fails Other will Pickup.

Raid 5: Combines Three of more Disks.  Storage Capacity is Reduced by One Disk.

Raid 10: Combination of Raid 1 and 0 Storage Capacity – 50% Mirrored.


LVM: Logical Volume Manager: Which will Separate Log Files on a Different Server.

File System

EXT 2: Good Perfoermance & Slower Crash Recovery.

EXT 3:  Good Performance over EXT2 Filer System.

IBM JFS:  Fast Recovery After a file System Failure.

Tuning MYSQL Server

Show Innodb Status: Which will show all the Status Information of Tables, Queries, Locks, Memory Usage, Memory Dumps, Cache, Flushed Transactions, Thread information Etc.

show innodb status;
Per second averages calculated from the last 15 seconds
OS WAIT ARRAY INFO: reservation count 107862, signal count 107846
Mutex spin waits 0, rounds 9648346, OS waits 26394
RW-shared spins 140432, OS waits 70114; RW-excl spins 63, OS waits 54
Trx id counter 0 966751765
Purge done for trx’s n:o < 0 966751662 undo n:o < 0 0
History list length 13
—TRANSACTION 0 0, not started, process no 26454, OS thread id 2923674480
MySQL thread id 466090, query id 8924458 HTN
show innodb status
—TRANSACTION 0 966751278, not started, process no 26454, OS thread id 2968963952
MySQL thread id 466080, query id 8921005 HTN
—TRANSACTION 0 0, not started, process no 26454, OS thread id 2946497392
MySQL thread id 466077, query id 8922846 HTN
—TRANSACTION 0 966751530, not started, process no 26454, OS thread id 3021794160
MySQL thread id 465759, query id 8922979 HTN
—TRANSACTION 0 0, not started, process no 26454, OS thread id 3076823920
MySQL thread id 466068, query id 8920769 HTN
—TRANSACTION 0 0, not started, process no 26454, OS thread id 2943998832
MySQL thread id 466051, query id 8920551 HTN
—TRANSACTION 0 0, not started, process no 26454, OS thread id 2938555248
MySQL thread id 466021, query id 8920902 HTN
—TRANSACTION 0 0, not started, process no 26454, OS thread id 2938956656
MySQL thread id 465966, query id 8920351 HTN
—TRANSACTION 0 0, not started, process no 26454, OS thread id 3077024624
MySQL thread id 465757, query id 8921477 HTN
—TRANSACTION 0 966751014, not started, process no 26454, OS thread id 2969365360
MySQL thread id 465566, query id 8921004 HTN
—TRANSACTION 0 0, not started, process no 26454, OS thread id 2900405104
MySQL thread id 465514, query id 8902812 HTN
—TRANSACTION 0 966751013, not started, process no 26454, OS thread id 2944400240
MySQL thread id 465472, query id 8919475 HTN
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0
Pending flushes (fsync) log: 0; buffer pool: 0
86187 OS file reads, 11907 OS file writes, 10888 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 1.47 writes/s, 0.47 fsyncs/s
Ibuf: size 1, free list len 60, seg size 62,
246 inserts, 246 merged recs, 39 merges
Hash table size 34679, node heap has 4 buffer(s)
6.53 hash searches/s, 6.53 non-hash searches/s
Log sequence number 5 2711071627
Log flushed up to   5 2711071627
Last checkpoint at  5 2711071627
0 pending log writes, 0 pending chkp writes
8299 log i/o’s done, 0.20 log i/o’s/second
Total memory allocated 21219654; in additional pool allocated 1048576
Dictionary memory allocated 2740496
Buffer pool size   512
Free buffers       1
Database pages     507
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1127608, created 33, written 2585
0.00 reads/s, 0.00 creates/s, 1.20 writes/s
Buffer pool hit rate 1000 / 1000
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 26454, id 2987748208, state: sleeping
Number of rows inserted 1553, updated 618, deleted 299, read 293174006
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 150.26 reads/s

SHOW VARIABLES: Displays all variables and the usage    ex: Show Global Variables Like ‘%tmp%/’

By default, SHOW STATUS will show SESSION variables. This may or may not be your intention, so to avoid confusion, always specify GLOBAL or SESSION as a keyword.



| Variable_name | Value |


| Created_tmp_disk_tables | 2744623 |

| Created_tmp_files | 147344 |

| Created_tmp_tables | 5003238 |


The SHOW VARIABLES can be used to determine the values of system variables. This is a very useful command when tuning a server. As with the SHOW STATUS command, it can use the LIKE modifier, and you should always specify GLOBAL or SESSION to avoid possible confusion.

If you wanted to see the global settings for temporary tables:



| Variable_name | Value |


| max_tmp_tables | 32 |

| tmp_table_size | 33554432 |






INNODB Performance Tuning  Parameters

INNODB_BUFFER_POOL_SIZE : 70-80% memory can be allocated to Buffer Pool size, Set it to 12gb on a 16gb box.

INNODB_LOG_FILE_SIZE:  This Depends on your Recovery Speed but 256mb Seems to be a good balance between Reasonable Recovery and Good Time Performance.

INNODB_LOG_BUFFER_SIZE: This can be Set to the

INNODB_FLUSH_LOG_AT_TRX_COMMAND: Works good before Server Crashes.




Transaction Isolation: Read Committed: This Option has some performance Benefits especially in locking in 5.0 and Even more to come with MySQL 5.1 and Row Level Application

Application Tuning for INNODB:

ERROR log:  Sql Daemon Start Stop, Critical Errors will be Logged in this Error Log File

Binary Log: Update, Delete, Insert,  Statements will be logged to Binary Log file.

General Query Log:  All Select Queries, general data retrieval queries will be recorded.

Slow Query Log:  Queries which are running slow and can be logged in to the file.

To activate the query log, simply place:
log-slow-queries = [slow_query_log_filename]
in your configuration file (my.cnf or my.ini),
slow_query_log_filename being the optional filename for your log file. If you don’t supply a filename, the default name will be used, which is the name of the host machine, with -slow.log being appended.

The slow query log logs all queries that take longer than long_query_time, which is usually 10 seconds by default (more than long enough for a self-respecting query to complete). You can alter the long_query_time in the configuration file. The following example sets the time to 5 seconds:
set-variable = long_query_time = 5
The slow query log can also optionally log all queries that don’t use an index by placing the following in the configuration file:


s of MySQL 5.1.6, the destination can be a file or a table, or both
with the --log-slow-queries[=file_name] option to enable the slow query log, and optionally use --log-output to specify the log destination (as described in Section 5.2.1, “Selecting General Query and Slow Query Log Output Destinations”).

Tee:  Turns on MYSQL Logging  to a specified file. ex: Tee / Filename,
NoTee:  Will stop logging mysql.

Memory Storage Engine Configuration

max_heap_table_size: The Maximum size of memory tables is limited by the system variable, it has a default value of 16 mega bytes.

Init_File: This can be used to specify a file to use to populate a memory table  when mysqld starts, for ex: the file may contain LOAD DATA INFILE Command.

Maria Storage Engine is a Crash Safe Version of MYISAM.

Overall MySQL Server Options


Overall MySQL Server Options

Option Name Global or

memlock Global
Locks the mysqld daemon into server memory. While this can improve performance if the server runs out of RAM, the mysqld daemon will crash.

sort_buffer_size = buffer_size
Session Determines the amount of system memory allocated for SQL sorts. If this size is exceeded, the server will use hard drive space for sorting data.

thread_cache_size = num_of_threads
Global mysqld creates a cache of unused connection threads rather than destroying threads and creating new ones as need.

thread_concurrency = N Global
Only use on Solaris systems. This should typically be set to twice the number of CPUs. It should be tested carefully before using in production.

tmp_table_size Global
Both this and the max_heap_table_size setting are used to determine the maximum size allowed of an in-memory temporary table before it is converted to a MyISAM table. The smallest value for these two settings is the one utilized.

MOCA (MYSQL Optimal Configuration Architechture)

1)  /opt/Mysql/5.1.33           -------------- Software Installed at this Location
2) db01/mysql/mysql01/data --------  Data Directory
Db01/mysql/mysql01/binlogs    ------------- binary logs directory
Db01/mysql/mysql01/admin       ------------ main administration


Db01/mysql/mysql01/Backups   ————  Backups
Db01/mysql/mysql01/Trans        ————  Transaction Data Files
Db01/mysql/mysql01/TransLogs  ———-   Trans Log Files.

Backup Strategies and Backup Procedures.

Logical Backup: Taking Backup Online While Database is Running.

Physical Backup: Taking Backup of Database Files or Disk Partitions: Occupies More Space.

Full Backup: Taking Full Backup of Database.

Incremental Backup: Taking Backups of the Database Since the last Backup.  You may take first full Backup and take Incremental Backups to Save Time. Once a Week full backup might be necessary.

Consistent Backup: Taking Backup at an Exact Moment of Time, A Backup Process may take time to Complete
HOT Backup: Taking Backup while Database is Running.
Cold Backup: Taking Backup While Database is Offline.
Point-in-Time-Restore: Restoring Backup for Certain Time and Date.

REPLICATION – – Configuring Slave Backup Server.

Restore Database and Execute a CHANGE MASTER Command to configure the Slave.

– Master to Slave.
Grant Replication Slave on *.* to ‘Slave’ @ ‘Slave-host’ Identified by ‘Password’ ;

2) Edit My.cnf   Configuration file and add the below line

Server-Id  =  1
Log – bin = Slave_log

Show Master Status;


1) Horizontal Partitioning : Partition tables horizontally as if 2 tables (table1 and table2) both are separated by a line. I) Archiving Old Data is a Common task in Horizontal Partitioning. Merge tables will be used to Partition Data Horizontally.

2) Vertical Partitioning: Partitioning table vertically using de-Normalization: A Customer Table will be devided into a two tables splitting customer Data Columns.

Partition Algorithm in MYSQL

1) Range: Selects a Partition by Determining if the partition key is inside a Range of Values. An Example of Partition for all Rows based on Customer in Values less than 500,000 Can go into one partition and values greater than or Equal to go into another Partition.



Partition by Range (To_Days (Entry_Date),


Partition PO Values Less than (To_Days (‘2009-01-01)),
Partition PO Values Less than (To_Days (‘2009-02-01)),
Partition PO Values Less than (To_Days (‘2009-03-01)),
Partition PO Values Less than (To_Days (‘2009-04-01)),
Partition PO Values Less than (To_Days (‘2009-05-01)),
Partition PO Values Less than MAXVALUE.

SHOW PLUGINS (all are active except ndbcluster);

ndbcluster – DISABLED – needs to be downloaded and Installed in order to work.

INNODB Row Level Locking

Advantages of row-level locking:

  • Fewer lock conflicts when different sessions access different rows

  • Fewer changes for rollbacks

  • Possible to lock a single row for a long time

Disadvantages of row-level locking:

  • Requires more memory than page-level or table-level locks

  • Slower than page-level or table-level locks when used on a large part of the table because you must acquire many more locks

  • Slower than other locks if you often do GROUP BY operations on a large part of the data or if you must scan the entire table frequently

Leave a comment

Posted by on November 6, 2009 in Database Administration, MySql


Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: