How far can I push a $7 server? - Importing CSV

How far can I push a $7 server?

That's what I wanted to find out.

I'm going to start with a very simple contact import, measure what happens, find the next bottleneck and change one thing at a time.

No perfect implementation from the beginning. No bigger server unless we actually need it. Let's see how far this little thing can go.

Constraints & experiment

Before starting, I needed a few rules so I couldn't conveniently change the experiment every time something went wrong.

• DigitalOcean Basic Droplet: 1 AMD vCPU, 1 GB RAM and 25 GB NVMe SSD.
• The CPU is shared, so there will be some noise between runs. That's fine. This isn't supposed to be a laboratory benchmark.
• Maximum import time: 10 minutes.
• CSV sizes: 1k, 5k, 10k, 50k, 100k, 250k, 500k, 1M and 5M contacts.
• Each CSV contains 90% new contacts and 10% existing contacts that need to be updated.
• The generated CSV contains valid, well-formed rows. Validation and malformed input handling are outside the scope of this experiment.
• Before every benchmark, the database is reset to a known state and preloaded with the contacts required for that 10% of updates.
• CSV files are streamed and processed progressively instead of loading the whole file into memory.
• Emails are unique within each generated CSV. The email address is used as the contact identity throughout the experiment.
• MySQL with InnoDB.
• Each statement runs using MySQL's default autocommit behavior. The importer doesn't use explicit database transactions.
• MySQL binary logs were cleared regularly during the benchmarks to prevent accumulated logs from consuming disk space and affecting subsequent runs.
• Each benchmark is initially executed three times.
• There's a 20-second cooldown between runs.
• This experiment is intentionally about a single sequential import process. Queues, workers and parallel processing would change the problem I'm measuring, so I'm leaving them for a separate experiment.
• CPU usage is measured using Linux /proc/stat immediately before and after the import.

The main things I'm measuring are total time, throughput and the number of database statements executed. I'm also keeping an eye on CPU usage and peak PHP memory.

This isn't a clean benchmark. It's a real application running on a cheap shared server, with all the noise that comes with it. That's kind of the point.

Data model

We only need a contact table with a name, email and phone number.

Email is the identity of a contact, so two contacts with the same email should represent the same record.

Column	Type	Notes
id	BIGINT	Primary key
name	VARCHAR	Contact name
email	VARCHAR(191)	Unique contact identifier
phone	VARCHAR	Nullable

Case 1: The simple way

We need to import contacts, so let's start with the simplest implementation I could reasonably write.

This is our initial table:

<?php

Schema::create('contacts', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email', 191);
    $table->string('phone')->nullable();
});

There's something intentionally missing here: the email index.

I know email is going to be used to find contacts, but I want to start without the index and see what actually happens.

The importer is also pretty straightforward:

<?php

$handle = fopen($scenario['csv'], 'r');
$headers = fgetcsv($handle);

while(($row = fgetcsv($handle)) !== false) {
    $data = array_combine($headers, $row);

    $name = $data['name'];
    $email = $data['email'];
    $phone = $data['phone'];

    $contact_exists = Contact::where('email', $email)->first();

    if(!$contact_exists){
        Contact::create([
            'name' => $name,
            'email' => $email,
            'phone' => $phone,
        ]);
    }else{
        $contact_exists->update([
            'name' => $name,
            'email' => $email,
            'phone' => $phone,
        ]);
    }
}

fclose($handle);

For every contact:

• Look for it by email.
• If it exists, update it.
• If it doesn't exist, insert it.

After three runs for each dataset, these are the results:

Contacts	Total time (seconds)	Rows/s	DB Statements	CPU	Memory	Result
1k	2.83	353.43	2,000	64.11%	26 MB	✅
5k	22.23	225.15	10,000	69.93%	28 MB	✅
10k	69.18	147.44	20,000	73.26%	28 MB	✅
50k	☠️ >10 min	—	—	—	—	Timed out with 81% processed

The problem is pretty obvious. The more rows we have, the worse the throughput gets. CPU isn't even fully saturated, but we're spending more and more time doing database work.

So, what would be the next obvious thing to check?

Case 2: Index is the key

