Database Posts

How to do a Simple Query Analysis and Optimization in Couchbase

I’ve joined an Indonesian Startup named Mapan since this September, leaving my previous role at a multinational consulting company. And to be honest, in here there are so many things to learn and a whole new playground to play with, and let me share one of new thing i’ve learn during my one month service here.

After im spending some time with mandatory company induction and way of working, i started with some technical matters and found a quite interesting issue regarding slow queries to Couchbase database, thats why im share step by step on how to analyze and gives workarond so that perhaps it wont happen again.

Dislaimer : on the time this writing is created, im working with Couchbase for less than 1 month. This writing is still open for discussion and improvement :-D .


Study Case #1

SELECT inventory.*
FROM inventory
WHERE inventory.`key` LIKE "ih:RUMA_0101_AABY12349%"
limit 1000

With almost 1 million data, it took more than 30seconds to fetch 1000 data which have key that starts with “ih:RUMA_0101_AABY12349”.

First analysis is that corresponding query despite hitting on an index, it still gives a quite amount of time of fetching as you can see on below image. It is happen mostly due to fetching is done first with all the data (85% of time is used for this, around 38 seconds) and do the filtering later on.

First thing that i see is that query is done on “key” field which have a common format, like “key”: “ih:RUMA_0101_AABY12349:1534087762346131”. So, instead of searching based on wildcard, better do searching on split string of “key” field, and create and index based on it.

CREATE INDEX key02_split ON inventory ( split(inventory.`key`, ":")[1] )   
PARTITION BY HASH (split(inventory.`key`, ":")[1]) where split(inventory.`key`, ":")[1] is not null

And change the query needed into below,

SELECT inventory.*
FROM inventory
WHERE split(inventory.`key`, ":")[1] = 'RUMA_0101_AABY12349'
limit 1000

Drastically reducing the query time into 80ms, as on below screenshot.


Study Case #2

SELECT `inventory_trx`.* FROM `inventory_trx` 
WHERE client = "RUMA" 
and movement_type in ["IB001", "IB002", "IB003", "OB001", "OB002", "TF001", "TF002", 
"IA001", "IA003", "IA005", "IA007", "IA002", "IA004"] 
and transaction_timestamp between 1525107600 and 1537808399 and  
(origin.code = "0101" or destination.code = "0101")  
order by transaction_timestamp desc offset 0 limit 1000

Again, same issue happen here. Despite hitting get_stock_mutation index, it still takes some time for fetching. On below screenshot, it shows almost 36 seconds only for fetching.

Again, almost the same approach is used for this case. We can see which field is used on WHERE parameter, and start indexing it. The only difference is that it have range query on transaction_timestamp field and also order by criteria, which will make it a little bit complicated.

It took not only query optimization, but also some negotiation to implements some changes on User Interface. On previous query, transaction_timestamp parameter doesnt have any limitation on start and end date, therefore make it harder to do indexing. After consulting with user, they are agree that there should be a one month limitation for doing range query.

After user agree with changes on UI, next is to create an Index on Couchbase for corresponding query.

CREATE INDEX inventory_trx_ix01 
ON inventory_trx ( client, movement_type, DATE_PART_MILLIS(transaction_timestamp * 1000, "month"), 
origin.code, destination.code, DATE_PART_MILLIS(transaction_timestamp * 1000, "year"))

Tweaking the query into below,

SELECT `inventory_trx`.* FROM `inventory_trx` 
WHERE client = "RUMA" 
and movement_type in ["IB001", "IB002", "IB003", "OB001", "OB002", 
"TF001", "TF002", "IA001", "IA003", "IA005", "IA007", "IA002", "IA004"] 
and DATE_PART_MILLIS(transaction_timestamp * 1000, "month") = 9
and DATE_PART_MILLIS(transaction_timestamp * 1000, "year") = 2018
and  (origin.code = "0101" or destination.code = "0101")
limit 1000

Reducing query time needed significantly,

Google+

[MySQL Error] Got a packet bigger than ‘max_allowed_packet’ bytes

Recently i got an error while importing my data into a new mysql database, this is the complete stacktrace

[Err] 1153 - Got a packet bigger than 'max_allowed_packet' bytes

The problem is gone after i run these command on my mysql console,

set global net_buffer_length=1000000; 
set global max_allowed_packet=1000000000;

(*) (*)

Google+

Menghapus Ratusan Juta Record dari Oracle Table

Baru kemarin ketemu case yang lumayan menarik, aplikasi yang dibuat tahun lalu kena timeout ketika delete seluruh isi table yang berisi paling tidak 200juta record. Berikut error lengkapnya, fyi framework yang dipakai adalah MyBatis dan Spring Framework dan database yang dipakai adalah Oracle,

