Software Engineer, Unit test Enthusiast, Indie Game Developer AND
currently working as a Rails Developer at the awesome 6Wunderkinder company

"Applications are never perfectly designed. Every choice has a prize. A good designer understands this tension and minimizes costs by making informed tradeoffs between the needs of the present and the possibilities of the future."

- Practical Object-Oriented Design in Ruby, Sandi Metz

"You were not there for The Beginning. You will not be there for The End. Your knowledge of what is going on can only be superficial and relative."

- William S. Burroughs, Naked Lunch

Text

There are some quite useful commands I had to collect from many different sources which can come in handy when working with SQL databases like MySQL and Postgres.
 

Postgres

Useful SQL statements

Show the maximum number of possible connections: SELECT name, setting FROM pg_settings WHERE name = ‘max_connections’;

Show the active connections: SELECT * from pg_stat_activity;

Show the number of active connections: SELECT COUNT(*) from pg_stat_activity;

Log in to database via terminal (with password): psql -h HOST -p 5432 -d DATABASE_NAME -U USER_NAME -W
 

Heroku Postgres App

The Mac OS X Postgres app from Heroku saves you a lot of hassle. Just download the app and drop it into your Applications folder and you are ready to go -> http://postgresapp.com/

When using this app, the postgresql.conf can be found in ~/Library/Application\ Support/Postgres/var/postgresql.conf
 

Postgres - Bumping up the maximum number of connections on *nix systems

The value for the maximum number of connections can be found in the aforementioned postgresql.conf file. It’s called “max_connections”. If you want to bump it up, you must also consider increasing the “shared_buffers” value. I currently have 200MB of shared buffers and 70 maximum allowed connections. You will have to restart the Postgres app then. If it refuses to start, it’s because your system’s kernel values for shared memory is not high enough. In order to adjust that, use the following commands in your terminal:

sudo sysctl -w kern.sysv.shmall=1073741824

sudo sysctl -w kern.sysv.shmmax=1073741824

This will bump up the shared memory to a maximum of 1024MB. SHMALL refers to the total amount of shared memory pages that can be used system wide. If you want to dig deeper, then read this: http://www.puschitz.com/TuningLinuxForOracle.shtml#SettingSharedMemory
 

Use Postgres as an in-memory database

The following changes to your Postgres config are especially useful for local development. All those settings are related to the WAL (Write-Ahead-Log). Write-Ahead-Logging consists of a couple of techniques that provide atomicity and durability which are two of the ACID properties.

Go back to your postgresql.conf and change those values:

fsync=off
full_page_writes=off
synchronous_commit=off
bgwriter_lru_maxpages=0

fsync usually tries to make sure that updates are written to disk. This enables to recover to a consistent state after a system/hardware crash. ATTENTION: If you turn it off it can result in unrecoverable data corruption!

full_page_writes will write the entire content of each disk page to the WAL (Write-Ahead-Log) during the first modification of that page after a checkpoint. It’s needed because a page write that is in process during an operating system crash might be only partially completed. AGAIN: It might speed up the database to turn this off, but if you do, it could lead to unrecoverable data corruption after a system/hardware crash!

synchronous_commit specifies whether a transaction commit will wait for WAL records to be written to disk before the command returns a “success” indication to the client. It’s not too critical if you turn this off and the system crashes. It would just result in some committed transactions to be lost.

bgwriter_lru_maxpages is related to the background writer. This is a separate server process which issues writes of dirty (which means new or modified) shared buffers. This value adjusts how many buffers will be written by the aforementioned background writer. Setting it to zero will disable it completely.

Have a look at this article, if you want to populate your database with a large amount of data: http://www.postgresql.org/docs/9.2/static/populate.html
 

Mysql

Useful SQL statements

Show the maximum number of possible connections: SHOW variables LIKE ‘%conn%’;

Show the active connections: SHOW process list;

Show the number of active connections: SELECT COUNT(id) FROM information_schema.processlist;

Log in to database via terminal (with password): mysql -u USER_NAME -p -h HOST


Sources

 

"Do you know what punishments I’ve endured for my crimes, my sins? None. I am proof of the absurdity of men’s most treasured abstractions. A just universe wouldn’t tolerate my existence."

- Brent Weeks, The Way of Shadows

"The less government we have the better-the fewer laws and the less confided power. The antidote to this abuse of formal government is the influence of private character, the growth of the individual."

- Ralph Waldo Emerson

"Whatever the future of Valve turns out like, one thing is for certain – and it so happens that it constitutes the reason why I am personally excited to be part of Valve: The current system of corporate governance is bunk. Capitalist corporations are on the way to certain extinction. Replete with hierarchies that are exceedingly wasteful of human talent and energies, intertwined with toxic finance, co-dependent with political structures that are losing democratic legitimacy fast, a form of post-capitalist, decentralised corporation will, sooner or later, emerge."

How to build a GitHub (by Zach Holman) - GitHub is an amazing company. Their philosophies are truly phenomenal.

"Trees? Nested objects and parent child relationships? SQL just laughs in your face and gives you a massive flat table saying, “You figure it out bro."

- Zed Shaw, http://sql.learncodethehardway.org/

The Naive Optimist: Refocusing on the people I love

ryanleecarson:

I don’t know what caused it but I recently realized that I spend way too much time ‘connecting’ with people that I will never meet, instead of people that I truly love.

These are the people that will matter most to me when I’m about to die:

  1. Gill (my Wife) and her family
  2. My two boys
  3. My…

I just love his blog posts. Being present wherever you are physically. That’s a good goal.

Source: ryanleecarson

"Procedural code gets information then makes decisions. Object-oriented code tells objects to do things."

- Alec Sharp