Thursday, October 24, 2019

LOG4J on Windows Complaining about Console Code Page

I was running the Windows JVM with LOG4J 2.8.1 with SLF4J 2.7.5 on the Windows Subsystem for Linux and encountered an error as follows,

2019-10-24 16:03:19,902 main ERROR Unable to inject fields into builder class 
for plugin type class org.apache.logging.log4j.core.appender.ConsoleAppender, 
element Console. java.nio.charset.UnsupportedCharsetException: cp0

In the Windows Subsystem for Linux terminal, the code page returned was 0, which was the source of the code page cp0.

user@WSL:/mnt/c/Windows/System32$ chcp.com
Active code page: 0
user@WSL:/mnt/c/Windows/System32$

Interestingly, the returned code page is on Windows Command Prompt,

C:\Windows\System32>chcp
Active code page: 437

C:\Windows\System32>

To get rid of the problem, we need to inform Java the encoding scheme, e.g.,

java -Dsun.stdout.encoding=UTF-8 -Dsun.err.encoding=UTF-8 FooClass

where we assume we run the FooClass class.

Tuesday, October 22, 2019

Using tuned to Configure PostgreSQL Database Management System

Tuned is a profile-based system tuning tool for dynamic and static tuning of system settings. I find it very useful even if for configuring system settings. Tuned comes from recent release of Fedora Linux distribution. The following steps are just to make sure that we have the tuned package in the system, and start and enable the tuned service.

sudo dnf install tuned
sudo systemctl start tuned
sudo systemctl enable tuned

Below are a few steps to show how we use tuned to manually adjust system settings (or static-tuning):

  • Listing existing profiles
    
    tuned-adm list
    

  • To activate a profile or to reactivate a profile after you have revised it,
    
    sudo tuned-adm active postgresql
    

    In this example, we have created a profile of our own to tune the system for running PostgreSQL Database Management System.
To create such a profile, follow the steps:
  1. Create a directory at /etc/tuned, e.g., if we wish to have a profile called postgresql, we create the directory postgresql at /etc/tuned,
    
    sudo mkdir /etc/tuned/postgresql
    

  2.  Create a profile at the directory you created, e.g., create tuned.conf at /etc/tuned/postgresql. The following is an example profile for PostgreSQL
    
    $ cat /etc/tuned/postgresql/tuned.conf
    #
    # tuned configuration
    #
    
    [main]
    summary=Optimize for running PostgreSQL inside a virtual guest
    
    [cpu]
    governor=performance
    energy_perf_bias=performance
    min_perf_pct=100
    
    [disk]
    readahead=>4096
    
    [sysctl]
    vm.dirty_ratio = 2
    vm.dirty_background_ratio=1
    vm.overcommit_memory=2
    vm.swappiness = 0
    vm.nr_hugepages=1192
    
    [vm]
    transparent_hugepages=never
    
    

    This profile basically sets the system to use 2MB hugepages for PostgreSQL. It is worth nothing that in the PostgreSQL's $PGDATA/postgresql.conf configuration file, we have these lines:
    
    shared_buffers = 2GB
    huge_pages = on
    

    Since shared buffers is 2GB/(hugepage size) = 2GB/2MB = 1024 pages, the vm.nr_hugepages (the number of reserved hugepages) cannot be less than 1024 pages plus additional pages for the process itself and others; otherwise, PostgreSQL will fail to start.

Friday, October 18, 2019

Enabling pg_stat_statements for PostgreSQL Database System Tuning and Analysis

To facilitate PostgreSQL database system performance tuning and analysis, we ought to collect query statistics, such as, the amount of time that a query takes. For this, we should enable pg_stat_statements extension. The following shows the steps on a Fedora Linux system:

  1. Install the postgresql-contrib package. In the system shell, run command

    sudo yum install postgresql-contrib

  2. Create pg_stat_statements extension for the database whose query performance we wish to analyze, e.g., we wish to tune performance for database template1,

    echo "CREATE EXTENSION pg_stat_statements;" | psql -U postgres template1

  3. Enable query statistics collection by editing $PGDATA/postgresql.conf where PGDATA is where the database is, e.g.,
    
    # Loading the extension
    shared_preload_libraries = 'pg_stat_statements'
    
    # Increase the max size of the query strings that the database system records
    track_activity_query_size = 2048
    
    # Track all statements including those generated by stored procedures
    pg_stat_statements.track = all
    
    # Log queries that run more than 1000 milliseconds. 
    log_min_duration_statement = 1000
    
    
  4. Restart the database server, i.e.,
    
    sudo systemctl restart postgresql
    



