RSS

Increasing Ulimit for Partitioning tables

Data Ofen grow larger while we keep adding data to it and Retrieving data would slow down eventually and takes longer time to produce results, this is sometimes unacceptable for clients to wait that longer,  One great solution to Retrieve data faster without Archiving or purging any data is Table Partitioning,  Table will be broken into small chunks according to date range / no.of records (like 1-100, 101-200, 201-300 and so on… )  and a lot of files will be created due to this partitioning,  

MySql User  on the HOST has a limit of only 4096 files to store by default. this has to be increased  to a certain number, otherwise  MySql database will be shutdown or corrupt due to insufficient files on the host.

To overcome this situation, you need to increase the file limit on the host.  Please add the below code in bash file and run it from your host as root, this will increase to the no.of files you need, but please make sure not to give the number above OS limit . the below example will allow 30,000 files for mysql user while creating any table partitions.

**********************************************

#! /bin/csh

cat >/etc/security/limits.d/mysql.conf <<EOF
mysql soft nofile 30000
mysql hard nofile 30000
EOF

**********************************************

To test the above scritp. login as user mysql and run “ulimit -n” command.

before running the script
> ulimit -n
4096

After running the above script
> ulimit -n
30000

 

 
Leave a comment

Posted by on January 27, 2012 in Database Administration, linux, MySql

 

Tags: , , ,

New Config File Creator from Percona

Percona Tools Website   is a beautiful tool to build a config file according to your Server Requirements, which is  a real benefit for any Companies who are using MySql having any Server performance issue, This tool may not exactly suit all your required settings but it surely helps you to build a better config file, you can modify it after creating this file.

it is worth a try…

 
Leave a comment

Posted by on January 23, 2012 in Database Administration, MySql, percona

 

Extracting 1 week of Data from a Big log File

It is very difficult to read one week of data from a Big Log file (i.e 2gb size log file).  where it takes 100% cpu and blocks all the processess, and take s more time to get a small piece of data using less command in unix.   to resolve this, search for the first line number of the 1st day of the Week and last line no of the last day of the Week and use SED command to extract from starting line position to end line position, copy that data to a new file.

First Try to Find the Line No’s of the Starting Date of 1 week (lets say 9 Apr 2010 – 16 Apr 2010)

grep -n ‘Time: 100409’ /var/log/mysqld/myserver_slow_queries.log | more

Copy or write down the Line no. let’s say 2399098

grep -n ‘Time: 100416’ /var/log/mysqld/myserver_slow_queries.log | more

Copy or write down the Last Line no. 2483712

now run the SED Unix Command to grab the data from start line to end line into a new log file.

sed -n 2399098,2483712p myserver_slow_queries.log > myserver_slow_queries.log.week

Done. One Week of Data from a Big Log file will be copied to a new file.

 
Leave a comment

Posted by on August 23, 2010 in Database Administration, linux, MySql

 

Tags: , , , ,

MySql Flush Hosts

I got a below connection error recently on our server,  but resolved after certain attempts. FLUSH Hosts is the command where it Flush out the connection errors and allow MySql to connect again.

[Tue Jun 01 16:07:10 2010] [error] [client 100.100.11.12] 

DBI connect('host:database:port;mysql_connect_timeout=10','web',...)

failed: Host 'mysite.net' is blocked because of many connection errors;

unblock with 'mysqladmin flush-hosts' at [Tue Jun 01 16:07:10 2010]

[error] [client 100.100.21.211]

 

Solution:

FLUSH HOSTS will Free all the connection errors from buffer.

 
Leave a comment

Posted by on August 16, 2010 in Database Administration, MySql

 

Tags: ,

Linux Screen Remote Login Issue

If you ever get the below error while you try to activate screen (Unix Screen Utility) as a different user rather than your login .  (for ex: i logged in as john and sudo admin and type screen at prompt).

Cannot open your terminal ‘/dev/pts/7’ – please check

1) Exit out of the user First.

2) cd /dev/pts/7

3) chmod +0666 /dev/pts/7

4) then sudo su user

5) type Screen. then it’ll work.

Please let me know if this doesn’t work..

 
Leave a comment

