How To Choose The Right Database?

[davidktw]

It may be only about database, but the down-to-earth narration is golden.
It could be applied into just about anything you encounter in your technical career or maybe even life choices.

 

[peterchan75]

Good Morning davidktw,
I have quarterly backup text files of about 1.5M records for 16 quarters. The file size is about 100MB for each quarter. At the present moment, it's still manageable. For some reports, I have to restrict to no more than 4 quarters or it would crash. It is time for a database?

 

[davidktw]

Good Morning davidktw,
I have quarterly backup text files of about 1.5M records for 16 quarters. The file size is about 100MB for each quarter. At the present moment, it's still manageable. For some reports, I have to restrict to no more than 4 quarters or it would crash. It is time for a database?

Database is not the sole data persistent solution. Files will work fine too if you know what you are doing. Since you did not mention how the files are used and when they are used, there is nothing I can recommend over how it is designed to be used today.

A file system can have millions of files and it doesn’t crash, the question is how you organise the files to facilitate your usage. Eg: Are they stored all in one directory hence making search through the directory slow, or you are imposing high concurrency load which your storage solution doesn’t scale well, or there are too much concurrency hence causing high latency due to excessive random seek while you are still on magnetic disks. Some of the solutions could be distribution of load, some could be implementations of caching and so forth.

Database does offer some advantages in its features like caching and more logical organisation and structuring, but these doesn’t comes free. They take up memory and processing power to achieve. Database are also bad candidate for large binary files, despite it can. If you have no need for ACID behaviour in how you manage the data or that transactional behaviour is unnecessary because of the way you access the information, then a database might not be the right way to manage the data. However a database may be employed to store metadata more effectively than storing them in the memory as basic data structures or on metadata files in some cases.

We can discuss on this further if you are willing to share more on how your files are used.

 

[peterchan75]

It's not a multi users database and it's mainly for reporting and off-line.
1. Database has 8 fields
2. Manager ID table
3. Product ID table
Data comes in every quarter.
Reports are by manager, product and combination.
Each quarter, there are revision to all the previous quarters either addition or replacement.

Here is the noob question.
Maintain quarterly data in separate table or one table?

 

[davidktw]

It's not a multi users database and it's mainly for reporting and off-line.
1. Database has 8 fields
2. Manager ID table
3. Product ID table
Data comes in every quarter.
Reports are by manager, product and combination.
Each quarter, there are revision to all the previous quarters either addition or replacement.

Here is the noob question.
Maintain quarterly data in separate table or one table?

Noted on your information, but not informative enough.

Do you see any need to separate into different table when one table can manage all?
If you need to quickly discard any rows without causing too much holes in the table files, you can always use partitioning if your database offers.

Partitioning can also offers performance gain.

https://vertabelo.com/blog/everything-you-need-to-know-about-mysql-partitions/

 

[peterchan75]

Noted on your information, but not informative enough.

Do you see any need to separate into different table when one table can manage all?
If you need to quickly discard any rows without causing too much holes in the table files, you can always use partitioning if your database offers.

Partitioning can also offers performance gain.

https://vertabelo.com/blog/everything-you-need-to-know-about-mysql-partitions/

If new data appear periodically, then is it good to have a table for each period. I can easily drop the table if they deem too old or irrelevant. Since MS Office does come with Access, I am thinking giving it a spin. perl Win32 OLE and MS Access do come handy.

 

[davidktw]

If new data appear periodically, then is it good to have a table for each period. I can easily drop the table if they deem too old or irrelevant. Since MS Office does come with Access, I am thinking giving it a spin. perl Win32 OLE and MS Access do come handy.

What you have described is exactly what Partition does. You can just drop the partition and it will be done. Read up the article I have provided and read up the mysql manual or your database manual and try.

 

[peterchan75]

What you have described is exactly what Partition does. You can just drop the partition and it will be done. Read up the article I have provided and read up the mysql manual or your database manual and try.

Thank you very much, master of IT.

 

[davidktw]

Thank you very much, master of IT.

愧不敢当，只是分享行道罢了。

 

[diminishin]

It's not a multi users database and it's mainly for reporting and off-line.

If this is the case you don't even need a database at all.
Just convert the files to parquet format for much better compression rates and the file size should become much smaller. Organise the files into date partitions so your queries will be faster. Like so, .../files/20221001/*.parquet

When you need to query the data just load the files into spark SQL and run the SQL directly. It's just a few lines of code.

 

[davidktw]

If this is the case you don't even need a database at all.
Just convert the files to parquet format for much better compression rates and the file size should become much smaller. Organise the files into date partitions so your queries will be faster. Like so, .../files/20221001/*.parquet

When you need to query the data just load the files into spark SQL and run the SQL directly. It's just a few lines of code.

Correct me if I'm wrong, instead of a RDBMS suggested by TS, you recommend not using it, but rather use an even more complex Hadoop cluster solution ? I wonder how is Apache Spark more lightweight here ?

 

[diminishin]

Correct me if I'm wrong, instead of a RDBMS suggested by TS, you recommend not using it, but rather use an even more complex Hadoop cluster solution ? I wonder how is Apache Spark more lightweight here ?

Don't need Hadoop actually cos the amount of data isn't alot. And we don't need hdfs too. Just store the data in file system can already.

Spark is easy to install too. Don't need to run it on k8 or yarn. Just run locally will do.

Cos he mentioned 1.5m for 4q is really small, just run everything locally and it'll still be fast haha

 

[davidktw]

Don't need Hadoop actually cos the amount of data isn't alot. And we don't need hdfs too. Just store the data in file system can already.

Spark is easy to install too. Don't need to run it on k8 or yarn. Just run locally will do.

Cos he mentioned 1.5m for 4q is really small, just run everything locally and it'll still be fast haha

If like you mentioned everything in the single node suffice, then why even employ a solution that is designed to scale in the first place?

Neither do a RDBMS need sophisticated installation this days. It is so straightforward that just a single package manager command would suffice.

1.5m for a RDBMS is nothing too.
I am not saying Apache Spark is a bad solution, but when you say you don’t need to install a RDBMS and instead suggested yet another thing to install and use, seems to be like lets not use Java, use dotNET instead. It becomes more of a choice than saving anything.

It feels like jumping the gun moving up the tier for a different solution when the most straightforward rdbms is more than capable in handling the workload.

Actually in this case, I am not so sure if RDBMS will even need to be employed just to manage reports, and I am also not sure if the reports are to be generated by the database or stored in the database based on what TS has articulated.

There are also very lightweight rdbms like sqlite that can do wonders before engaging a standalone rdbms installation, say if a relational management is really required where in this case, there is no concurrent users at all.

 

[diminishin]

If like you mentioned everything in the single node suffice, then why even employ a solution that is designed to scale in the first place?

Neither do a RDBMS need sophisticated installation this days. It is so straightforward that just a single package manager command would suffice.

1.5m for a RDBMS is nothing too.
I am not saying Apache Spark is a bad solution, but when you say you don’t need to install a RDBMS and instead suggested yet another thing to install and use, seems to be like lets not use Java, use dotNET instead. It becomes more of a choice than saving anything.

Like feel like jumping the gun moving up the tier for a different solution when the most straightforward rdbms is more than capable in handling the workload.

Actually in this case, I am not so sure if RDBMS will even need to be employed just to manage reports, and I am also not sure if the reports are to be generated by the database or stored in the database based on what TS has articulated.

There are also very lightweight rdbms like sqlite that can do wonders before engaging a standalone rdbms installation, say if a relational management is really required where in this case, there is no concurrent users at all

The amount of data is so small that actually any solution will do just fine. This is a perfect opportunity to try out new technologies like spark if one have not used it before.

Not like it matters since the amount of data is small, but you get better cost savings on storage too if u convert the files to parquet.

 

[davidktw]

The amount of data is so small that actually any solution will do just fine. This is a perfect opportunity to try out new technologies like spark if one have not used it before.

Not like it matters since the amount of data is small, but you get better cost savings on storage too if u convert the files to parquet.

Sure for trying new stuffs, but that is not how we do solutioning. It is not a development exercise, new is not always better.

In any case, I don’t find the storage size is a compelling reason. Because there is no mentioning of what size we are dealing with here, and compression/decompression is not free either. One is just exchanging one thing for another. Parquet compression only make most sense when you are in the first place dealing with extremely large data set which make it a more compelling feature on cost and network throughput if needed to send across the network.

For 1.5m or even 10m records, I will be surprise if it even get beyond the 10GB range.

 

[peterchan75]

Don't need Hadoop actually cos the amount of data isn't alot. And we don't need hdfs too. Just store the data in file system can already.

Spark is easy to install too. Don't need to run it on k8 or yarn. Just run locally will do.

Cos he mentioned 1.5m for 4q is really small, just run everything locally and it'll still be fast haha

Sorry. It's 1.5M records per quarter. File size is about 90-100MB per quarter. I have a round 16 quarters.
The reason I am thinking of moving away from text delimited file is perl went BSOD(crashed) when I go beyond 4 qtr in one of my reports. It could be my temp array got too big. I have 16GB of RAM in my i7-2600 with plenty of SSD space.

 

[davidktw]

Sorry. It's 1.5M records per quarter. File size is about 90-100MB per quarter. I have a round 16 quarters.
The reason I am thinking of moving away from text delimited file is perl went BSOD(crashed) when I go beyond 4 qtr in one of my reports. It could be my temp array got too big. I have 16GB of RAM in my i7-2600 with plenty of SSD space.

Well at merely 100MB is actually far from large. I have process logfiles in the range of GBs.
Before you even go into using database for your purpose, you should consider changing the way you process data in your perl script.
Perl has quite a number of modules that can mimic file based data as Array/Hash so that your script can treat the data as in memory, but actually is backed by file.
It will be slower, but it will be able to scale.

There are also embedded database(SQLite/Berkeley DB) for your use. Perl magical symbol system make the accessing of the file like Hash for your application too. So you can read in your text file and store as file backed database for processing.


Here are some references for your reading
https://metacpan.org/pod/File::Maphttps://metacpan.org/pod/Tie::File::AsHashhttps://metacpan.org/pod/DB_File::DB_Databasehttps://metacpan.org/pod/Tie::Persistenthttps://metacpan.org/pod/MLDBM
Here is a demonstration of how I create a BerkleyDB backed file of 2mils of complex elements stored inside 648MB file

#!/usr/bin/env perl

use strict;
use warnings;
use MLDBM qw(DB_File Storable);
use Fcntl;
use Data::Dumper;

my %dict;
my $db = tie %dict, 'MLDBM', './dictfile.db', O_CREAT|O_RDWR, 0640 or die $!;

# this is to simulate reading in from a file
# and store temporarily into a hash data structure
# for (my $i = 0; $i < 2000000; $i++) {
#   print "\r$i";
#   $dict{$i} = {
#     name => $i,
#     some_value => "$i is $i - "."$i" * 10,
#     some_values => [ ("$i") x 10 ]
#   };
# }

system('ls -lh ./dictfile.db');

print scalar(keys(%dict)), "\n";
for (my $i = 0; $i < 3; $i++) {
  print Dumper($dict{int(rand(2000000))}), "\n";
}

# vim: sw=2 ts=2 et si ci

The output when executed

$ ./demo.pl
-rw-r----- 1 davidktw davidktw 648M Dec 22 22:23 ./dictfile.db
2000018
$VAR1 = {
          'some_values' => [
                             '1956424',
                             '1956424',
                             '1956424',
                             '1956424',
                             '1956424',
                             '1956424',
                             '1956424',
                             '1956424',
                             '1956424',
                             '1956424'
                           ],
          'name' => '1956424',
          'some_value' => '1956424 is 1956424 - 19564240'
        };

$VAR1 = {
          'some_value' => '743354 is 743354 - 7433540',
          'name' => '743354',
          'some_values' => [
                             '743354',
                             '743354',
                             '743354',
                             '743354',
                             '743354',
                             '743354',
                             '743354',
                             '743354',
                             '743354',
                             '743354'
                           ]
        };

$VAR1 = {
          'some_value' => '1748290 is 1748290 - 17482900',
          'some_values' => [
                             '1748290',
                             '1748290',
                             '1748290',
                             '1748290',
                             '1748290',
                             '1748290',
                             '1748290',
                             '1748290',
                             '1748290',
                             '1748290'
                           ],
          'name' => '1748290'
        };

If I changed the 2nd loop to read through the hash randomly for 1million times, the timing would be the following

$ time ./demo.pl >/dev/null

real	0m47.740s
user	0m44.491s
sys	0m3.243s

While doing so, the top shows 13% of the memory used by Perl which equate to less than 300MB of memory.

Tasks: 167 total,   2 running, 163 sleeping,   2 stopped,   0 zombie
%Cpu(s): 25.0 us,  1.6 sy,  0.0 ni, 73.4 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :   1976.1 total,    455.2 free,    501.4 used,   1019.5 buff/cache
MiB Swap:   2048.0 total,   2008.0 free,     40.0 used.   1307.3 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
   8578 davidktw  20   0  269560 262320   6744 R 100.0  13.0   0:46.70 perl
      1 root      20   0  170612   8496   4772 S   0.0   0.4   0:03.60 systemd
      2 root      20   0       0      0      0 S   0.0   0.0   0:00.01 kthreadd
      3 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_gp
      4 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_par_gp
      6 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 kworker/0:0H-events_highpri
      8 root       0 -20       0      0      0 I   0.0   0.0   0:07.29 kworker/0:1H-kblockd
      9 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 mm_percpu_wq
     10 root      20   0       0      0      0 S   0.0   0.0   0:00.08 ksoftirqd/0
     11 root      20   0       0      0      0 I   0.0   0.0   0:02.34 rcu_sched
     12 root      rt   0       0      0      0 S   0.0   0.0   0:00.03 migration/0

Hence before you even consider moving into a standalone database (BerkelyDB is a file-based database) for this purpose, you may want to change your script to use file backed data structure first.
You should also consider line by line processing of your text file if possible, instead of reading everything into the memory if that is possible. If not, then what I have suggested may save you a lot of effort to convert from what is already working for you.

Below is an updated set of codes using the most recent BerkeleyDB perl module

#!/usr/bin/env perl

use strict;
use warnings;
#use MLDBM qw(DB_File Storable);
use BerkeleyDB;
use MLDBM qw(BerkeleyDB::Hash Storable);
use Fcntl;
use Data::Dumper;

my %dict;
#my $db = tie %dict, 'MLDBM', './dictfile.db', O_CREAT|O_RDWR, 0640 or die $!;
my $db = tie %dict, 'MLDBM', -Filename => './dictfile.db', -Flags => DB_CREATE or die $!;

# this is to simulate reading in from a file
# and store temporarily into a hash data structure
# for (my $i = 0; $i < 2000000; $i++) {
#   print "\r$i";
#   $dict{$i} = {
#     name => $i,
#     some_value => "$i is $i - "."$i" * 10,
#     some_values => [ ("$i") x 10 ]
#   };
# }

system('ls -lh ./dictfile.db');

print scalar(keys(%dict)), "\n";
for (my $i = 0; $i < 1000000; $i++) {
  print Dumper($dict{int(rand(2000000))}), "\n";
}

# vim: sw=2 ts=2 et si ci

 

[davidktw]

@peterchan75
This is the most recent codebase with some timings

#!/usr/bin/env perl

use strict;
use warnings;
#use MLDBM qw(DB_File Storable);
use BerkeleyDB;
use MLDBM qw(BerkeleyDB::Hash Storable);
use Fcntl;
use Data::Dumper;

my %dict;
#my $db = tie %dict, 'MLDBM', './dictfile.db', O_CREAT|O_RDWR, 0640 or die $!;
my $db = tie %dict, 'MLDBM', -Filename => './dictfile.db', -Flags => DB_CREATE, -Cachesize => 1000 * 1024 * 1024 or die $!;

# this is to simulate reading in from a file
# and store temporarily into a hash data structure
# for (my $i = 0; $i < 2000000; $i++) {
#   print STDERR "\r$i";
#   $dict{$i} = {
#     name => $i,
#     some_value => "$i is $i - "."$i" * 10,
#     some_values => [ ("$i") x 10 ]
#   };
# }
# $db->db_sync();

system('ls -lh ./dictfile.db 2>&1');

print scalar(keys(%dict)), "\n";
for (my $i = 0; $i < 2000000; $i++) {
  print $dict{int(rand(2000000))}{name}, "\n";
}

# vim: sw=2 ts=2 et si ci

First I declare the Dumper in the 2nd loop as it is dominating in the timing and it is a unrealistic operation too.
Second I change the loop to 2mils times.
Third I added much more significant caching

The first 2 timing below is using merely 100M of cache

$ time ./demo.pl >/dev/null

real	0m30.878s
user	0m26.887s
sys	0m3.988s
davidktw@ul2004lts:~$ time ./demo.pl >/dev/null

real	0m27.876s
user	0m23.962s
sys	0m3.912s

Then I change the cache to 1GB the ran the same codebase

$ time ./demo.pl >/dev/null

real	0m19.978s
user	0m19.410s
sys	0m0.566s
davidktw@ul2004lts:~$ time ./demo.pl >/dev/null

real	0m24.029s
user	0m23.452s
sys	0m0.573s

We can see the cache size matters. In my case it's random which makes the hit more or less uniform.
In most cases, actual workload of reading writing access should have some kind of locality that makes better use of the memory instead of reading from the disk for frequently access information typical processing.
As such you may experience much better performance, but even for my uniform random workload, I see there is quite a fair bit of cache hits that provided better results. This is how you can make use of the abundance in file storage and limited onboard memory as cache.

Hope this helps. Have fun

 

[peterchan75]

Well at merely 100MB is actually far from large. I have process logfiles in the range of GBs.
Before you even go into using database for your purpose, you should consider changing the way you process data in your perl script.
Perl has quite a number of modules that can mimic file based data as Array/Hash so that your script can treat the data as in memory, but actually is backed by file.
It will be slower, but it will be able to scale.

There are also embedded database(SQLite/Berkeley DB) for your use. Perl magical symbol system make the accessing of the file like Hash for your application too. So you can read in your text file and store as file backed database for processing.


Here are some references for your reading
https://metacpan.org/pod/File::Maphttps://metacpan.org/pod/Tie::File::AsHashhttps://metacpan.org/pod/DB_File::DB_Databasehttps://metacpan.org/pod/Tie::persistenthttps://metacpan.org/pod/MLDBM
Here is a demonstration of how I create a BerkleyDB backed file of 2mils of complex elements stored inside 648MB file

#!/usr/bin/env perl

use strict;
use warnings;
use MLDBM qw(DB_File Storable);
use Fcntl;
use Data::Dumper;

my %dict;
my $db = tie %dict, 'MLDBM', './dictfile.db', O_CREAT|O_RDWR, 0640 or die $!;

# this is to simulate reading in from a file
# and store temporarily into a hash data structure
# for (my $i = 0; $i < 2000000; $i++) {
#   print "\r$i";
#   $dict{$i} = {
#     name => $i,
#     some_value => "$i is $i - "."$i" * 10,
#     some_values => [ ("$i") x 10 ]
#   };
# }

system('ls -lh ./dictfile.db');

print scalar(keys(%dict)), "\n";
for (my $i = 0; $i < 3; $i++) {
  print Dumper($dict{int(rand(2000000))}), "\n";
}

# vim: sw=2 ts=2 et si ci

The output when executed

$ ./demo.pl
-rw-r----- 1 davidktw davidktw 648M Dec 22 22:23 ./dictfile.db
2000018
$VAR1 = {
          'some_values' => [
                             '1956424',
                             '1956424',
                             '1956424',
                             '1956424',
                             '1956424',
                             '1956424',
                             '1956424',
                             '1956424',
                             '1956424',
                             '1956424'
                           ],
          'name' => '1956424',
          'some_value' => '1956424 is 1956424 - 19564240'
        };

$VAR1 = {
          'some_value' => '743354 is 743354 - 7433540',
          'name' => '743354',
          'some_values' => [
                             '743354',
                             '743354',
                             '743354',
                             '743354',
                             '743354',
                             '743354',
                             '743354',
                             '743354',
                             '743354',
                             '743354'
                           ]
        };

$VAR1 = {
          'some_value' => '1748290 is 1748290 - 17482900',
          'some_values' => [
                             '1748290',
                             '1748290',
                             '1748290',
                             '1748290',
                             '1748290',
                             '1748290',
                             '1748290',
                             '1748290',
                             '1748290',
                             '1748290'
                           ],
          'name' => '1748290'
        };

If I changed the 2nd loop to read through the hash randomly for 1million times, the timing would be the following

$ time ./demo.pl >/dev/null

real    0m47.740s
user    0m44.491s
sys    0m3.243s

While doing so, the top shows 13% of the memory used by Perl which equate to less than 300MB of memory.

Tasks: 167 total,   2 running, 163 sleeping,   2 stopped,   0 zombie
%Cpu(s): 25.0 us,  1.6 sy,  0.0 ni, 73.4 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :   1976.1 total,    455.2 free,    501.4 used,   1019.5 buff/cache
MiB Swap:   2048.0 total,   2008.0 free,     40.0 used.   1307.3 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
   8578 davidktw  20   0  269560 262320   6744 R 100.0  13.0   0:46.70 perl
      1 root      20   0  170612   8496   4772 S   0.0   0.4   0:03.60 systemd
      2 root      20   0       0      0      0 S   0.0   0.0   0:00.01 kthreadd
      3 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_gp
      4 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_par_gp
      6 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 kworker/0:0H-events_highpri
      8 root       0 -20       0      0      0 I   0.0   0.0   0:07.29 kworker/0:1H-kblockd
      9 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 mm_percpu_wq
     10 root      20   0       0      0      0 S   0.0   0.0   0:00.08 ksoftirqd/0
     11 root      20   0       0      0      0 I   0.0   0.0   0:02.34 rcu_sched
     12 root      rt   0       0      0      0 S   0.0   0.0   0:00.03 migration/0

Hence before you even consider moving into a standalone database (BerkelyDB is a file-based database) for this purpose, you may want to change your script to use file backed data structure first.
You should also consider line by line processing of your text file if possible, instead of reading everything into the memory if that is possible. If not, then what I have suggested may save you a lot of effort to convert from what is already working for you.

Below is an updated set of codes using the most recent BerkeleyDB perl module

#!/usr/bin/env perl

use strict;
use warnings;
#use MLDBM qw(DB_File Storable);
use BerkeleyDB;
use MLDBM qw(BerkeleyDB::Hash Storable);
use Fcntl;
use Data::Dumper;

my %dict;
#my $db = tie %dict, 'MLDBM', './dictfile.db', O_CREAT|O_RDWR, 0640 or die $!;
my $db = tie %dict, 'MLDBM', -Filename => './dictfile.db', -Flags => DB_CREATE or die $!;

# this is to simulate reading in from a file
# and store temporarily into a hash data structure
# for (my $i = 0; $i < 2000000; $i++) {
#   print "\r$i";
#   $dict{$i} = {
#     name => $i,
#     some_value => "$i is $i - "."$i" * 10,
#     some_values => [ ("$i") x 10 ]
#   };
# }

system('ls -lh ./dictfile.db');

print scalar(keys(%dict)), "\n";
for (my $i = 0; $i < 1000000; $i++) {
  print Dumper($dict{int(rand(2000000))}), "\n";
}

# vim: sw=2 ts=2 et si ci

I suppose I am just skimming on the surface and don't do deep dive. Similar to your gripe on not knowing the detail in sorting algorithm. Once problems surface, I don't know exactly what is going on.
In this particular report, I need to look at several quarters at one go due data dependency.
I am just guessing it's memory issue. It could be my logic too.

Thank you very much. I will explore your suggestion when I have time.

 

[davidktw]

I suppose I am just skimming on the surface and don't do deep dive. Similar to your gripe on not knowing the detail in sorting algorithm. Once problems surface, I don't know exactly what is going on.
In this particular report, I need to look at several quarters at one go due data dependency.
I am just guessing it's memory issue. It could be my logic too.

Thank you very much. I will explore your suggestion when I have time.

If you are not sure your segmentation fault is due to memory issue, you better confirm that before you make any change. Don’t bark up the wrong tree.

Say I would expect your line to be variable length delimited. If it is fixed length for each record, you could easily perform fseek on the file for you to randomly access each record by simple indexing mathematics.

If each line record is variable line, which is very normal say among log files. You can simply read thru the entire file once and find all the byte offsets of each line and save that as an index array in an index file. Load up the index file each time you want to access the data file so that you can perform seeking before accessing.

You can do it in Perl, but you can use the linux grep tool that is written in C to do it even much faster

grep -Po ‘your regex’ input file

Save the output in anyway convenient for the perl script to use