<2016-10-31 02:15:01,723>,[http-/0.0.0.0:8080-16]>>[INFO]start deleting TABLE_NAME Data
<2016-10-31 02:18:02,426>,[http-/0.0.0.0:8080-16]>>[ERROR]
### Error updating database.  Cause: java.sql.SQLException: ORA-01013: user requested cancel of current operation

### The error may involve id.edwin.service.delete-Inline
### The error occurred while setting parameters
### SQL: delete from TABLE_NAME
### Cause: java.sql.SQLException: ORA-01013: user requested cancel of current operation

; uncategorized SQLException for SQL []; SQL state [72000]; error code [1013]; ORA-01013: user requested cancel of current operation
; nested exception is java.sql.SQLException: ORA-01013: user requested cancel of current operation

sepertinya sudah timeout duluan (3menit timeout), padahal belum semua data terhapus. :-(

Setelah googling bentar, sepertinya ada dua solusi yaitu menggunakan TRUNCATE dan CTAS (CREATE TABLE AS SELECT). Setelah diskusi panjang lebar dengan kuncen (admin) Database, opsi terakhir (sepertinya) jauh lebih cepat, drop table tersebut kemudian di re-create ulang. Berikut adalah query-nya

CREATE TABLE TABLE_NAME_NEW AS
        SELECT * FROM TABLE_NAME WHERE ROWNUM = 1 ;
Rename TABLE_NAME to TABLE_NAME_OLD ;
Rename TABLE_NAME_NEW to TABLE_NAME;
drop table TABLE_NAME_OLD ;

Setelah itu baru didelete isi table TABLE_NAME, lebih cepat karena isi datanya hanya 1 row. Satu-satunya kekurangan adalah, tidak bisa replicate Primary Key dan Index dari table yang sebelumnya di drop, yang mana itu bukanlah masalah bagi saya :-D

Google+

Weird Exception, “Unsupported startup parameter: extra_float_digits” When Connecting to PostgreSQL

Today I’m planning on deploying my application (which runs well on my local laptop), to a production server. But there is a very weird error occur, it says

Caused by: org.postgresql.util.PSQLException: ERROR: Unsupported startup parameter: extra_float_digits
	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:291)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
	at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
	at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)

Somehow it seems that the error happen because of some wrong PostgreSql JDBC version. But the error stays even if I changed my Postgresql JDBC version several times. So my temporary conclusion is, improper Postgresql configuration.

After debating with several sysadmins and DBA, i found out that my sql connection actually goes to a Connection Pooling called PGBouncer, instead of connecting directly to Postgresql. Weird, because i also using Connection Pooling (Apache DBCP) for my application. So it means that my app is using a double connection pooling :D

After spending sometime googling, i found out that all i need to do is adding this simple configuration on pgbouncer.ini

ignore_startup_parameters = extra_float_digits

hint :
If your are unable to found the location for pgbouncer.ini, just using a simple ps -ef command on console.

bash-4.1$ ps -ef | grep pgbouncer
500      18927     1  0 May30 ?        00:00:00 /opt/PostgresPlus/pgbouncer-1.6/bin/pgbouncer -d /opt/PostgresPlus/pgbouncer-1.6/share/pgbouncer.ini
500      19045 18913  0 00:11 pts/4    00:00:00 grep pgbouncer

As you can see, the pgbouncer.ini location is in /opt/PostgresPlus/pgbouncer-1.6/share/.

Well, one thing solve, and suddenly i found another error :sigh. So many errors today. Here is my complete stacktrace

 org.postgresql.util.PSQLException: ERROR: No such database: mydatabase
 	at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:291)
	at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:108)
	at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:66)
	at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:125)
	at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:30)
	at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:22)
	at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:30)

Somehow i cannot find mydatabase, despite i already created it.

Actually, the answer is simple. Again pgbouncer is the culprit. I cannot connect to a database if the database havent registered yet at pgbouncer.ini. Registering my database solve this issue.

;; database name = connect string
;;
;; connect string params:
;;   dbname= host= port= user= password=
;;   client_encoding= datestyle= timezone=
;;   pool_size= connect_query=
[databases]

; foodb over unix socket
edb = host=127.0.0.1 port=5444
mydatabase = host=127.0.0.1 port=5444

You can see my PostgreSQL’s port is 5444.

Hopefully, my tutorial helps other. Because i spent a ridiculously amount of time solving it :D

Google+

Daftar Kode Provinsi, Kecamatan, Kabupaten dan Desa di Indonesia Menurut Kemendagri

Setelah berkeliaran kesana-kesini, apalagi data kemendagri berupa pdf sehingga menyulitkan diparsing :-P , akhirnya dapet juga versi sql dari data Kemendagri. Setelah diparsing sehingga mysql-compatible, hasil akhir sql filenya bisa diunduh di —> sini

Data Kemendagri

Google+