Out of the box performance: PHP + PDO + MariaDB

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.)

How I got linux SSH to work on windows 10…

So one of the biggest issues I personally have had with windows is the in-ability to emulate a decent Linux terminal. Sure I could dual boot, run a VM or any Os virtualization. But for me, it all seemed very…detached. Well, herein is a solution I have found to be very congruent to my goal:

Step 1 Follow the directions

Provided by http://www.windowscentral.com/how-install-bash-shell-command-line-windows-10 to get bash on Windows 10 (they have images as well).

However if you like me and just want the TL;DR:

  • Settings -> Update & Security -> Enable Developer Mode, Click Yes
  • Control Panel -> Program Features -> Turn Windows Feature ON -> Enable ‘Windows Subsystem for Linux’
  • Restart

After restart completes:

  • open a CMD prompt
  • run the command bash
  • provide a username for the linux system -> press enter
  • You should now be back on the CMD promtp -> close window
  • Open a new cmd -> type bash

Gaze in amazement as you now have linux terminal running on windows without the use of a VM.

Now that we are 1/2 way to our goal of SSHing into a remote linux machine, lets move onto the magic part!

Step 2: The fancy footwork

These instruction will not cover how to make a SSH keypair (that is covered at length elsewhere). For this example I had to log into an AWS EC2 instance.  Ensure an instance is running, we have access to the public IP, and a copy of the *.pem key saves on the windows machine somewhere.

 

  • Copy the AWS *.pem file from windows into ~/.ssh:  cp /mnt/c/me/Documents/pem/example.pem ~/.ssh/example.pem
  • chmod 0600 ~/.ssh/example.pem
  • ls -la`-r-x------ 1 root root 1692 Feb  8 21:09 example.pem to ensure the permission did infact change
  • ec2-user@12.34.56.78 -i ~/.ssh/example.pem

If all went well you should be greeted with a welcome message from the remote machine.

Conclusion

Overall not that complicated once the steps are sorted out, but ugg, sorting out all the step on two different platforms. Remindes me of the time we go *AMP running on WindowsXP alongside IIS/.NET/SQL back in my college days. #donotwant