So, server is idle - it's run only one VM. This virtual machine (Debian 9.1, 16GB RAM, 4 VCPU), which runs PostgreSQL and MySQL databases. Queries receive only one - PostgreSQL (few insert per second). And... it takes lot of time, to do this inserts. I wrote simple script, which insert data to very simple table, whitout any constraints or indexes (three varchar fields). Script performance - about 1-2 inserts per second. It's extremely slow!
At first, I thought it's was Postgres trouble. But I rewrote my script for MySQL, and it shows same result. Then, I create new VM on FreeBSD, install PostgreSQL, create same database and table from Linux VM, and check performance again - 1-2 inserts per second.
Okay. Let's see on iostat and iotop. iotop shows, that Postgres process, which serves my insert queries, consume about 60-70% of IO (for simple inserts!) on VM. iostat -x says, that now uses about 40-45 writes/sec on VM, and about 80-90 w/s in host machine.
I tried to rate write speed with dd utility, like this:
Code: Select all
dd if=/dev/urandom of=/tmp/fooo.bar bs=1M count=1000
Next, while script execute about 100 inserts, I check file:
Code: Select all
/proc/[Postgres_PID]/io
rchar: 138544
wchar: 114689
syscr: 39
syscw: 15
read_bytes: 0
write_bytes: 540672
cancelled_write_bytes: 0
UPD: I'm copied remote file (about 4,5 GB) to this VM via SFTP. It was copied on 15-20 MB/s speed (about 3-4 minutes). I really don't understand it. I thought, that it was KVM problem (2 different database have same troubles, different VM on FreeBSD have same troubled behavior, wich Linux VM), but remote file was copied fast, iotop shows 20-30% load, and iostat shows w/s about 150-200 (sometimes about 300). What's happening? Please, help someone...
