Category Archives: Databases

Writing market data to SQLite database

SQLite utilizes up to 90% of the my SSD disk when SHIB coin riches its local peaks with more than 10.000 – 18.000 trades/min that is 166 – 300 trades/second. My C++ app inserts 64-bit values to tables like this:

CREATE TABLE trades (time INT PRIMARY KEY NOT NULL, price REAL, volume REAL) WITHOUT ROWID;

so it is only 3984 – 7200 bytes/second. Totally across all markets there can be about 30.000 trades/min that is 500 trades/second and only 12.000 bytes/second and it is enough to slow down my SQLite database.

I use WAL with 4GB cache:

PRAGMA journal_mode = WAL;
PRAGMA cache_size = -4 * 1024 * 1024;

and do all the inserts in a transaction once a 5 seconds into 500+ tables (a separate table for each market) with a prepared statement.

See the screenshots of my app below:

(more…)

Auditing of Autonomous Oracle Database

Check if unified auditing is enabled:

SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';

Check what options are enabled:

SELECT up.AUDIT_OPTION, uep.SUCCESS, uep.FAILURE from AUDIT_UNIFIED_ENABLED_POLICIES uep, AUDIT_UNIFIED_POLICIES up 
where uep.ENTITY_NAME = 'ALL USERS' and uep.ENABLED_OPTION='BY USER' and uep.POLICY_NAME = up.POLICY_NAME and up.AUDIT_OPTION_TYPE = 'STANDARD ACTION'
(more…)

Creating Oracle Database in a Cloud

Login to Oracle Cloud console, go to Autonomous Transaction Processing page:

(more…)

Shrinking MySQL database

Calculating disk space usage per MySQL DB:

sudo du -h /var/lib/mysql/
932M    /var/lib/mysql/beauty
179M    /var/lib/mysql/slogpost
485M    /var/lib/mysql/omegauto
8.0K    /var/lib/mysql/test
676K    /var/lib/mysql/sys
22G     /var/lib/mysql/bot
79M     /var/lib/mysql/master
11M     /var/lib/mysql/mysql
176K    /var/lib/mysql/phpmyadmin
21M     /var/lib/mysql/shar
127M    /var/lib/mysql/mike
1.1M    /var/lib/mysql/performance_schema
79M     /var/lib/mysql/devnote
12M     /var/lib/mysql/mike1
48K     /var/lib/mysql/game
24G     /var/lib/mysql/
(more…)

Setting up Oracle Database for development

To set up an Oracle Database for development I install the database in a docker container and then modify password policies in the default user profile as follows:

ALTER PROFILE DEFAULT LIMIT COMPOSITE_LIMIT UNLIMITED
  PASSWORD_LIFE_TIME UNLIMITED
  PASSWORD_REUSE_TIME UNLIMITED
  PASSWORD_REUSE_MAX UNLIMITED
  PASSWORD_VERIFY_FUNCTION NULL
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_GRACE_TIME UNLIMITED
  FAILED_LOGIN_ATTEMPTS UNLIMITED;

this prevents passwords from expiring and users from being locked.

(more…)

Oracle Database 19c auditing with Multitenant Architecture

First I installed Oracle 19c in Docker container , enabled unified auditing, and created multiple pluggable databases:

export ORACLE_SID=ORCLCDB
cd $ORACLE_HOME/bin
#connect to root container
./sqlplus / as sysdba
CREATE PLUGGABLE DATABASE testpdb1 ADMIN USER admin1 IDENTIFIED BY dbpasswd1
    FILE_NAME_CONVERT=('/opt/oracle/oradata/ORCLCDB/pdbseed', '/opt/oracle/oradata/ORCLCDB/testpdb1');
Pluggable database created.
CREATE PLUGGABLE DATABASE testpdb2 ADMIN USER admin2 IDENTIFIED BY dbpasswd2
    FILE_NAME_CONVERT=('/opt/oracle/oradata/ORCLCDB/pdbseed', '/opt/oracle/oradata/ORCLCDB/testpdb2');
Pluggable database created.
(more…)

Installing Oracle Database 19.3 in a Docker container on Ubuntu Server 18.04

First I installed Ubuntu Server 18.04 as a Hyper-V machine on Windows 10.

While creating the virtual machine I selected Generation 1, because Generation 2 resulted in some strange effects.

Then I created a separate user with sudo permission:

sudo useradd -d /home/singh -m --shell "/bin/bash" singh
sudo passwd singh
sudo usermod -aG sudo singh
(more…)

Inegrating Oracle Database 18 with MS Active Directory

I configured DNS on my Windows Server 2016 (takes some time, probably 15 minutes):

ipconfig /registerdns
(more…)

Measuring SQLite insertion performance with C++ code

I did a quick Google search on “SQLite performance” and found the following:

Then to benchmark SQLite performance by myself I used the following C++ code that inserts 1000 batches of 1000 000 rows to a single table with an integer primary key:

(more…)

Oracle Database user and role IDs

Below I provided the results of two following queries:

SELECT USERNAME, USER_ID FROM DBA_USERS ORDER BY USER_ID DESC;
SELECT ROLE, ROLE_ID FROM DBA_ROLES ORDER BY ROLE_ID DESC;

that display Oracle Database 18 users, roles and their IDs:

(more…)