June 19, 2013

Post: Measuring the amount of writes in InnoDB redo logs

October 8, 2012 By 4 Comments

Choosing a good InnoDB log file size is key to InnoDB write performance. This can be done by measuring the amount … redo log size calculations. After each one double checked the calculations, it turned out that we experienced something not expected: Using Innodb_os_log… constant write workload (a mono-threaded script that inserts one row at a time in a table, as fast as it…

Filed Under: Insight for DBAs, MySQL

Post: How to calculate a good InnoDB log file size

November 21, 2008 By 38 Comments

…can set innodb_log_file_size=64M Does that look surprisingly small? It might. I commonly see log file sizes … If this rule-of-thumb calculation ends up showing you that your log file size ought to be many gigabytes…. Perhaps you’re inserting a lot of big rows or something. In this case you might want …

Filed Under: Insight for DBAs Tagged With: ,

Post: SHOW INNODB STATUS walk through

July 17, 2006 By 40 Comments

… printout Innodb will print: ===================================== 060717 3:07:56 INNODB MONITOR OUTPUT ===================================== Per second averages calculated …Innodb builds for some pages to speed up row lookup replacing btree search with hash search. This section shows hash table size…

Filed Under: Insight for DBAs Tagged With:

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

…InnoDB tables) with a Scale Factor of 2 (InnoDB dataset size ~5G). I did not use Scale Factor of 40 (InnoDB dataset size…Innodb_data_reads 120552 123872 100551 103011 77213 Innodb_pages_read 120548 123868 100551 123592 100566 Innodb_rows… in how counter is calculated inside MariaDB? Other Observations…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , , , , , , , , , ,

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…innodb_ft_min_token_size to 1, dropped/rebuilt the InnoDB…InnoDB table, be prepared for a table rebuild. Calculation…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Comment: Researching your MySQL table sizes

June 28, 2012 By 28 Comments

…=innodb utf8; for calculating approximate data size i use the formula : (max row size * collation length(bytes)) * number of rows) (i.e)–> ((61*4)*100) { max row size : name = (50+3) , place_id = ( 4) , lastdt_modified = (4) : Total = 61 For 100 rows = 24400 – > 24 KB } the size of one row calculated according…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Tuning InnoDB Concurrency Tickets

May 24, 2010 By 5 Comments

…row examined + 1 ticket per row updated DELETE: 1 ticket per row examined + 1 ticket per row deleted ALTER: (2 * rows…, you can calculate these values …innodb_log_buffer_size = 8M innodb_log_files_in_group=2 innodb_log_file_size=1900M innodb_thread_concurrency=16 innodb_flush_method = O_DIRECT innodb…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , ,

Post: Statistics of InnoDB tables and indexes available in xtrabackup

September 14, 2009 By 6 Comments

… is that or another index in InnoDB … you had to calculate it yourself by multiplying size of row (which I should add is harder…` (`from_message_id`,`link_id`) ) ENGINE=InnoDB AUTO_INCREMENT=26141165 DEFAULT CHARSET=utf8; And size of file is about 12.88 GB… table by itself, as InnoDB is clustering data by primary key) takes 497839 pages ( 16KB each) and size of data 7492026403 bytes…

Filed Under: Percona Software Tagged With: , ,

Post: Connecting orphaned .ibd files

May 13, 2011 By 19 Comments

… 3 SPACE: 15 PAGE_NO: 3 *************************** 2. row *************************** TABLE_ID: 13 ID: 16 NAME:…SIZE in the source code). Let’s take a look at the page header: InnoDB Page Header Name Size…new style checksum) page 11: new style: calculated = 0x E4189B9B; recorded = 0x C168689B fixing …

Filed Under: MySQL

Post: How much memory can MySQL use in the worst case?

February 12, 2009 By 17 Comments

…innodb_additional_mem_pool_size, by the way. If it needs more it will allocate it. innodb_additional_mem_pool_size…1); +—————–+ | get_lock(@a, 1) | +—————–+ | 1 | +—————–+ 1 row in set (3.63 sec) Now….cnf for a server, I usually calculate the Big Ones like the buffer …

Filed Under: Insight for DBAs Tagged With:
« Older Posts