Posted by on August 16, 2010 in Database Administration, linux, MySql

 

Tags: , , , ,

MASTER Master Replication Tutorial

Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.16.4
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: MASTERMYSQL01-bin.000009
Read_Master_Log_Pos: 4

Relay_Log_File: MASTERMYSQL02-relay-bin.000015

Relay_Log_Pos: 3630
Relay_Master_Log_File: MASTERMYSQL01-bin.000009
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4
Relay_Log_Space: 3630
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 1519187

1 row in set (0.00 sec)

Above highlighted rows must be indicate related log files and  Slave_IO_Running and   Slave_SQL_Running: must be to YES.

Step 6:

On master 1:

mysql> show master status;
+————————+———-+————–+——————+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+————————+———-+————–+——————+
|MysqlMYSQL01-bin.000008 |      410 | adam         |                  |
+————————+———-+————–+——————+
1 row in set (0.00 sec)

The above scenario is for master-slave, now we will create a slave master scenario for the same systems and it will work as master master.

Step 7:

On Master2/Slave 1, edit my.cnf and master entries into it:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=2

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

log-bin                     #information for becoming master added
binlog-do-db=adam

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 8:

Create a replication slave account on master2 for master1:

mysql> grant replication slave on *.* to ‘replication’@192.168.16.4 identified by ‘slave2’;

Step 9:

Edit my.cnf on master1 for information of its master.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

log-bin
binlog-do-db=adam
binlog-ignore-db=mysql
binlog-ignore-db=test

server-id=1
#information for becoming slave.
master-host = 192.168.16.5
master-user = replication
master-password = slave2
master-port = 3306

[mysql.server]user=mysqlbasedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Step 10:

Restart both mysql master1 and master2.

On mysql master1:

mysql> start slave;

On mysql master2:

mysql > show master status;

On mysql master 1:

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.16.5
Master_User: replica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: Mysql1MYSQL02-bin.000008
Read_Master_Log_Pos: 410
Relay_Log_File: Mysql1MYSQL01-relay-bin.000008
Relay_Log_Pos: 445
Relay_Master_Log_File: Mysql1MYSQL02-bin.000008
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 410
Relay_Log_Space: 445
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 103799
1 row in set (0.00 sec)

ERROR:
No query specified

Check for the hightlighted rows, make sure its running. Now you can create tables in the database and you will see changes in slave. Enjoy!!

 
Leave a comment

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

 

Tags: , , ,

Best INNODB Configuration (my.cnf) file

[client]

port = 3306

socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]

socket = /var/run/mysqld/mysqld.sock

nice = 0

[mysqld]

user = mysql

pid-file = /var/run/mysqld/mysqld.pid

socket = /var/run/mysqld/mysqld.sock

port = 3306

basedir = /usr

datadir = /var/lib/mysql

tmpdir = /tmp

language = /usr/share/mysql/english

skip-external-locking

bind-address = 192.168.2.200

key_buffer = 16M

max_allowed_packet = 16000M

thread_stack = 128K

thread_cache_size = 8

#max_connections = 100

#table_cache = 64

#thread_concurrency = 10

query_cache_limit = 1M

query_cache_size = 1024M

innodb_buffer_pool_size=2048M

innodb_additional_mem_pool_size=400M

innodb_log_file_size=25M

innodb_log_buffer_size=80M

sort_buffer=512M

thread_concurrency=32

record_buffer=128M

query_cache_type=2

tmp_table_size=1G

#log = /var/log/mysql/mysql.log

#log_slow_queries = /var/log/mysql/mysql-slow.log

#long_query_time = 2

#log-queries-not-using-indexes

#server-id = 1

#log_bin = /var/log/mysql/mysql-bin.log

expire_logs_days = 10

max_binlog_size = 100M

#binlog_do_db = include_database_name

#binlog_ignore_db = include_database_name

#skip-innodb

# chroot = /var/lib/mysql/

[mysqldump]

quick

quote-names

max_allowed_packet = 16M

[mysql]

#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]

key_buffer = 16M

* Turn off HyperThreading -- it tends to slow things down.

