MySQL Forums :: InnoDB :: how to calculate innodb log file size


Advanced Search

how to calculate innodb log file size
Posted by: sandeep thakur ()
Date: January 08, 2012 05:33PM

Hi,

I am getting following errors:
++++
120105 4:47:06 InnoDB: ERROR: the age of the last checkpoint is 9433832,
InnoDB: which exceeds the log group capacity 9433498.
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.
++++

Please let me know the exact cause of this error, i.e. is Innodb log-files size is culprit for this error or somthing else is there.

And Please suggest me how much log file size will be fine for innodb log files & how to calculate the size of innodb log files.

++++++++++++


Following is the details of tables having engine innodb & datatype either mediumtext, text or blob :
+--------------+---------------------+--------+----------------+-------
| TABLE_NAME | ENGINE | AVG_ROW_LENGTH | DATA_LENGTH | INDEX_LENGTH | DATA_TYPE
+--------------+---------------------+--------+----------------+-------
| tab1 | MyISAM | 2771 | 4938187188 | 88826880 | text
| tab2 | MyISAM | 76 | 346968 | 297984 | text
| tab3 | InnoDB | 2749 | 24690688 | 606208 | text ( 2 columns having TEXT datatype )
| tab4 | InnoDB | 961 | 96043008 | 3178496 | text
| tab5 | InnoDB | 5002 | 514686976 | 8994816 | text
| tab6 | InnoDB | 1201 | 13123584 | 753664 | text
| tab7 | InnoDB | 0 | 16384 | 49152 | text
| tab8 | InnoDB | 821 | 516947968 | 101220352 | mediumtext ( 2 columns having mediumtext datatype )
| tab9 | InnoDB | 57 | 3686400 | 11124736 | text
| tab10 | InnoDB | 117 | 16384 | 49152 | mediumtext
+--------------+---------------------+--------+----------------+-------

Details of innodb variables & status is below:
++++++
mysql> show variables like "%innodb%";
+---------------------------------+------------------------+
| Variable_name | Value |
+---------------------------------+------------------------+
| have_innodb | YES |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_adaptive_hash_index | ON |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 1048576 |
| innodb_log_file_size | 15728640 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 8 |
| innodb_thread_sleep_delay | 10000 |
+---------------------------------+------------------------+
36 rows in set (0.00 sec)

mysql>
mysql> show status like "%innodb%";
+-----------------------------------+--------------+
| Variable_name | Value |
+-----------------------------------+--------------+
| Com_show_innodb_status | 0 |
| Innodb_buffer_pool_pages_data | 506 |
| Innodb_buffer_pool_pages_dirty | 3 |
| Innodb_buffer_pool_pages_flushed | 4204167 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_misc | 6 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 6990 |
| Innodb_buffer_pool_read_ahead_seq | 237081 |
| Innodb_buffer_pool_read_requests | 3401488211 |
| Innodb_buffer_pool_reads | 10800889 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 237055424 |
| Innodb_data_fsyncs | 9290596 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 227158446080 |
| Innodb_data_reads | 11868498 |
| Innodb_data_writes | 12065132 |
| Innodb_data_written | 152145045504 |
| Innodb_dblwr_pages_written | 4204167 |
| Innodb_dblwr_writes | 107761 |
| Innodb_log_waits | 262 |
| Innodb_log_write_requests | 23408271 |
| Innodb_log_writes | 9037179 |
| Innodb_os_log_fsyncs | 9075668 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 14359335424 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 138972 |
| Innodb_pages_read | 13864391 |
| Innodb_pages_written | 4204167 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
| Innodb_rows_deleted | 3629897 |
| Innodb_rows_inserted | 9500632 |
| Innodb_rows_read | 618505966 |
| Innodb_rows_updated | 4631326 |
+-----------------------------------+--------------+
43 rows in set (0.00 sec)

mysql> show innodb status\G
*************************** 1. row ***************************
Status:
=====================================
120108 18:27:43 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 35 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1206735, signal count 1205599
Mutex spin waits 0, rounds 51307899, OS waits 294621
RW-shared spins 947779, OS waits 398054; RW-excl spins 718205, OS waits 488482
------------
TRANSACTIONS
------------
Trx id counter 0 12336385
Purge done for trx's n:o < 0 12336358 undo n:o < 0 0
History list length 18
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 13510, OS thread id 1163561280
MySQL thread id 41307, query id 23169018 localhost root
show innodb status
---TRANSACTION 0 12336384, not started, process no 13510, OS thread id 1162496320
MySQL thread id 37696, query id 23169017 Has read all relay log; waiting for the slave I/O thread to update it
--------
FILE I/O
--------
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
11868498 OS file reads, 12065144 OS file writes, 9290606 OS fsyncs
9.80 reads/s, 32003 avg bytes/read, 27.23 writes/s, 8.40 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 243, seg size 245,
5557878 inserts, 5557878 merged recs, 733324 merges
Hash table size 17393, used cells 3071, node heap has 6 buffer(s)
0.66 hash searches/s, 87.40 non-hash searches/s
---
LOG
---
Log sequence number 4 112577466
Log flushed up to 4 112577466
Last checkpoint at 4 112576494
0 pending log writes, 0 pending chkp writes
9083219 log i/o's done, 4.46 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 23634866; in additional pool allocated 1048576
Buffer pool size 512
Free buffers 0
Database pages 506
Modified db pages 3
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 13864391, created 138972, written 4204170
19.14 reads/s, 0.00 creates/s, 35.37 writes/s
Buffer pool hit rate 956 / 1000
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread process no. 13510, id 1161165120, state: sleeping
Number of rows inserted 9500639, updated 4631326, deleted 3629897, read 618505966
0.66 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set, 1 warning (0.00 sec)
++++++

Options: ReplyQuote


Subject Views Written By Posted
how to calculate innodb log file size 4697 sandeep thakur 01/08/2012 05:33PM
Re: how to calculate innodb log file size 1353 Toomula Nanda kishore 01/08/2012 11:51PM
Re: how to calculate innodb log file size 1917 Rick James 01/09/2012 11:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.