How to use pgbench for PostgreSQL benchmark testing

Pgbench is used for Postgresql benchmark testing. Pgbench iterates through a bunch of SQL statements . There are various configurables such as sessions (single or multiple) and clients. The pgbench approach is based on TPC-B . The TPC-B benchmark focuses on benchmarking as opposed to OLTP type testing. The measurement is based on transactions per second. In other words - you'll define how many transactions to execute per client and this will result in a rate per second. There is a default test framework – which involves 5 SELECT , UPDATE, INSERT commands per transaction- there are options to customise... Read more →

How to clear Postgresql database cache

Question: In SQL Server there are two commands which clear down the data and query caches - DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS. Are there similar commands in PostgreSQL hosted on Linux? I want to clear down datasets from memory cache to allow query performance tuning. Answer: If you'd like to view what is in the buffer cache - pg_buffercache will present to you what is in the cache. Of course , pg_buffercache needs to be installed. It's a complicated data set and requires some knowledge to interpret. Unfortunately - there isn't a straightforward way of clearing down the database cache... Read more →

How to calculate the PostgreSQL database size

Question : How can I calculate the size of a PostgreSQL database using psql?I want to use the command line and execute a sql statement for various checks? Answer: An easy way to derive the Postgresql database size is to use the pg_database_size() function. The pg_database_size() reads the database name and returns the database size in bytes. For example : SELECT pg_database_size('my_db_name') This returns in bytes - which unless you enjoy calculating byte sizes - there is another function you can use which will make these numbers human readable - pg_size_pretty() Combining these two functions will return the database size... Read more →

ODBC Data Source Administrator Connection Type for IBM i Access for Windows

Question: I'm setting up a User DSN on a ODBC Data Source Administrator - and configuring the IBM i Access for Windows ODBC Setup. The default is Use ODBC access mode , but should we use Limit to Read Only (Select Statements) . btw - the intention for this DSN config is to read only. Which option should I choose? Answer: Before we get into connection types - I want to establish the basic principle - which is that the main security focus should be on securing the data rather than access to the data . Of course , it... Read more →

HCI and Database servers

There are constant infrastructure technology changes. There are regular infrastructure changes - for various reasons , such as hardware refresh , hardware not supported by vendor any longer , data centre simplification , changing data centres , on-prem versus off prem. What remains permanent is the database servers that remain on the OS. So it's incumbent upon the DBA to approach the problem with a risk analysis mindset. One of the first is start to understand the underlying infrastructure. Hyper Converged Infrastructure (HCI) utilizes storage residing in servers and distributing the storage treating it like a storage array. This consolidation... Read more →

Which one to use - Apache Kafka versus RabbitMQ

Best case scenario (use case) for Apache Kafka 1) Requirement for partitioned order at least once write. Requirement for A to B streaming with high throughput. 2) Durable message store . Why would this be important? Consumers can get a replay of the event stream - whereas limited in message brokers - where once message is delivered , removed from queue 3) If a solution requires data stream processing. 4) Event sourcing required. Event sourcing persists the state of a business entity e.g maintaining a chain of event at different states in a sequential order for a Customer 5) More... Read more →

How to fix mongo odbc 1.1 access denied for user in Challenge and Response

When supporting developers during setting up BI Connector and the ODBC System DSN for connection to a MongoDB , a common problem is the incorrect application of the username format , leading to a "Access is Denied" error message. What should the correct username format be for the Challenge and Response method? Step 1 is setting up the BI Connector. Typically the developer initiated the command line utility mongosqld . The mongosqld command line utility accepts incoming requests from a SQL client (or ODBC DSN setup used by the SQL Client) and acts as a proxy for the incoming requests.I... Read more →

How to find Kafka port number and Zookeeper port number

Question: I need to supply the Kafka port number and Zookeeper port number for firewall exceptions. Where can I find the port number details for both Kafka and Zookeeper ? Answer : To find the Kafka port number - locate the Kafka server.properties file. Typically the server.properties file will have the information required. The file is normally found on this location . By default the port number is 9092. $(confluent_home)/etc/kafka/server.properties To find the Zookeeper port number - locate the Zookeeper properties file. Normally found on $(confluent_home)/etc/kafka/zookeeper.properties. Once you've opened the file , look for ClientPort=2181 For Zookeeper - 2181 =... Read more →

How to use Powershell export-csv without a header

Question: I'm trying to use powershell cmdlet export-csv to output a csv file - but without the header information from a sql server recordset i.e first row I'm trying to implement this powershell code snippet. $outputfile="\\myoutputfile.csv" $dt = new-object "System.Data.DataTable" --add some data into the data table dt | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray | export-csv -path $outputfile -NoTypeInformation Answer: It is possible to get the no header on an export-csv - but it does require a few workarounds - which seem excessive for what you're attempting to achieve I prefer to use the powershell cmdlet... Read more →

How to use curl for a RabbitMQ connection

Question : How can I use curl to test a remote connection to RabbitMQ? The goal is to use Apache Kafka as the end-goal for data streaming from RabbitMQ , but I just want to use a simple curl command line method to establish whether the credentials , host & port are valid. Note this is a remote connection Answer: If you have the RabbitMQ queue name and want to return the messages in a queue this method will get the messages. Before attempting the connection ensure the host , username,password, port are all valid. Including making sure is open... Read more →