I deliberately didn't add an index to the email column in Case 1. Our importer does a SELECT by email for every single contact. Without an index, MySQL has to do a full scan of the table to find that email. And as the table grows, that becomes more expensive. So this time, the implementation doesn't change at all.

We only add:

<?php

$table->unique('email');

We just added a unique index to the email column. This way, we make sure that MySQL doesn't have to do a full scan of the table and maybe we can gain something from there. But indexes come with a cost. They take additional disk space and inserts become slightly more expensive because MySQL also has to maintain the index.

How much does this cost us? With 5 million contacts in the table, the unique email index takes around 380 MB of disk space.

Index	Size
PRIMARY	446 MB
contacts_email_unique	379.98 MB

Let's find out if the cost is actually worth it.

After 3 runs of the same importer, these are the results:

Contacts	Total time (seconds)	Rows/s	DB Statements	CPU	Memory	Result
1k	3.21	316.07	2,000	54.41%	26 MB	✅
5k	15.29	329.22	10,000	59.22%	28 MB	✅
10k	28.02	357.07	20,000	59.05%	28 MB	✅
50k	148.99	336.16	100,000	59.01%	28 MB	✅
100k	284.95	351.09	200,000	58.83%	28 MB	✅
250k	☠️ >10 min	—	—	—	—	Timed out with 82% processed

The charts below visualize the total time and throughput for different import sizes.

We were able to import 100k contacts successfully. Without the index, we could only import 10k. That is a great improvement for a little change. Also the throughput has become much more stable, but it is still not enough to import our 5M contacts. What can we do next? Let's look at what the importer is actually doing.
We are constantly going PHP → MySQL → PHP → MySQL. Could reducing those trips help? Let's find out.

Case 3: Just bulk it up

We are doing too many trips to the database. We still need to check each contact to know whether it already exists, but maybe we don't need to write them one by one. What if we collect the new contacts and insert them in batches instead?

Here is the new approach:

<?php

$handle = fopen($scenario['csv'], 'r');

$headers = fgetcsv($handle);


$bulk_inserts = array();
$chunk_size = 500;

while(($row = fgetcsv($handle)) !== false) {
    $data = array_combine($headers, $row);

    $name = $data['name'];
    $email = $data['email'];
    $phone = $data['phone'];

    $contact_exists = Contact::where('email', $email)->first();

    if(!$contact_exists){
        $bulk_inserts[] = array(
            'name' => $name,
            'email' => $email,
            'phone' => $phone
        );                    
    }else{
        $contact_exists->update([
            'name' => $name,
            'email' => $email,
            'phone' => $phone,
        ]);
    }

    if(count($bulk_inserts) >= $chunk_size){
        Contact::insert($bulk_inserts);
        $bulk_inserts = array();
    }

}

if(count($bulk_inserts) > 0){
    Contact::insert($bulk_inserts);
    $bulk_inserts = array();
}

fclose($handle);

We're still doing basically the same thing:

• Check if the contact exists.
• If it exists, update it.
• If it doesn't exist, add it to the current batch.
• When the batch is full, insert all new contacts together.

After 3 runs of the new batch insert approach, these are the results:

Contacts	Total time (seconds)	Rows/s	DB Statements	CPU	Memory	Result
1k	0.92	1,085.15	1,102	78.93%	28 MB	✅
5k	4.67	1,072.91	5,509	78.92%	28 MB	✅
10k	8.82	1,139.15	11,018	78.92%	28 MB	✅
50k	44.38	1,127.38	55,090	81.06%	28 MB	✅
100k	92.52	1,081.32	110,180	81.38%	28 MB	✅
250k	229.50	1,089.48	275,450	81.54%	28 MB	✅
500k	447.76	1,117.05	550,900	81.23%	28 MB	✅
1M	☠️ >10 min	—	—	—	—	Timed out with 68% processed

And again, a visual representation of the performance:

We were able to import 500k! Throughput and total time improved significantly. CPU usage also increased, but that's fine. I'm paying for that CPU. Having a CPU at 3% all the time is wasted potential.
But this improvement is not enough. We need to do something else in order to get our 5M contacts imported.
We already reduced the number of INSERT statements, but there are still a lot of SELECT statements being executed.

Case 4: Let the database handle it

Looking at the database statements from Case 3, the next problem becomes quite obvious. For 500k contacts, we have:

SELECT	INSERT	UPDATE	Total
500,000	900	50,000	550,900

We already reduced the number of INSERT statements, but the SELECT statements are still a bottleneck.
How can we reduce the number of SELECT statements? When I discovered upserts years ago, I remember thinking they were basically magic. Just do an upsert and all your problems disappear.

Here is the new implementation:

<?php

$handle = fopen($scenario['csv'], 'r');

$headers = fgetcsv($handle);

$bulk_inserts = array();
$chunk_size = 500;

while(($row = fgetcsv($handle)) !== false) {
    $data = array_combine($headers, $row);

    $name = $data['name'];
    $email = $data['email'];
    $phone = $data['phone'];

    $bulk_inserts[] = array(
        'name' => $name,
        'email' => $email,
        'phone' => $phone
    );

    if(count($bulk_inserts) >= $chunk_size){

        Contact::upsert(
            $bulk_inserts,
            array('email'),
            array('name', 'phone')
        );

        $bulk_inserts = array();
    }

}

if(count($bulk_inserts) > 0){
    Contact::upsert(
        $bulk_inserts,
        array('email'),
        array('name', 'phone')
    );
    $bulk_inserts = array();
}

fclose($handle);

We're basically telling the database: Insert these contacts. If you find an existing row with the same email, update it instead. This changes the number of database statements dramatically. For 500k contacts, we go from 550,900 statements in Case 3 to just 1,000 upserts. That's a 99.82% reduction in database statements.

After 3 runs using the upsert approach, these are the results:

Contacts	Total time (seconds)	Rows/s	DB Statements	CPU	Memory	Result
1k	0.13	7,539.17	2	79.21%	28 MB	✅
5k	0.59	8,339.69	10	83.43%	28 MB	✅
10k	1.38	7,469.21	20	85.41%	28 MB	✅
50k	5.09	9,826.21	100	93.73%	28 MB	✅
100k	10.25	9,794.18	200	93.98%	28 MB	✅
250k	28.68	8,766.65	500	94.51%	28 MB	✅
500k	58.14	8,601.68	1,000	94.47%	28 MB	✅
1M	114.81	8,714.24	2,000	94.56%	28 MB	✅
5M	546.65	9,150.7	10,000	94.6%	28 MB	Only 2 of 3 runs completed successfully

And again, a visual representation of the performance:

We were able to import those 5M contacts, but... only 2/3 times.
Total time is within the limit, so I decided to do an additional 10 runs to see how it behaves.

Runs	Completed under 10 min	Timed out	Timeout progress
10	5	5	97.8% processed on average

So, our little server can process 5 million contacts, but not reliably within the 10-minute limit.

Case 5: And then, the design came

We have a pretty impressive importer for such a small server. But the design just arrived:

Well.

With the Case 4 implementation, I don't have those numbers anymore. Laravel's bulk upsert doesn't give me the created/updated breakdown I need for that message and going back to one of the previous implementations isn't an option either.

So now the requirements are:

• Import as many contacts as possible, ideally 5 million.
• Know exactly how many contacts were created and updated.

What if we mix Case 3 with Case 4? We can check which emails already exist once per chunk, bulk insert the new contacts and use an upsert only for the existing ones. Let's give it a try.

The new implementation:

<?php

// In this experiment, "updated" means that the contact already existed before the import 
// and went through the update path. I'm not checking whether the name or phone actually changed.

$handle = fopen($scenario['csv'], 'r');

$headers = fgetcsv($handle);

$chunk_size = 500;
$contacts_to_check = array();

$created = 0;
$updated = 0;

while(($row = fgetcsv($handle)) !== false) {
    $data = array_combine($headers, $row);

    $name = $data['name'];
    $email = $data['email'];
    $phone = $data['phone'];

    $contacts_to_check[$email] = array(
        'name' => $name,
        'email' => $email,
        'phone' => $phone
    );
    
    if(count($contacts_to_check) >= $chunk_size){

        $existing_emails = Contact::whereIn('email', array_keys($contacts_to_check))->pluck('email')->all();

        $existing_emails = array_flip($existing_emails);

        $to_insert = array();
        $to_update = array();

        foreach($contacts_to_check as $email => $contact){
            if(isset($existing_emails[$email])){
                $to_update[] = $contact;
            }else{
                $to_insert[] = $contact;
            }
        }


        Contact::insert($to_insert);
        $created += count($to_insert);

        Contact::upsert(
            $to_update,
            array('email'),
            array('name', 'phone')
        );
        $updated += count($to_update);

        $contacts_to_check = array();

    }

}

