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.