Backstory:
Another week, another evening at the Pub with some friends and colleges. Somehow or the other we got on the topic of database insert performance and how long it would take to reach the 32bit max integer. That being 2.14somethingsomethingsomething billion. I wagered that the the max signed int could be a reached relatively quickly, my college on the other hand said ‘no no no; it would take hours. Days even’. And so, a wager was born.
The requirements:
PHP + PDO + a SQL database; default configurations. No editing php.ini to allow higher memory usage, no disabling *SQL disk_flush in my.cnf, etc. Raw install, logic, go for the gold.
The process:
On the local development machines we limit the container service manager to limit hardware usage to 7 of the 2.4Ghz CPUs and 15Gb of memory. For the disk we run 256GB SSD, desktop models; nothing fancy.
On that hardware I setup a PHP 7:latest service and a MariaDB:latest service; then linked them. From there it was a matter of connection credentials and increasing the batch insert count until it was close, but not over, the default memory usage per thread. Then how to start up multiple threads, easy enough, bash helped out there. So using bash I spun up 10 threads and let the process run for 1 to 2 minutes.
Getting the max value after the given time frame I was able to extrapolated out how long it would take to fill the 2.14 billion rows.
The Result:
At current the fastest time requirement would take 1.526 hrs to go from 0 to 2.14 billion row inserts. I know we can get faster but ran out of time today.
The Source:
If you are interested in the code / stats /etc the repo is here https://github.com/davidjeddy/full-up-the-db. Feel free to fork / PR the repo if you can get a faster speed. It would be really awesome to show 32 bit max int can be reached in 5 minutes or less. (Remember, no editing of configurations.)
