Export Large Table

Cpecific's profile image Cpecific posted 1 year ago in Import/Export Permalink

HeidiSQL 12.2.0.6576

I have a large table (7.5GB; 1.3 million rows; fixed rows, around 5952 bytes). When I try to export this table with data (either to zip or directly to another sql server), Heidi tries to fetch entire table, instead of splitting everything into chunks (Max INSERT size doesn't work). And cancel button doesn't work either, cause entire app is hang up on the current query.

If I were to guess, the problem for Heidi is my primary key (it uses 2 columns).

It consists of (in specified order):

  1. Date.
  2. Int.

Do you think you can fix it?

ansgar's profile image ansgar posted 1 year ago Permalink

Please watch out for the SELECT query in the log panel when you export that table. HeidiSQL limits the number of rows by calculating chunks based on the average row length of the table.

I just ran a test on a 700MB table with 1.5 mio rows, and HeidiSQL uses a limit:

Description

Cpecific's profile image Cpecific posted 1 year ago Permalink

In my case it tries to fetch many many times more that it should.

1 attachment(s):
  • Untitled
ansgar's profile image ansgar posted 1 year ago Permalink

Yes that looks indeed strange. I suppose the average row length is very small, so the calculated row number is too high.

You could check that with such a query:

SHOW TABLE STATUS LIKE 'reporttag_opt';

Please post the results here, especially the avg_row_len.

Cpecific's profile image Cpecific posted 1 year ago Permalink

Name;Engine;Version;Row_format;

reporttag_opt;MyISAM;10;Fixed;

.

Rows;Avg_row_length;Data_length;Max_data_length;

1345793;5950;8007468350;1674776111428403199;

.

Index_length;Data_free;Auto_increment;Create_time;

18138112;0;\N;2022-11-24 15:56:26;

.

Update_time;Check_time;Collation;Checksum;

2022-12-02 10:55:44;\N;utf8mb4_general_ci;\N;

.

Create_options;Comment;Max_index_length;Temporary

row_format=FIXED;;4398046510080;N

ansgar's profile image ansgar posted 1 year ago Permalink

Thanks. I will look at the calculation code after work.

Code modification/commit 889a1b3 from Ansgar Becker <anse@heidisql.com>, 1 year ago, revision 6588
SQL export: Calculate a reasonable LIMIT clause when "Get full table status" session setting is disabled, causing AvgRowLen<=0. See https://www.heidisql.com/forum.php?t=40058
ansgar's profile image ansgar posted 1 year ago Permalink

The current limit was calculated with the formula 100 * 1024 * 1024 / Avg_Row_Len. In your case, Avg_Row_Len was 1, resulting in a LIMIT 104857600. It's "1" likely because you have disabled the session setting "Get full table status", right?

Description

Next builds assume a default Avg_Row_Len of 10000 bytes, so without the full table status you will get a limit of 10486, which should perform much more responsively.

Cpecific's profile image Cpecific posted 1 year ago Permalink

It's "1" likely because you have disabled the session setting "Get full table status", right?

No. I never even touched that tab. And this option is currently checked.

I have posted my data. Avg_Row_Len equals 5950.

And yeah, it works after last update. Thanks.

gulshan212's profile image gulshan212 posted 1 year ago Permalink

Hi this is Gulshan Negi I think the number of rows is too much, you need to recheck your query and run it.

Thanks

Please login to leave a reply, or register at first.