Re: 4 GB RAM and runs so low the PC screams

[Mike Jonsson]

Thanks for taking your time to follow this issue Rick.

/*Edit:

note 1: setting MySQL MyISAM to SWAP = OFF did absolutely nothing for performance in this case. To bad as I hoped it would.

note 2: Key Buffer space runs up to 100% usage and thats it.
Most likely where the big caveat to this lay in respect to MySQL and non OS.

End Edit.*/

I modified the code again and removed all calls to FLUSH TABLES.
Currently I have 3 188 MB used RAM left and I guess something is working its way up on the scale.

Windows appears to be allocating free/stand by memory quite well now but it is sad to see I still cant make use of it all.

As for the "non issue" I am still not convinced.
Perhaps I am "damaged by M$" when I still suspect something in the system.

The thing that bugs me the most is likely that it will take another 6-8 days to complete this process just to build my LUT's.

I read quite a lot of your RoT's and other pages which I found to be extremely useful.

As a further comparison of various methods I can tell you what I also tested before all this.

1) Generating all tables in ASCII format and trying LOAD FILE was a dead end.
It would take approx. 2-3 hours to run 1 file and each file is then 1.3 GB
All in all doing the math it is unsuitable for regular modifications as
it is a matter of 10*10^6 lines per file and I estimate landing on
12,8 - 14 * 10^9 lines total in all tables.

It would also require a further split from a main table to the sub tables
sort of "partitioning" the data by bits values without having partitioning
available in MySQL.

The move transfer internally takes equally as long time.

2) Running INSERT INTO from ASCII produced a massive block.
It is not suitable because it does one line at a time.

3) INSERT INTO with blocks of data was not possible either.

All three examples above took exactly the same time when compounded
by time to load, time to sub divide and time to truncate temp tables.

4) Loading only the 5 g0-g4 variables and using a trigger;

I made a work order by the principle

LOAD FILE > ON UPDATE > Split to sub tables
When loaded, truncate the temp table.

Okay, this I needed as much as a hole in my head.
It took 4-5 times longer than LOAD FILE and uses at least
20 times the number of calls and procedures.

In addition to this, the reason my code now uses CASE > END CASE;
is simply because I copied the model from this test and
statements using PREPARE or dynamic SQL are not allowed in triggers.

Apparently triggers also inherit this source/limit from called
stored procedures so this method proved completely useless
for large data sets.

5) As you earlier said, using CURSOR was completely out of the question.
There I would likely have nearly slammed on of those hard limits in MySQL
you stated in your papers we could not hit.

And in all, I remain extremely surprised because the complete set of data
which needs to be checked is just above 33 * 10^9 lines.

In my world, not a very large range of data sets.

I fear what will happen when I try to do the final check and run the cross references of the sub tables checking (7*10^9)^2 possible entries.



Edited 2 time(s). Last edit at 04/02/2013 09:49AM by Mike Jonsson.