I have a script I am running that will allow the user to add test junk to the mySQL DB for testing backups. The script offers the choice to add records of size X until the DB reaches a target size. The script works, but always overshoots the target size because when you get the DB size

SELECT Round(Sum(data_length + index_length) / 1024 / 1024, 1) \"DB Size in MB\"
FROM information_schema.tables 
WHERE table_schema='dbName' 
GROUP BY table_schema;

it doesn't seem to account for the buffer. So the script eventually detects I've reached the target size, but only when the buffer happens to flush (during debugging, I added 1MB text blocks one at a time, and the DB size reported as 2.5MB a few hundred times before jumping up to ~450MB).

I did try to FLUSH TABLES between each insert, but this just made the DB size constantly report as 0.0 even with 7000+ rows.

How can I ensure I am getting an accurate DB size after each insert (preferably without disabling innodb for the whole DB or restarting mysql)?

Edit: I am using TEXT for the data columns. COMMIT before pulling DB size doesn't work (still wind up at ~450MB). SQL_NO_CACHE on SELECT statement above doesn't work (still wind up at ~450MB).


Plan A:

Shrink innodb_buffer_pool_size so as to force flushing to disk sooner.

  • Be cautious -- too small (a few MB) a buffer_pool may lead to a hang or crash.
  • There is still some caching, so you still cannot get precisely what you want.

Plan B:

  1. Insert 1000 rows.
  2. Shutdown MySQL (only this once)
  3. Do the math.
  4. Extrapolate to see how many rows to insert.
  5. Finish to the computed limit.

Caveat: The math won't be quite right, so you might under/overshoot.

Plan C:

As with Plan B, but do a second shutdown and extrapolation after inserting half the number of rows computed from Plan B. This should give you a much more accurate target.

Plan D:

From my experience, InnoDB tables are usually 2x to 3x larger than MyISAM tables with the same schema and same data. Since MyISAM table size is rather easy to compute, will doing that, then multiplying by 2 to 3 be good enough?

If all columns are TEXT, that is a pretty messy schema. The storage of data will be compromised. SELECTs will have to work harder, especially for range tests on numeric values stored in TEXT columns.

  • in this context, the innodb table size was wildly random (compression?) and seemed to have no predictable relationship to what should / would be a comparably-populated myisam table size. The innodb tables were actually smaller than the myisam tables. – Mike M Jan 31 '18 at 21:58

While researching the full list of available commands to create / delete / modify tables, I wound up settling on using dedicated test tables and basing sizing on those specific tables independently of the rest of the DB. That allowed me to specify the engine for each table I wanted to use as a test table, and the myISAM engine reports accurate sizing immediately.

Final solution command:

CREATE TABLE testtable (<column specs>) ENGINE=MyISAM;

innodb_change_buffer_max_size is RESERVED first thing and implies innodb_buffer_pool_size available to you will be reduced. If innodb_change_buffer_max_size is 25 and innodb_buffer_pool_size is 10G, you have 7.5G available. Considering data in the buffer pool is not compressed, be practical and leave wiggle room.


Make use of the variable innodb_max_dirty_pages_pct. This variable establishes a target for flushing activity from the buffer pool to disk.

set global innodb_max_dirty_pages_pct = 0; - Triggers the buffer pool flushing from memory to disk.



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.