• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
  • Home
  • Create a VM ($25 Credit)
  • Buy a Domain
  • 1 Month free Back Blaze Backup
  • Other Deals
    • Domain Email
    • Nixstats Server Monitoring
    • ewww.io Auto WordPress Image Resizing and Acceleration
  • About
  • Links

IoT, Code, Security, Server Stuff etc

Views are my own and not my employer's.

Personal Development Blog...

Coding for fun since 1996, Learn by doing and sharing.

Buy a domain name, then create your own server (get $25 free credit)

View all of my posts.

  • Cloud
    • I moved my domain to UpCloud (on the other side of the world) from Vultr (Sydney) and could not be happier with the performance.
    • How to buy a new domain and SSL cert from NameCheap, a Server from Digital Ocean and configure it.
    • Setting up a Vultr VM and configuring it
    • All Cloud Articles
  • Dev
    • I moved my domain to UpCloud (on the other side of the world) from Vultr (Sydney) and could not be happier with the performance.
    • How to setup pooled MySQL connections in Node JS that don’t disconnect
    • NodeJS code to handle App logins via API (using MySQL connection pools (1000 connections) and query parameters)
    • Infographic: So you have an idea for an app
    • All Development Articles
  • MySQL
    • Using the free Adminer GUI for MySQL on your website
    • All MySQL Articles
  • Perf
    • PHP 7 code to send object oriented sanitised input data via bound parameters to a MYSQL database
    • I moved my domain to UpCloud (on the other side of the world) from Vultr (Sydney) and could not be happier with the performance.
    • Measuring VM performance (CPU, Disk, Latency, Concurrent Users etc) on Ubuntu and comparing Vultr, Digital Ocean and UpCloud – Part 1 of 4
    • Speeding up WordPress with the ewww.io ExactDN CDN and Image Compression Plugin
    • Setting up a website to use Cloudflare on a VM hosted on Vultr and Namecheap
    • All Performance Articles
  • Sec
    • Using the Qualys FreeScan Scanner to test your website for online vulnerabilities
    • Using OWASP ZAP GUI to scan your Applications for security issues
    • Setting up the Debian Kali Linux distro to perform penetration testing of your systems
    • Enabling TLS 1.3 SSL on a NGINX Website (Ubuntu 16.04 server) that is using Cloudflare
    • PHP implementation to check a password exposure level with Troy Hunt’s pwnedpasswords API
    • Setting strong SSL cryptographic protocols and ciphers on Ubuntu and NGINX
    • Securing Google G Suite email by setting up SPF, DKIM and DMARC with Cloudflare
    • All Security Articles
  • Server
    • I moved my domain to UpCloud (on the other side of the world) from Vultr (Sydney) and could not be happier with the performance.
    • All Server Articles
  • Ubuntu
    • I moved my domain to UpCloud (on the other side of the world) from Vultr (Sydney) and could not be happier with the performance.
    • Useful Linux Terminal Commands
    • All Ubuntu Articles
  • VM
    • I moved my domain to UpCloud (on the other side of the world) from Vultr (Sydney) and could not be happier with the performance.
    • All VM Articles
  • WordPress
    • Speeding up WordPress with the ewww.io ExactDN CDN and Image Compression Plugin
    • Installing and managing WordPress with WP-CLI from the command line on Ubuntu
    • How to backup WordPress on a host that has CPanel
    • Moving WordPress to a new self managed server away from CPanel
    • Moving a CPanel domain with email to a self managed VPS and Gmail
    • All WordPress Articles
  • All

How to setup pooled MySQL connections in Node JS that don’t disconnect

August 29, 2015 by Simon Fearby

Using NodeJS/NGINX to power a custom API or website is quite simple. At some stage you will want to connect NodeJS to a MySQL database.

Update (June 2018): I moved my domain to UpCloud (they are that awesome). Use this link to signup and get $25 free credit. Read the steps I took to move my domain to UpCloud here.

Upcloud Site Speed in GTMetrix

Buy a domain name from Namecheap here.

Domain names for just 88 cents!

Here is a quick sample of the minimum code needed to get a Node Server up with a API target of “/api/database/status”. What better way to know if a database is up than to read a setting from a table to confirm it is up.