if(count($contacts_to_check) > 0){
    $existing_emails = Contact::whereIn('email', array_keys($contacts_to_check))->pluck('email')->all();
    $existing_emails = array_flip($existing_emails);

    $to_insert = array();
    $to_update = array();

    foreach($contacts_to_check as $email => $contact){
        if(isset($existing_emails[$email])){
            $to_update[] = $contact;
        }else{
            $to_insert[] = $contact;
        }
    }

    Contact::insert($to_insert);
    $created += count($to_insert);

    Contact::upsert(
        $to_update,
        array('email'),
        array('name', 'phone')
    );
    $updated += count($to_update);

}

fclose($handle);

With this implementation, we are increasing database operations. For 5M contacts, Case 4 needed 10,000 statements. Now we need 30,000: 10,000 SELECT, 10,000 INSERT and 10,000 UPSERT statements.

After 3 runs, let's see the results:

Contacts	Total time (seconds)	Rows/s	DB Statements	CPU	Memory	Result
1k	0.13	7,612.62	6	79.53%	28 MB	✅
5k	0.69	7,282.23	30	82.83%	28 MB	✅
10k	1.38	7,602.09	60	84.05%	28 MB	✅
50k	6.10	8,232.78	300	90.26%	28 MB	✅
100k	12.53	7,983.28	600	90.73%	28 MB	✅
250k	30.19	8,282.07	1,500	91.19%	28 MB	✅
500k	61.79	8,124.60	3,000	92.21%	28 MB	✅
1M	125.85	7,948.68	6,000	91.99%	28 MB	✅
5M	☠️ >10 min	—	—	—	—	Timed out with 91% processed

Here are the visual representations of the total time and throughput for Case 5.

That was close. I decided to do an additional 10 runs to see how it behaves.

Runs	Completed under 10 min	Timed out	Timeout progress
10	0	10	89% processed on average

Well, at least now I know exactly how many contacts were created and updated.

Case 6: What if we upgrade the server?

Maybe we can just pay more and that's it. Meet the upgraded server.

	Original server	Upgraded server
Price	$7 / month	$21 / month
CPU	1 AMD vCPU	2 AMD vCPU
RAM	1 GB	2 GB
Disk	25 GB NVMe SSD	25 GB NVMe SSD

Instead of comparing every benchmark again, I went back to the point where each implementation hit the 10-minute cutoff on the $7 server. No code changes. Same datasets. Same database. And a 3x cost.

The larger server improved some of the import times, but only slightly. We are paying 3x more for the server and we didn't see anything close to a proportional improvement.

I was hoping that, in this case, pay to win would actually work.

What if I change something else?

We didn't solve the problem by upgrading the server. Before changing anything else, I wanted to test a different variable: the chunk size. We were working with a fixed chunk size of 500 but we didn't know if that was optimal. So I moved back to the $7 server and tried three different chunk sizes with the same V5 implementation:

• 2,000 contacts per chunk
• 5,000 contacts per chunk
• 10,000 contacts per chunk

Chunk size also comes with a price: larger chunks mean fewer database statements but more memory usage and potentially longer processing times per chunk.

The dataset is the same 5 million contact import with 10% existing contacts and Case 5 code. The only variable changing between these runs is the chunk size.

A chunk size of 5,000 was enough to get the import below the 10-minute limit on the original $7 server. But... how about 10 runs? We did it with Case 4 and Case 5. It didn't fail in the first 3, but I wanted to see if it would remain consistent over 10 runs, because the average time was near the limit.

These are the results for a chunk size of 5,000:

Runs	Completed under 10 min	Timed out	Timeout progress
10	7	3	91.67% processed on average

And the average result of the 7 successful runs:

Contacts	Total time (seconds)	Rows/s	DB Statements	CPU	Memory	Result
5M	557.89	8,963.9	3,000	98.35%	36 MB	OK

Well, 5,000 looked promising. We went from 0/10 successful runs with a chunk size of 500 to 7/10 with 5,000. Memory usage increased to 36 MB, which is still pretty small for a server with only 1 GB of RAM.

What about transactions?

At the beginning of the experiment, I mentioned that each statement was running using MySQL's default autocommit behavior and that the importer wasn't using explicit database transactions.

With a chunk size of 5,000, each chunk executes one SELECT, one INSERT and one UPSERT. The number of database statements is already pretty small, but the writes are still being committed separately.

What happens if we wrap the whole chunk in a transaction instead?

I'm going back to the same $7 server, the same 5 million contacts, the same 10% of existing contacts and the same chunk size of 5,000. The only thing changing is that each chunk now runs inside a transaction.

<?php

if(count($contacts_to_check) >= $chunk_size){

    DB::transaction(function () use (&$contacts_to_check, &$created, &$updated) {

        $existing_emails = Contact::whereIn(
            'email',
            array_keys($contacts_to_check)
        )->pluck('email')->all();

        $existing_emails = array_flip($existing_emails);

        $to_insert = array();
        $to_update = array();

        foreach($contacts_to_check as $email => $contact){
            if(isset($existing_emails[$email])){
                $to_update[] = $contact;
            }else{
                $to_insert[] = $contact;
            }
        }

        Contact::insert($to_insert);
        $created += count($to_insert);

        Contact::upsert(
            $to_update,
            array('email'),
            array('name', 'phone')
        );

        $updated += count($to_update);

        $contacts_to_check = array();
    });
}

The importer still executes the same 3,000 database statements for the full 5 million contact import: 1,000 SELECT, 1,000 INSERT and 1,000 UPSERT. I'm not counting transaction control statements such as BEGIN and COMMIT in that number, just like the previous benchmarks only counted the importer operations.

The difference is that the writes for each chunk are now committed together instead of independently.

Runs	Completed under 10 min	Timed out	Timeout progress
3	0	3	☠️ 91.3% processed on average

Well, that didn't help. None of the three runs completed within the 10-minute limit.

Transactions do give us something useful even if they don't make the import faster: atomicity at the chunk level. If something fails while writing a chunk, the whole chunk can be rolled back instead of leaving only part of it written.

But that also comes with a tradeoff. The transaction stays open while the chunk is being processed and written, which can matter more in a real application where other processes may be writing to the same data at the same time.

For this experiment, though, the question is simpler: does it make the 5 million contact import reliably fit inside our 10-minute limit? No.

Conclusion

So, what would I actually use?

As usual, it depends on the requirements.

Case 1 is not recommended at all. Without an index on the email column, performance degrades too quickly as the number of contacts grows.

Case 2 is perfectly fine for small imports. Adding an index makes a huge difference while keeping the implementation simple and easy to understand. On this server, 100,000 contacts took around 4.7 minutes.

Case 3 is a good middle ground for larger files. Batching the inserts gives us a big performance improvement while still allowing us to keep track of created and updated contacts. It processed 500,000 contacts in around 7.5 minutes.

Case 4 was the fastest approach I tested. It processed 1 million contacts in around 2 minutes, but there's a trade-off: once everything becomes an upsert, I lose the information about which contacts were created and which already existed. If I didn't need that information, this would probably be my choice.

Case 5 gives me that information back, but at a performance cost. In this experiment, that was an actual product requirement, so that's the trade-off I chose.

With Case 5 and a chunk size of 5,000, 7 out of 10 runs completed in under 10 minutes. The successful runs took 557.89 seconds on average, or around 9 minutes and 18 seconds.

Case 6 showed that upgrading the server isn't necessarily the answer. Moving from the $7 server to the $21 server didn't move the boundary for the implementation I actually needed, so in this case, paying for more hardware wasn't worth it.

And what about the original question? Can a $7 server import 5 million contacts in under 10 minutes?

Yes. But not reliably with the implementation I actually need.

By the time I finished all these benchmarks, this little server had processed over 250 million contacts.

We managed to push the synchronous importer surprisingly far. But after spending all this time trying to make 5 million contacts fit inside a 10-minute request, there's another question I probably should have asked earlier: Should this be synchronous at all?

I don't think so. Maybe I should do another experiment.

No servers were harmed during this experiment.*

*One server did run out of disk space, but we don't talk about that.