Thursday, October 17, 2019

Increasing PostgreSQL Maintenance Task Speed

I observed maintenance tasks, such as, indexing a large table on a PostgreSQL database took long time. Examining the PostgreSQL configuration, I saw the maintenance_work_mem is 64MB, the default value, e.g.,

postgres=# show maintenance_work_mem;
 maintenance_work_mem
----------------------
 64MB
(1 row)

postgres=#

To speed up the indexing processing, I temporarily increase it to 1GB without restarting the database server. The following transcripts shows the steps:

postgres=# show maintenance_work_mem;
 maintenance_work_mem
----------------------
 64MB
(1 row)

postgres=# ALTER SYSTEM SET maintenance_work_mem='1GB';
ALTER SYSTEM
postgres=# show maintenance_work_mem;
 maintenance_work_mem
----------------------
 64MB
(1 row)

postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1 row)

postgres=# show maintenance_work_mem;
 maintenance_work_mem
----------------------
 1GB
(1 row)

postgres=#



What Are Current Queries in PostgreSQL Database?

I wonder what queries my PostgreSQL database are executing, and figure it out using the query below

SELECT query FROM pg_stat_activity;

Finding Disk's Serial Numbers on FreeNAS (or FreeBSD)

To list hard disk drives' serial numbers in a FreeNAS or a FreeBSD system, we can run the following command

geom disk list | grep ident

Limiting Number of Old Kernels in Fedora Linux

As we upgrade/update Linux systems, such as, Fedora Linux, we will see the old kernels accumulate in the system. Since these kernel images are in the Boot partition that is often small, the Boot partition can run out of space. We may remove these old kernel images if we are certain the new kernel installed via the upgrade/update process works fine. In the past, I did it manually (since I didn't know better). In fact, we can do it automatically by configuring the system to allow a number of kernels in the system.

For this, in Fedora Linux, we can add a line to /etc/dnf.conf, such as,

installonly_limit=2

which limits the number of installed kernels to 2. When we perform a dnf upgrade, the system shall remove the old kernel images exceeding the limit. By the way, for a Linux distribution utilizing YUM, adding the same line to /etc/yum.conf will have the same effect.

Monday, October 7, 2019

Permission Denied when Querying PostgreSQL Database

When I query a PostgreSQL database whose name is exampledb that has a table called exampletbl from PostgreSQL user exampleuser, I encountered "Permission Denied" error as follows,

exampledb=> SELECT * FROM exampletbl;
ERROR:  permission denied for relation exampletbl
exampledb=>

The solution is to grant necessary permissions to the user. In the following, I grant "All Permissions" to the user. The following is the command on the command line from a terminal window:

$ echo GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO exampleuser | \
  psql -U postgres exampledb

The key is to grant permissions to the tables in the database to the user, and we can complete this via the database admin user (postgres).

We may also need to grant the user permissions to "All Sequences" and "All Functions" as follows on the command line,

$ echo GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO exampleuser | \
  psql -U postgres exampledb
 
$ echo GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO exampleuser | \
  psql -U postgres exampledb 

Saturday, October 5, 2019

Eclipse Complains "ImportError: DLL Load failed"

When I ran a Python program in a PyDev project in Eclipse, I encountered the following error:

ImportError: DLL load failed: The specified module could not be found.

It turns out that the PATH (or path in UNIX systems) in environment variable does contain the path where the DLL files required by the package imported in the Python program are in. Knowing this, I resolve it by adding the path to the environment variable.

Instead of adding it to the system path environment variable, I added it to the Eclipse "run-as" configuration, e.g., right-click on the Python program we want to run, select "Run As", click on "Run Configuration", click on the "Environment" tab, click at the "Select" button, check the "Path" variable, bring up the edit dialog window, and add the DLL path value to the existing value.

I am using Anaconda3 in a Windows 10 host and installed it at C:\Applications\Anaconda3. In this case, the DLL path value I added is C:\Applications\Anaconda3\Library\bin