var app = require('express')();
var http = require('http').Server(app);
var mysql = require('mysql');
var bodyParser = require("body-parser");

app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());

var db_config = { host : 'localhost', user : 'your_db_username', password : 'your_db_password', database : 'your_databse_name',};
var connection = mysql.createConnection(db_config);

app.get('/api/database/status',function(req,res){
	var retvalSettingValue = "?";
    connection.query('SELECT SettingValue FROM your_status_table WHERE SettingKey =\'DatabaseStatus\'', function(err, rows, fields) {	
    	if (err) {
			var data = { "Time":"", "DatabaseStatus":"" };
			data["Time"] = (new Date()).getTime();
			data["DatabaseStatus"] = "Down";
			res.json(data); 
		} else {
			var dbretval = rows[0].SettingValue;
			if (dbretval == 1 ) {
				var data = { "Time":"", "DatabaseStatus":"" };
				data["Time"] = (new Date()).getTime();
				data["DatabaseStatus"] = "Up";
				res.json(data); 
			} else {
				var data = { "Time":"", "DatabaseStatus":"" };
				data["Time"] = (new Date()).getTime();
				data["DatabaseStatus"] = "Down";
				res.json(data); 
			}
		}
    });

http.listen(3000,function(){
	console.log("Connected & Listen to port 3000 at /api ..");
});

This should return the following JSON data.

{"Time":1440835363892,"DatabaseStatus":"Up"}

The problem for me with the code above was that MySQL kept disconnecting after a few days. Adjusting timeouts, setting up keep alive cron jobs failed to keep the connection up. I then tried introducing the following code solve the disconnected MySQL connection.

function handleDisconnect() {
	console.log('handleDisconnect()');
	connection.destroy();
	connection = mysql.createConnection(db_config);
	connection.connect(function(err) {
	    if(err) {
			console.log(' Error when connecting to db  (DBERR001):', err);
			setTimeout(handleDisconnect, 1000);
	    }
	});

}

I added the following before and connection.query in existing code.

	connection.connect(function(err) {
	if(err) {
		console.log('Connection is asleep (time to wake it up): ', err);
		setTimeout(handleDisconnect, 1000);
		handleDisconnect();
	}
	});

I ended up swapping over to using pooled MySQL connections and this code has been working for days. Pooled connections reserves a number of connections ready for use in the background. You simply ask for a connection and when you are done you release it. The pooled connection closes and refreshed the connection and puts it at the end of the queue.

var app = require('express')();
var http = require('http').Server(app);
var mysql = require('mysql');
var bodyParser = require("body-parser");

app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());

var mysql_pool  = mysql.createPool({
  connectionLimit : 100,
  host            : 'localhost',
  user            : 'your_database_user',
  password        : 'your_database_password',
  database        : 'your_database'
});

app.get('/api/database/status',function(req,res) {
	console.log('API CALL: /api/database/status');
	var retvalSettingValue = "?";
	mysql_pool.getConnection(function(err, connection) {
		if (err) {
			connection.release();
	  		console.log(' Error getting mysql_pool connection: ' + err);
	  		throw err;
	  	}
	    connection.query('SELECT SettingValue FROM your_database_table WHERE SettingKey =\'DatabaseStatus\'', function(err2, rows, fields) {	
	    	if (err2) {
				var data = { "Time":"", "DatabaseStatus":"" };
				data["Time"] = (new Date()).getTime();
				data["DatabaseStatus"] = "Down";
				res.json(data); 
			} else {
				var dbretval = rows[0].SettingValue;
				if (dbretval == 1 ) {
					var data = { "Time":"", "DatabaseStatus":"" };
					data["Time"] = (new Date()).getTime();
					data["DatabaseStatus"] = "Up";
					res.json(data); 
				} else {
					var data = { "Time":"", "DatabaseStatus":"" };
					data["Time"] = (new Date()).getTime();
					data["DatabaseStatus"] = "Down";
					res.json(data); 
				}
			}
			console.log(' mysql_pool.release()');
			connection.release();
	    });
	});
});

http.listen(3000,function(){
	console.log("Connected & Listen to port 3000 at /api ..");
});

Throughput

To test the connections/throughput it’s time to fire up more than 100 connections using siege over 60 seconds. I am using HTTPS connections so his would slow down the throughput a fair bit (but I am sticking with HTTPS for customer security/privacy). Read my guide here on adding a A+ level SSL cert to a digital ocean VM.

#!/bin/bash
free -m

siege -b -c1 -t1M https://yourserver.com/api/database/status
cat /var/log/siege.log

free -m

Here are the results from a siege single client hitting the NodeJS/pooled connections for 60 seconds. I am able to get about 6,150 database reads in 60 seconds with no dropped connections.

[email protected]:/home/scripts# bash _Benchmark.sh n
             total       used       free     shared    buffers     cached
Mem:          2001       1784        217         53        186        980
-/+ buffers/cache:        617       1384
Swap:         4095          0       4095

** SIEGE 3.0.5
** Preparing 1 concurrent users for battle.
The server is now under siege...
Lifting the server siege...      done.

Transactions:                   6150 hits
Availability:                 100.00 %
Elapsed time:                  59.69 secs
Data transferred:               0.26 MB
Response time:                  0.01 secs
Transaction rate:             103.03 trans/sec
Throughput:                     0.00 MB/sec
Concurrency:                    0.97
Successful transactions:        6150
Failed transactions:               0
Longest transaction:            0.05
Shortest transaction:           0.00
 
FILE: /var/log/siege.log
You can disable this annoying message by editing
the .siegerc file in your home directory; change
the directive 'show-logfile' to false.
      Date & Time,  Trans,  Elap Time,  Data Trans,  Resp Time,  Trans Rate,  Throughput,  Concurrent,    OKAY,   Failed
2015-08-29 04:18:35,   6150,      59.69,           0,       0.01,      103.03,        0.00,        0.97,    6150,       0
             total       used       free     shared    buffers     cached


Mem:          2001       1806        195         53        186        981
-/+ buffers/cache:        638       1363
Swap:         4095          0       4095

2 siege clients hitting the server delivers 8,120 database reads with 0 dropped connections.

      Date & Time,  Trans,  Elap Time,  Data Trans,  Resp Time,  Trans Rate,  Throughput,  Concurrent,    OKAY,   Failed
2015-08-29 04:25:13,   8120,      59.83,           0,       0.01,      135.72,        0.00,        1.96,    8120,       0

10 siege clients hitting the server delivers 16,383 database reads with 0 dropped connections.

      Date & Time,  Trans,  Elap Time,  Data Trans,  Resp Time,  Trans Rate,  Throughput,  Concurrent,    OKAY,   Failed
2015-08-29 04:29:50,  16383,      59.51,           0,       0.04,      275.30,        0.00,        9.86,   16383,       0

20 siege clients hitting the server delivers 15440 database reads with 0 dropped connections. This looks like the max the server can handle (257 delivered requests a second (19.7 concurrent connections)).

      Date & Time,  Trans,  Elap Time,  Data Trans,  Resp Time,  Trans Rate,  Throughput,  Concurrent,    OKAY,   Failed
2015-08-29 04:33:06,  15440,      59.93,           0,       0.08,      257.63,        0.00,       19.77,   15440,       0

My SQL, Linux Memory and Cache tweaks

I have tweaked my stock MySQL config a bit (changed values below)

#
# The MySQL database server configuration file.
#
...
[mysqld]
...
key_buffer		= 32M
key_buffer_size = 32M
max_allowed_packet	= 16M
max-connect-errors             = 1000000

#max_connections        = 100
max-connections = 500
thread_stack		= 256K
thread_cache_size       = 8
myisam-recover         = FORCE,BACKUP
#table_cache            = 64
#thread_concurrency     = 10
...
query_cache_limit	= 1M
query_cache_size        = 0
...
expire_logs_days	= 10
max_binlog_size         = 100M
...
# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
thread-cache-size              = 50
open-files-limit               = 65535
# table-definition-cache         = 1024
# table-open-cache               = 2048
..

Some may notice a 20mb memory jump in memory usage after I benchmarked, I have set my ubuntu to use as much memory as possible and not release too soon. I also have a 4GB swap file (that is ready in reserve but not used much).

  • swappiness this control is used to define how aggressively the kernel swaps out anonymous memory relative to pagecache and other caches. Increasing the value increases the amount of swapping. The default value is 60.
  • vfs_cache_pressure this variable controls the tendency of the kernel to reclaim the memory which is used for caching of VFS caches, versus pagecache and swap. Increasing this value increases the rate at which VFS caches are reclaimed.

/etc/sysctl.conf

vm.swappiness = 10
vm.vfs_cache_pressure = 50
vm.min_free_kbytes= 131072

Tips on setting linux cache usage and snappiness here.

More performance tweaks here: https://gist.github.com/dakull/5629740

Donate and make this blog better




Donate and make this blog better




Ask a question or recommend an article
[contact-form-7 id=”30″ title=”Ask a Question”]

Filed Under: Cloud, Development, Linux, MySQL, NodeJS, Scalable Tagged With: MySQL, NodeJS, Pooled, SQL

Advertisement:

Copyright © Fearby.com - Do not copy or duplicate (that means you laptrinhx.com)

Primary Sidebar

Poll

What would you like to see more posts about?
Results

Support this Blog

Create your own server today (support me by using these links

Create your own server on UpCloud here ($25 free credit).

Create your own server on Vultr here.

Create your own server on Digital Ocean here ($10 free credit).

Remember you can install the Runcloud server management dashboard here if you need DevOps help.

Advertisement:

Tags

2FA (9) Advice (17) Analytics (9) App (9) Apple (10) AWS (9) Backup (21) Business (8) CDN (8) Cloud (49) Cloudflare (8) Code (8) Development (26) Digital Ocean (13) DNS (11) Domain (27) Firewall (12) Git (7) Hosting (18) IoT (9) LetsEncrypt (7) Linux (21) Marketing (11) MySQL (24) NGINX (11) NodeJS (11) OS (10) Performance (6) PHP (13) Scalability (12) Scalable (14) Security (45) SEO (7) Server (26) Software (7) SSH (7) ssl (17) Tech Advice (9) Ubuntu (39) Uncategorized (23) UpCloud (12) VM (45) Vultr (24) Website (14) Wordpress (25)

Disclaimer

Terms And Conditions Of Use All content provided on this "www.fearby.com" blog is for informational purposes only. Views are his own and not his employers. The owner of this blog makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. Never make changes to a live site without backing it up first.

Advertisement:

Footer

Popular

  • Backing up your computer automatically with BackBlaze software (no data limit)
  • How to back up an iPhone (including photos and videos) multiple ways
  • Add two factor auth login protection to WordPress with YubiCo hardware YubiKeys and or 2FA Authenticator App
  • Setup two factor authenticator protection at login on Ubuntu or Debian
  • Using the Yubico YubiKey NEO hardware-based two-factor authentication device to improve authentication and logins to OSX and software
  • I moved my domain to UpCloud (on the other side of the world) from Vultr (Sydney) and could not be happier with the performance.
  • Monitor server performance with NixStats and receive alerts by SMS, Push, Email, Telegram etc
  • Speeding up WordPress with the ewww.io ExactDN CDN and Image Compression Plugin
  • Add Google AdWords to your WordPress blog

Security

  • Check the compatibility of your WordPress theme and plugin code with PHP Compatibility Checker
  • Add two factor auth login protection to WordPress with YubiCo hardware YubiKeys and or 2FA Authenticator App
  • Setup two factor authenticator protection at login on Ubuntu or Debian
  • Using the Yubico YubiKey NEO hardware-based two-factor authentication device to improve authentication and logins to OSX and software
  • Setting up DNSSEC on a Namecheap domain hosted on UpCloud using CloudFlare
  • Set up Feature-Policy, Referrer-Policy and Content Security Policy headers in Nginx
  • Securing Google G Suite email by setting up SPF, DKIM and DMARC with Cloudflare
  • Enabling TLS 1.3 SSL on a NGINX Website (Ubuntu 16.04 server) that is using Cloudflare
  • Using the Qualys FreeScan Scanner to test your website for online vulnerabilities
  • Beyond SSL with Content Security Policy, Public Key Pinning etc
  • Upgraded to Wordfence Premium to get real-time login defence, malware scanner and two-factor authentication for WordPress logins
  • Run an Ubuntu VM system audit with Lynis
  • Securing Ubuntu in the cloud
  • No matter what server-provider you are using I strongly recommend you have a hot spare ready on a different provider

Code

  • How to code PHP on your localhost and deploy to the cloud via SFTP with PHPStorm by Jet Brains
  • Useful Java FX Code I use in a project using IntelliJ IDEA and jdk1.8.0_161.jdk
  • No matter what server-provider you are using I strongly recommend you have a hot spare ready on a different provider
  • How to setup PHP FPM on demand child workers in PHP 7.x to increase website traffic
  • Installing Android Studio 3 and creating your first Kotlin Android App
  • PHP 7 code to send object oriented sanitised input data via bound parameters to a MYSQL database
  • How to use Sublime Text editor locally to edit code files on a remote server via SSH
  • Creating your first Java FX app and using the Gluon Scene Builder in the IntelliJ IDEA IDE
  • Deploying nodejs apps in the background and monitoring them with PM2 from keymetrics.io

Tech

  • Backing up your computer automatically with BackBlaze software (no data limit)
  • How to back up an iPhone (including photos and videos) multiple ways
  • US v Huawei: The battle for 5G
  • Check the compatibility of your WordPress theme and plugin code with PHP Compatibility Checker
  • Is OSX Mojave on a 2014 MacBook Pro slower or faster than High Sierra
  • Telstra promised Fibre to the house (FTTP) when I had FTTN and this is what happened..
  • The case of the overheating Mac Book Pro and Occam’s Razor
  • Useful Linux Terminal Commands
  • Useful OSX Terminal Commands
  • Useful Linux Terminal Commands
  • What is the difference between 2D, 3D, 360 Video, AR, AR2D, AR3D, MR, VR and HR?
  • Application scalability on a budget (my journey)
  • Monitor server performance with NixStats and receive alerts by SMS, Push, Email, Telegram etc
  • Why I will never buy a new Apple Laptop until they fix the hardware cooling issues.

Wordpress

  • Replacing Google Analytics with Piwik/Matomo for a locally hosted privacy focused open source analytics solution
  • Setting web push notifications in WordPress with OneSignal
  • Telstra promised Fibre to the house (FTTP) when I had FTTN and this is what happened..
  • Check the compatibility of your WordPress theme and plugin code with PHP Compatibility Checker
  • Add two factor auth login protection to WordPress with YubiCo hardware YubiKeys and or 2FA Authenticator App
  • Monitor server performance with NixStats and receive alerts by SMS, Push, Email, Telegram etc
  • Upgraded to Wordfence Premium to get real-time login defence, malware scanner and two-factor authentication for WordPress logins
  • Wordfence Security Plugin for WordPress
  • Speeding up WordPress with the ewww.io ExactDN CDN and Image Compression Plugin
  • Installing and managing WordPress with WP-CLI from the command line on Ubuntu
  • Moving WordPress to a new self managed server away from CPanel
  • Moving WordPress to a new self managed server away from CPanel

General

  • Backing up your computer automatically with BackBlaze software (no data limit)
  • How to back up an iPhone (including photos and videos) multiple ways
  • US v Huawei: The battle for 5G
  • Using the WinSCP Client on Windows to transfer files to and from a Linux server over SFTP
  • Connecting to a server via SSH with Putty
  • Setting web push notifications in WordPress with OneSignal
  • Infographic: So you have an idea for an app
  • Restoring lost files on a Windows FAT, FAT32, NTFS or Linux EXT, Linux XFS volume with iRecover from diydatarecovery.nl
  • Building faster web apps with google tools and exceed user expectations
  • Why I will never buy a new Apple Laptop until they fix the hardware cooling issues.
  • Telstra promised Fibre to the house (FTTP) when I had FTTN and this is what happened..

Copyright © 2023 · News Pro on Genesis Framework · WordPress · Log in

Some ads on this site use cookies. You can opt-out if of local analytics tracking by scrolling to the bottom of the front page or any article and clicking "You are not opted out. Click here to opt out.". Accept Reject Read More
GDPR, Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT