Insert into a table with 100000 records,each record about 40 bytes,cost 2.3G memory.So is there some way to decrease the memory cost?

[joffier]

Environmet:
virtual-machine ubuntu x86_64 8C 8G
redis5.0 cluster mode,with 3 master nodes,no slave

create db and table sql:
REDISQL.REDISQL.CREATE_DB DB_DEVICE
REDISQL.EXEC DB_DEVICE "create table device_tab (seq TEXT,deviceId TEXT,customerId TEXT,tranName TEXT,time TEXT);"
REDISQL.EXEC DB_DEVICE "create index idx_deviceId on device_tab(deviceId);"

insert shell:
#!/bin/bash
for var in $(seq 1 100000)
do
redis-cli -c -p 6381 REDISQL.EXEC DB_DEVICE "insert into device_tab values('007','did3','2019081304','MonInfoUdt','154006');"
echo $var
done

after insert into the table:
127.0.0.1:6381> REDISQL.EXEC mydb "select count(*) from device_tab;"

1. 1. (integer) 101012

127.0.0.1:6381> info memory

Memory

used_memory:2117413200
used_memory_human:1.97G
used_memory_rss:2565685248
used_memory_rss_human:2.39G

So i think this cost too many memory.And is there something wrong what i did or is there some way to decrease the memory cost?

By the way,i create the same table and index just use sqlite3 with disk mode.And insert about 100000 records(each is same with above redisql insert record).The disk file is 5.9M.

root@joffier-virtual-machine:/data/sqlite# sqlite3 mydb.sqlite "select count(*) from device_tab"
101757

root@joffier-virtual-machine:/data/sqlite# ls -lh
总用量 5.9M
-rw-r--r-- 1 root root 159 8月 14 14:19 batchInsert.sh
-rw-r--r-- 1 root root 5.9M 8月 14 14:37 mydb.sqlite

Hope your reply sincely. Thank you!

[siscia]

Hi there!

so the info memory command does not report the amount of memory used by RediSQL.

There is a way to get to know this information precisely, but it uses SQLite C API, which I need to integrate into RediSQL. (https://www.sqlite.org/c3ref/memory_highwater.html)

First thing I would ask you to do, is to see if is really RediSQL that use all that memory, it should not be.

So please, use the info memory command before inserting all the data, and right after. Since RediSQL does not update the counter of Redis, the number that you see should be quite similar.

Also, please check that you only have RediSQL in that machine, the key * command should return about empty or small keys.

What does the OS say about the size of the Redis instance?

[joffier]

Hi siscia,

Thank you for your reply.

Since i'm not know the 'info memory' command does not report the amount of memory used by Redisql.

Before insert into the table,the info memory shows like follows(i choose the empty node 6379):
127.0.0.1:6379> keys *
(empty list or set)

127.0.0.1:6379> info memory

Memory

used_memory:1542120
used_memory_human:1.47M
used_memory_rss:18481152
used_memory_rss_human:17.62M

And now the 6381 node is like follows:
127.0.0.1:6381> keys *

1. "mydb"

127.0.0.1:6381> info memory

Memory

used_memory:2117434112
used_memory_human:1.97G
used_memory_rss:2568749056
used_memory_rss_human:2.39G

And the OS information like follows:
root@joffier-virtual-machine:~# ps -ef|grep redis-server
root 80212 80045 0 17:10 pts/21 00:00:00 grep --color=auto redis-server
root 108513 1 0 15:37 ? 00:00:11 redis-server *:6379 [cluster]
root 108529 1 0 15:37 ? 00:00:11 redis-server *:6380 [cluster]
root 108543 1 0 15:37 ? 00:00:54 redis-server *:6381 [cluster]

root@joffier-virtual-machine:~# top
top - 17:04:49 up 6:18, 7 users, load average: 0.00, 0.00, 0.00
Tasks: 237 total, 1 running, 236 sleeping, 0 stopped, 0 zombie
%Cpu(s): 0.7 us, 1.0 sy, 0.0 ni, 98.0 id, 0.0 wa, 0.0 hi, 0.3 si, 0.0 st
KiB Mem : 8156932 total, 4076612 free, 3247292 used, 833028 buff/cache
KiB Swap: 8385532 total, 8352804 free, 32728 used. 4590320 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1573 joffier 20 0 1232184 83644 47864 S 0.3 1.0 2:57.68 compiz
80135 root 20 0 94924 7000 6052 S 0.3 0.1 0:00.09 sshd
80183 root 20 0 43692 3808 3048 R 0.3 0.0 0:00.10 top
108513 root 20 0 489216 18048 7088 S 0.3 0.2 0:10.68 redis-server
108529 root 20 0 494828 23896 7080 S 0.3 0.3 0:10.69 redis-server
108543 root 20 0 3444156 2.392g 7908 S 0.3 30.8 0:53.29 redis-server
1 root 20 0 185280 5188 3424 S 0.0 0.1 0:03.22 systemd

[siscia]

Hi there!

So, from the OS we see a lot of memory used by *:6381, not by *:6379.

I guess this is where mydb hash into the Redis cluster.

How much memory you got into this machine? Can you stress it until it gives you OOM?

Indeed I can reproduce the problem. Are you using the latest release?

[joffier]

Hi siscia,

The redisSql module is the latest release like follows:

RediSQL_v1.0.3-rc04_53a7a1_arm-unknown-linux-gnueabi_release.so

The redis version is :redis_version:5.0.5

I allocate 8G memory into the virtual machine.

Yeah,the db key 'mydb' is hashed to node 6381,like follows:

after insert into the table:
127.0.0.1:6381> keys *

1. "mydb"
   127.0.0.1:6381> REDISQL.EXEC mydb "select count(*) from device_tab;"
   (integer) 101012
   127.0.0.1:6381> info memory
   Memory
   used_memory:2117413200
   used_memory_human:1.97G
   used_memory_rss:2565685248
   used_memory_rss_human:2.39G

So does that mean the 100000 records indeed cost 2.39G ram memory? But i think this should not be.

And i will try insert more data until it gives me OOM.

[siscia]

I am running it now with valgrind and indeed I see some memory leak.

Let me go back to you in a while!

[siscia]

So, I fixed a memory leak, but I don't believe it is the issue we are looking for.

At least I didn't see enough improvements.

Anyhow you can get the latest compiled stuff in the tag v1.1.1 after this: https://travis-ci.org/RedBeardLab/rediSQL/jobs/571788918 finish.

I want to track down this issue, so I will add the command to get the amount of memory used by SQLite, and with that command, we can compare and see if we are leaking memory somehow.

[joffier]

Hi siscia,
Thank you for your support.And i have used your latest redisql module 'RediSQL_v1.1.1_9b110f_arm-unknown-linux-gnueabi_release.so'. But it seems dose not work.

Then i continue insert data into the table. And when the ram memory is not enough,the swap space is used.In the attach file,you can see the detail test.

Thank you.
RediSQL_memoryAllocate_test.txt

[siscia]

Hi Joffier,

Indeed I find another memory-related problem. The fix brings the use of memory down to 1/3.

Can you try the latest release when it becomes available? (CI: https://travis-ci.org/RedBeardLab/rediSQL/builds/571928427)

I noticed that you mentioned the ARM build, are you running on ARM? We have the x86 builds as well.

Cheers,

[siscia]

Nope, sorry, found a bug in this implementation.

But I really believe to have found your issue.

[joffier]

Hi Siscia,
Sorry for use the wrong version of RediSql module. Then i choose the latest x86-64 module 'RediSQL_v1.1.1_9b110f_x86_64-unknown-linux-gnu_release.so'. But it seems dose not work.Detail data as follows:
192.168.232.128:6380> keys *

1. "DB_DEVICE"
   192.168.232.128:6380> REDISQL.EXEC DB_DEVICE "select count(*) from device_tab;"
2. 1. (integer) 100375
      192.168.232.128:6380> info memory

Memory

used_memory:2105054720
used_memory_human:1.96G
used_memory_rss:2548842496
used_memory_rss_human:2.37G

root@joffier-virtual-machine:/data/redis/module# ps -ef|grep redis-server|grep 6380
root 5100 1 3 14:32 ? 00:01:02 redis-server *:6380 [cluster]

Tasks: 234 total, 1 running, 233 sleeping, 0 stopped, 0 zombie
%Cpu(s): 1.0 us, 0.3 sy, 0.0 ni, 98.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 8156932 total, 4638204 free, 2832572 used, 686156 buff/cache
KiB Swap: 8385532 total, 7900720 free, 484812 used. 5023652 avail Mem

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1573 joffier 20 0 1232920 33188 18224 S 0.7 0.4 5:12.97 compiz
7 root 20 0 0 0 0 S 0.3 0.0 1:08.02 rcu_sched
4851 root 20 0 94924 6656 5648 S 0.3 0.1 3:11.80 sshd
5100 root 20 0 3374000 2.374g 7888 S 0.3 30.5 1:02.14 redis-server

Waiting for your good news :D

[siscia]

We are getting there!

The latest version had an issue (aka they break the system) with the virtual tables so I didn't want to publish online.

However, you can try it from here.
redisql_1.1.2.tar.gz

Does it improve the memory situation?

[joffier]

Hi Siscia,

That's great. And it does decrease the memory cost. I also found that the message in command 'info memory',used_memory_human dose not change,used_memory_rss_human have increased from 19.99M to 25.35M. So is that mean the real memory allocate? If you could offer the sqlite3 memory statics command in later version,that's will be wonderful.

And from the OS 'top' statics,i could see the total free memory from '7105408B+687248B=7792656B' to '7062972B+710504B=7773496B' .May be some other process cost a little extra memory in the insert procedure. In a word,the OS memory dose not cost so many as before.

Thank you!

1. 1. (integer) 0 1) 1) (integer) 100497
      127.0.0.1:6380> info memory 127.0.0.1:6380> info memory

Memory # Memory

used_memory:1541928 used_memory:1543144
used_memory_human:1.47M used_memory_human:1.47M
used_memory_rss:20959232 used_memory_rss:26583040
used_memory_rss_human:19.99M used_memory_rss_human:25.35M

Before:
Tasks: 238 total, 1 running, 237 sleeping, 0 stopped, 0 zombie
%Cpu(s): 1.0 us, 1.0 sy, 0.0 ni, 98.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 8156932 total, 7105408 free, 364276 used, 687248 buff/cache
KiB Swap: 8385532 total, 7913320 free, 472212 used. 7490780 avail Mem

 PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                                                                                                                                                                            
1573 joffier   20   0 1232920  33188  18224 S  1.0  0.4   5:50.87 compiz                                                                                                                                                                                                             

107332 root 20 0 486644 14540 6300 S 0.3 0.2 0:00.86 redis-server
107354 root 20 0 484576 14452 6196 S 0.3 0.2 0:00.91 redis-server
120544 root 20 0 552376 16424 7856 S 0.3 0.2 0:00.59 redis-server

After:
Tasks: 239 total, 1 running, 238 sleeping, 0 stopped, 0 zombie
%Cpu(s): 1.0 us, 0.7 sy, 0.0 ni, 98.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 8156932 total, 7062972 free, 383456 used, 710504 buff/cache
KiB Swap: 8385532 total, 7913332 free, 472200 used. 7470392 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND             
 1573 joffier   20   0 1232920  33188  18224 S  0.7  0.4   5:56.28 compiz              

120560 root 20 0 43692 3768 3012 R 0.7 0.0 0:03.03 top
107332 root 20 0 486644 14540 6300 S 0.3 0.2 0:02.19 redis-server
107354 root 20 0 484576 16472 6196 S 0.3 0.2 0:02.41 redis-server
120544 root 20 0 571328 25960 7988 S 0.3 0.3 0:47.89 redis-server

[siscia]

Let me re-format this:

(integer) 0 1) 1) (integer) 100497
127.0.0.1:6380> info memory 127.0.0.1:6380> info memory

Memory # Memory

used_memory:1541928 used_memory:1543144
used_memory_human:1.47M used_memory_human:1.47M
used_memory_rss:20959232 used_memory_rss:26583040
used_memory_rss_human:19.99M used_memory_rss_human:25.35M

Before:

Tasks: 238 total, 1 running, 237 sleeping, 0 stopped, 0 zombie
%Cpu(s): 1.0 us, 1.0 sy, 0.0 ni, 98.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 8156932 total, 7105408 free, 364276 used, 687248 buff/cache
KiB Swap: 8385532 total, 7913320 free, 472212 used. 7490780 avail Mem

 PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                                                                                                                                                                            
1573 joffier   20   0 1232920  33188  18224 S  1.0  0.4   5:50.87 compiz                                                                                                                                                                                                             

107332 root 20 0 486644 14540 6300 S 0.3 0.2 0:00.86 redis-server
107354 root 20 0 484576 14452 6196 S 0.3 0.2 0:00.91 redis-server
120544 root 20 0 552376 16424 7856 S 0.3 0.2 0:00.59 redis-server

After:

Tasks: 239 total, 1 running, 238 sleeping, 0 stopped, 0 zombie
%Cpu(s): 1.0 us, 0.7 sy, 0.0 ni, 98.3 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 8156932 total, 7062972 free, 383456 used, 710504 buff/cache
KiB Swap: 8385532 total, 7913332 free, 472200 used. 7470392 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND             
 1573 joffier   20   0 1232920  33188  18224 S  0.7  0.4   5:56.28 compiz              

120560 root 20 0 43692 3768 3012 R 0.7 0.0 0:03.03 top
107332 root 20 0 486644 14540 6300 S 0.3 0.2 0:02.19 redis-server
107354 root 20 0 484576 16472 6196 S 0.3 0.2 0:02.41 redis-server
120544 root 20 0 571328 25960 7988 S 0.3 0.3 0:47.89 redis-server

So we have an increase in memory reported by redis of ~5.35MB which is inline with what we expect give the size of the database file. Great!

I will ship out a new release very soon (hopefully) addressing the virtual table issue. Also, very quickly I want to add the new command for getting the size of the database.

Thanks very much for reporting the issue! Let me know if there is more I can do for you.

I am not yet quite sure about the memory usage reported by the OS but hopefully is nothing too wrong!

[mengpengfei]

We are getting there!

The latest version had an issue (aka they break the system) with the virtual tables so I didn't want to publish online.

However, you can try it from here.
redisql_1.1.2.tar.gz

Does it improve the memory si

i meet the same problem，does it have been solved?
does the issue of redisql_1.1.2.tar.gz have been solved?

[mengpengfei]

We are getting there!
The latest version had an issue (aka they break the system) with the virtual tables so I didn't want to publish online.
However, you can try it from here.
redisql_1.1.2.tar.gz
Does it improve the memory si

i meet the same problem，does it have been solved?
does the issue of redisql_1.1.2.tar.gz have been solved?
can i use the fts of redisql_1.1.2.tar.gz?

[siscia]

Hello!

Unfortunately the orginal user didn't reply anymore, so the issue was just closed.

From my tests seems that the issue was solved, can you try with the linked binary?

If that works we can do a nice and clean release for you!

Cheers,
Simone

[mengpengfei]

Hello!

Unfortunately the orginal user didn't reply anymore, so the issue was just closed.

From my tests seems that the issue was solved, can you try with the linked binary?

If that works we can do a nice and clean release for you!

Cheers,
Simone

thanks very much.
I have tried with the linked binary of redisql_1.1.2.tar.gz，Actually the issue was solved。

you have said that the version of redisql_1.1.2.tar.gz had an issue (aka they break the system) with the virtual tables.

so Can i use the fts of redisql_1.1.2.tar.gz?

wait for your release for me,thanks.

[siscia]

Let's see if I can find some times in the immediate future for the release.

I don't expect any problem with FTS on the 1.1.2, virtual tables are another quite complex beast.

I believe you are good to go with the 1.1.2

[siscia]

Hummm, no, now that I read better, I don't believe an official 1.1.2 is going to be released.

We will move directly to 2.

[mengpengfei]

I don't know the difference，i support you

[mengpengfei]

what do you mean about “The latest version had an issue (aka they break the system) with the virtual tables so I didn't want to publish online.”
I worry redissql have some problems with FTS on the 1.1.2

[siscia]

No, I was referring to the virtual tables in redisql. This one: https://redisql.redbeardlab.com/rediSQL/references/#virtual-tables

The FTS do not have any problem that I am aware of.