Many Ways to Optimize an OLTP Database Running PostgreSQL

Studying the business requirements carefully can allow you to redesign the database and the SQL queries to make things happen more efficiently. You could rewrite stored procedures to involve less computational complexity. We cannot provide every possible way of enhancing the performance of your PostgreSQL database given the multitude of idiosyncrasies that could exist. We hope that some of these ideas give you some ideas of where to begin.

1. Place important indexes of heavily used tables on solid state disks.

 » Read more..

How Big is a Page in SQL Databases?

Question
Pages are sizes of a database table read from disk and placed into memory (either virtual memory or into RAM). Is a page always 8 KB in a SQL database?

Answer
No. But they are often 8 KB in size.

  • PostgreSQL has pages that are 8 KB in size (according to this page).
  • MySQL pages vary in size from 8 KB to 64 KB (according to this page).

 » Read more..

What is a SQL Statement That Will Work for Any MySQL Database?

Problem scenario
You want to test connectivity to a database. But you do not know anything about the database beyond its connection information. What SQL statement is universal and guaranteed to work?

Solution
show databases;

 » Read more..

How Do You Use the AWS CLI to List Aurora Databases?

Problem scenario
You have an Amazon Aurora database in you AWS account. You want to see the status of this database with the AWS CLI. How do you list information about your Aurora databases?

Solution
Prerequisites

You must have the AWS CLI installed and configured with a user that has the ability to view Aurora databases (e.g., an administrator of your AWS account).

 » Read more..

How Do You Find what VPC an Aurora Database Is In?

Problem scenario
You want to be sure that a given Aurora database is in a specific VPC. How do you determine what VPC it is in?

Solution
Prerequisite
You must have the AWS CLI installed and configured with a user that has the ability to view Aurora databases (e.g., an administrator of your AWS account). If you need assistance,

 » Read more..

How Do You Troubleshoot the Java Program Message “java.sql.SQLException: No database selected”

Problem scenario
Your Java program returns this message: “java.sql.SQLException: No database selected”
What should you do?

Possible Solution #1
Your SQL statement was appropriate for a database but not for the circumstance of connecting to a SQL instance. Try show databases; instead of the SQL you were running.

Possible Solution #2
Did you connect to the SQL instance you thought you connected to?

 » Read more..

How Do You Run a Java Program to Run SQL Commands Against a MySQL Database?

Problem scenario
You have a MySQL database. Rather than use a SQL front-end application, you want to write a Java program that will run a SQL statement against the database. What should you do?

Solution

1. Install the Java compiler if it is not already installed. If you need assistance, see this posting.

2. Run this command:
curl -Lk https://www.javatpoint.com/src/jdbc/mysql-connector.jar /tmp/mysql-connector.jar

3.

 » Read more..

How Do You Connect to an Aurora MySQL Database from a Linux Server with a MySQL Command?

Problem scenario
You are using an Aurora or RDS MySQL database. You have a Linux server that you want to run MySQL commands from (rather than install a SQL front-end). What should you do?

Solution

1. Install a MySQL client. If you need assistance with this, see this posting.

2. Make sure the Security Group governing the Aurora or RDS region allows for incoming connections from one of the following:
a) if you are using an EC-2 server in the same region,

 » Read more..

How Do You Troubleshoot the Java Program Message “com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown database foobar”?

Problem scenario
Your Java program returns this message: “com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown database foobar”

What should you do?

Possible solution #1
Are you using Amazon Aurora? AWS has a MySQL PaaS offering. You may have a section of the Java code that looks like this:

Connection con=DriverManager.getConnection(“jdbc:mysql://foobar-us-west-2b.abcdefghijk.us-west-2.rds.amazonaws.com:3306/foobar”)

Remove the last “foobar” from the end. The connection string should look like this for Aurora databases:

jdbc:mysql://foobar-us-west-2b.abcdefghijk.us-west-2.rds.amazonaws.com:3306/foobar

Recompile the program.

 » Read more..

How Do You Get the mysql Command to Work from an Ubuntu Linux Command Line?

Problem scenario
The mysql command does not work from Ubuntu. You try to run a “mysql” command but you get “command not found.” What do you do to install a mysql CLI utility?

Solution
Run one of the following:

sudo apt -y install mysql-client-core-5.7
sudo apt -y install mariadb-client-core-10.1

 » Read more..