Speed insert or update query running on a large postgres table

Got this tip from the this post:

Problem

When you run a large query (insert/update) on a huge table with several indexes, these indexes can seriously slow the query execution.

Solution

With Postgresql it can be faster to disable the indexes before running the query and reindex all the table afterwards.

You can do it like this :

  1. Disable all table indexes
UPDATE pg_index
SET indisready=false
WHERE indrelid = (
    SELECT oid
    FROM pg_class
    WHERE relname='<TABLE_NAME>'
);
  1. Run your query
UPDATE <TABLE_NAME> SET ...;
  1. Re-enable all table indexes
UPDATE pg_index
SET indisready=true
WHERE indrelid = (
    SELECT oid
    FROM pg_class
    WHERE relname='<TABLE_NAME>'
);
  1. Re-index table

Edit Width of Thermal Receipt Jasper Report

Problem

Need to edit the dimensions of a thermal receipt that is used in cash point machines. The dimensions I got for the paper were 57X47X13. These dimensions are in mm.

The important thing to note is 57mm is the width.

Solution

Change the jrxml dimesions from Pixels to mm. Then Change the width of the report to 57mm.

Then edit the text fields dimensions accordingly.

Here’s a screenshot of the report:

thermal receipt on jasper reports
Thermal receipt on Jasper Reports

References

THERMAL ROLL 57X40X13MM

timedatectl

Problem

An ubuntu server 16.04 time was not being synchronized. Here was the error in the syslog:


timesyncd[26781]: Timed out waiting for reply from [2001:67c:1560:8003::c8]:123 (ntp.ubuntu.com)

 

When I checked time timedatectl NTP synchronized was showing as off and when I tried to turn it on it wasn’t changing state.


# timedatectl
Local time: Mon 2019-01-21 10:49:54 XXX
Universal time: Mon 2019-01-21 07:49:54 UTC
RTC time: Mon 2019-01-21 07:53:45
Time zone: One/Two (XXX, +0300)
Network time on: yes
NTP synchronized: no
RTC in local TZ: no

# timedatectl set-ntp on
# timedatectl
Local time: Mon 2019-01-21 10:54:41 XXX
Universal time: Mon 2019-01-21 07:54:41 UTC
RTC time: Mon 2019-01-21 07:58:32
Time zone: One/Two (XXX, +0300)
Network time on: yes
NTP synchronized: no
RTC in local TZ: no
# timedatectl
Local time: Mon 2019-01-21 11:12:14 XXX
Universal time: Mon 2019-01-21 08:12:14 UTC
RTC time: Mon 2019-01-21 08:16:05
Time zone: One/Two (XXX, +0300)
Network time on: yes
NTP synchronized: no
RTC in local TZ: no

Solution

I needed to update the firewall settings and allow outgoing traffic for the ntp port. After this the time was synchronized:


systemd[1185]: Time has been changed

Then when you check timedatectl


# timedatectl
Local time: Mon 2019-01-21 11:35:04 XXX
Universal time: Mon 2019-01-21 08:35:04 UTC
RTC time: Mon 2019-01-21 08:35:04
Time zone: One/Two (XXX, +0300)
Network time on: yes
NTP synchronized: yes
RTC in local TZ: no

References

How To Set Up Time Synchronization on Ubuntu 16.04

How to use ntpdate behind a proxy?

Remove unused docker images containers network and volumes

Problem

Unused containers, images, volumes, networks were taking up alot of disc space

Solution

  • You can decide first decide to clear out containers that have been stopped that is those with their status as exited:
$ docker rm $(docker ps -a -f status=exited -q)
  • You can also delete containers with status created:
$ docker ps -a -f status=created
$ docker rm $(docker ps -a -f status=created -q)
f5eb19a737e2
e849f8700371
  • Now you can delete all the unused (dangling) images, volumes, networks:
imela@whiscardz:~$ docker system prune
WARNING! This will remove:
  - all stopped containers
  - all networks not used by at least one container
  - all dangling images
  - all dangling build cache

Are you sure you want to continue? [y/N] y
Deleted Containers:
183b526b500c4bf223716a1016f9570dc14b9e5e020f2075e064b1eea3c0f3d9
e98c862cf285dbf501657ca6b60505cb0c3093c6e46a89c23758626fb32e5806
732140022a4d68abf68f0361d97582e840b8f0fc0673804b0f4549b8552c9cc1
a5077fd981b185a0e917fa5c093f233ae3ced605b37716337b18198c0623ab94
d2ea666070bfd37befbd5bca65cfa818b031221e7e17bffe163b14babb7cef10
66f9ee55e6dd7b9c1d90bfd1cda0163817dc6ba2178794bfa638a9681f37e89e

Deleted Networks:
imeddemo_default
jasperserver_default
bk_default

Deleted Images:
deleted: sha256:1eda126390080a96b1eebaf8848a7ba28317abae83a41d30641da7161e998909
deleted: sha256:0a0d7f8bdfea6c863856c626e06fc859ae39f3e2e3c4e30a333a7968ba1d96ef
deleted: sha256:f7613ad065286861d41ab4a369fd7e2093bb136eb18120a2fcb9ea00c56e5aca

Total reclaimed space: 8.627GB
  • The above does not delete unused volumes so you do that below. You can optionally first save all the volumes to be deleted in a file.
imela@whiscardz:~/docker/cleanup/2020-07-23$ docker volume ls -q -f 'dangling=true' > danglingVolumes.txt
imela@whiscardz:~/docker/cleanup/2020-07-23$ docker volume rm $(docker volume ls -q -f 'dangling=true')
0d0cc707154ca775c6e757af0c1e3f4217148f2782270378966168a5c4b8a27c

References

How To Remove Docker Images, Containers, and Volumes