* key_buffer = 16M -- very small if you use any MyISAM. In which case, set it to 3000M. (It must not be bigger than 4G.) Since you are not using any MyISAM, leave it at 16M.

* query_cache_size = 1024M -- The bigger it is, the slower it runs. Suggest 200M.

* innodb_buffer_pool_size=2048M -- You have 16GB of RAM. Is it 'all' available to MySQL? If so, and you are using InnoDB exclusively, suggest 13G.

* If mixture of MyISAM and Innodb, adjust proportionally.

* InnoDB database with 1,7 GiB of data? Wasted your money on so much RAM.

* #log_slow_queries = /var/log/mysql/mysql-slow.log

#long_query_time = 2

Do you have any control over the schema or queries? Perhaps not if you are using a canned product. If you did have control, I would recommend turning on the slowlog and using it to help tune things.

* #max_connections = 100 -- I don't know what the default is; keep an eye on max_used_connections. If you run out of connections set max_connections appropriately.

* #table_cache = 64 -- You can probably set this to something, then watch open_tables to see whether it needs to be bigger.

Executive summary: Your current settings are close to the best available.

This is one of the Best Configuration file from MYSQL

ADMINISTRATION BIBLE BOOK.


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

[client]

port = 3306

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

[mysqld]

#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_connections=200

max_connect_errors = 400

wait_timeout=7200

connect_timeout=10

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

362

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_data_file_path=ibdata1:200M:autoextend

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

innodb_file_per_table

# binary logging is required for replication

log-bin=mysql-bin

max_binlog_size = 1024M

server-id = 4

slave-skip-errors = 1062

expire-logs-days = 7

[mysqldump]

quick

max_allowed_packet = 16M

[mysql.server]

user=mysql

group=mysql

basedir=/var/lib

[mysqld_safe]

nice = -5

open_files_limit = 8192

log-error=/var/log/mysql/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

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 sampleconfiguration file from a production system:# The following options will be passed to all MySQL clients[client]port = 3306socket = /var/lib/mysql/mysql.sock[mysqld]#this will prevent mysql from startingport = 3306socket = /var/lib/mysql/mysql.sockdatadir =/var/lib/mysqllog_slow_queries = /var/log/mysql/mysqld_slow-queries.loglong_query_time = 4max_connections=200max_connect_errors = 400wait_timeout=7200connect_timeout=10key_buffer = 512Mtmp_table_size = 32Mmax_heap_table_size = 32Mmax_allowed_packet = 32Mtable_cache = 1800join_buffer_size = 8Msort_buffer_size = 16Mread_buffer_size = 8Mread_rnd_buffer_size = 524288myisam_sort_buffer_size = 256Mthread_cache_size = 384bulk_insert_buffer_size = 8Mquery_cache_limit = 4Mquery_cache_size = 128Mquery_cache_type = 1362MySQL Server Tuning 10query_prealloc_size = 65536query_alloc_block_size = 131072# Try number of CPU’s*2 for thread_concurrencythread_concurrency = 4#innodb configurationinnodb_data_home_dir = /mysql/datainnodb_data_file_path=ibdata1:200M:autoextendinnodb_log_group_home_dir = /mysql/data/datainnodb_log_arch_dir = /mysql/data/datainnodb_buffer_pool_size = 24576Minnodb_additional_mem_pool_size = 32Minnodb_log_file_size = 1024Minnodb_log_files_in_group = 2innodb_log_buffer_size = 16Minnodb_flush_log_at_trx_commit = 1innodb_lock_wait_timeout = 120sync-binlog = 1innodb_support_xa = 0innodb_thread_concurrency = 128innodb_file_per_table# binary logging is required for replicationlog-bin=mysql-binmax_binlog_size = 1024Mserver-id = 4slave-skip-errors = 1062expire-logs-days = 7[mysqldump]quickmax_allowed_packet = 16M[mysql.server]user=mysqlgroup=mysqlbasedir=/var/lib[mysqld_safe]nice = -5open_files_limit = 8192log-error=/var/log/mysql/mysqld.logpid-file=/var/run/mysqld/mysqld.pid

 
4 Comments

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

 

Tags: , , , ,

 
%d bloggers like this: