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.

I really need some mysql expertize. I am a newbi to mysql and I am seeing some server crash of my db in the past 1 week.

I am using mysql 5.1.36 on Ubuntu. This is a dedicated mysql server with Dual core and 4GB memory and 40GB SSD.

The log errors are:

120413 23:57:15 [Note] Plugin 'FEDERATED' is disabled.
120413 23:57:15 [Warning] option 'innodb-autoextend-increment': unsigned value 2000 adjusted to 1000
120413 23:57:15  InnoDB: Initializing buffer pool, size = 2.9G
120413 23:57:15  InnoDB: Completed initialization of buffer pool
120413 23:57:16  InnoDB: Started; log sequence number 0 44234
120413 23:57:16 [Note] Event Scheduler: Loaded 0 events
120413 23:57:16 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.58-1ubuntu1-log'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu)
120414  0:00:25 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=e2-relay-bin' to avoid this problem.
120414  0:00:25 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87039427'.
120414  0:58:37 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87039427'. New state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87846901'.
120414  2:20:34  InnoDB: ERROR: the age of the last checkpoint is 241588252,
InnoDB: which exceeds the log group capacity 241588224.
InnoDB: If you are using big BLOB or TEXT rows, you must set the   
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.

My.cnf is as below.

default-storage-engine=innodb
default-table-type=innodb
key_buffer              = 384M
max_allowed_packet      = 64M
thread_stack            = 256K
thread_cache_size       = 16
max_heap_table_size     = 64M
myisam_sort_buffer_size = 64M
join_buffer_size        = 8M
read_buffer_size        = 2M
read_rnd_buffer_size    = 8M
sort_buffer_size        = 3M
tmp_table_size          = 64M
# Innodb changes
innodb_additional_mem_pool_size = 16M
innodb_autoextend_increment     = 2000
innodb_buffer_pool_size         = 3000M #As current Db is around 1.2G.
innodb_file_per_table
innodb_data_file_path           = ibdata1:512M;ibdata2:512M:autoextend
innodb_flush_log_at_trx_commit  = 2 #For more reliablity use 1
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 8M
innodb_log_file_size            = 128M #Transaction Log up to 1/4 Buffer Pool
innodb_thread_concurrency       = 16
#innodb_force_recovery          = 2
#innodb_read_io_threads         = 8
#innodb_write_io_threads                = 8
innodb_lock_wait_timeout        = 50
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 400
table_cache            = 1024M
thread_concurrency     = 16

Any suggestions what I can change.

share|improve this question
    
Sorry... the first few lines got erased. –  TheVyom Apr 14 '12 at 7:46
    
Do yourself a favor and use a newer version of MySQL, if you can. 5.1.38 is ancient. At least upgrade to a more recent 5.1 release, if not 5.5 if you can. –  Aaron Brown Apr 17 '12 at 21:16
add comment

1 Answer

up vote 9 down vote accepted

At first glance, I would say that your innodb_log_file_size is way too small. It should be bigger to do two things:

  • Accommodate any big BLOB or TEXT fields
  • Holding bigger transactions

Here is what you should do for now to see if it helps:

STEP 01) Change the following in /etc/my.cnf

[mysqld]
innodb_log_buffer_size          = 32M
innodb_buffer_pool_size         = 3G
innodb_log_file_size            = 768M

STEP 02) service mysql stop

STEP 03) rm -f /var/lib/mysql/ib_logfile*

STEP 04) service mysql start

This will rebuild the following files

  • /var/lib/mysql/ib_logfile0
  • /var/lib/mysql/ib_logfile1

Give it a Try !!!

UPDATE 2013-07-03 12:37 EDT

I have updated my other posts on this and missed this one

ButtleButkus just commented at 2013-07-03 07:18:56 EDT

Wouldn't it be advisable to copy the ib_logfile* to another location for backup before deleting them?

Since there could be unfinished transactional data inside, here is what should be done

STEP 01) Change the following in /etc/my.cnf

[mysqld]
innodb_log_buffer_size          = 32M
innodb_buffer_pool_size         = 3G
innodb_log_file_size            = 768M

STEP 02) mysql -uroot -p -e"SET GLOBAL innodb_fast_shutdown = 0;"

STEP 03) service mysql stop

STEP 04) rm -f /var/lib/mysql/ib_logfile*

STEP 05) service mysql start

I added SET GLOBAL innodb_fast_shutdown = 0;. What does that do? It forces InnoDB to completely purge transactional changes from all of InnoDB moving parts, including the transactional logs (ib_logfile0, ib_logfile1). Thus, there is no need to backup the old ib_logfile0, ib_logfile1. If deleting them makes you nervous, then make Step 04

mv /var/lib/mysql/ib_logfile* ..

to put the old logs in /var/lib. If the recreation of the logs is successful and mysqld starts up, then you can delete the old logs.

I have been using this feature for a year now. I have updated my other posts to reflect this...

If there are other older posts of mine where I do not mention innodb_fast_shutdown, let me know so I can update it. Thanks again, ButtleButkus.

share|improve this answer
    
Thanks. Will try this and update. –  TheVyom Apr 16 '12 at 3:10
    
Increased the log file to 256M and reduced the buffer_pool_size to 2G seems to do the trick. –  TheVyom Apr 17 '12 at 8:50
    
Did my answer help ??? –  RolandoMySQLDBA May 15 '12 at 20:15
    
Sorry forgot to update this. This has helped a lot and our server has been up without any issues since last 1 month. –  TheVyom May 17 '12 at 4:19
    
Wouldn't it be advisable to copy the ib_logfile* to another location for backup before deleting them? –  Buttle Butkus Jul 3 '13 at 3:18
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.