Hello,
We have MonetDB open files usage reaching 65k and beyond. How do we minimize the openfiles, We see most of them are mapped to memory.
What is the storage(); function? What does the storage() function do when we execute it ? The usage of open files drastically reduced when the select *from storage() was executed on the database. What are the effects of running the select *from storage(); when users are accessing the database?
What is the maximum size of database MonetDB can support ? Does running the storage() function when there is a large set of open files crash the database ? Do we need to set any system level parameters ? shmmax, file-max etc., Do we have any standard settings as prerequisites that needs to be considered if we are hosting databases ranging 1TB to 2TB? What should be the open files setting if we have 6-10 databases with size ranging from 100GB to 2TB ? Do we have options to export specific schemas rather that complete database or tables? Request you to please review and share the above information.
Open files Test Case -
DB size 630Gb Max Open files set to - 65536
[oracle@lnx1386 scripts]$ sh 10min_ofc.sh lsof -u oracle|wc -l Timestamp: 04-14-18 01:56:34 Open Files: 6559 lsof -u oracle|grep -i tcp|wc -l Timestamp: 04-14-18 01:56:34 Open Files Of TCP: 21 lsof -u oracle | grep -i /data/MYDBFARM_DIR/SV11DMP_DBFARM/TSV_PROD_DB | wc -l Timestamp: 04-14-18 01:56:35 Open Files - DB Files : 5864 lsof -u oracle | grep -i protocol: TCP | wc -l Timestamp: 04-14-18 01:56:35 Open Files Of - protocol: TCP : 2 lsof -u oracle | grep -i CLOSE_WAIT | wc -l Timestamp: 04-14-18 01:56:35 Open Files of TCP - CLOSE_WAIT: 0 lsof -u oracle | grep -i ESTABLISHED | wc -l Timestamp: 04-14-18 01:56:36 Open Files Of TCP - ESTABLISHED: 15 netstat -an |grep tcp|wc -l Timestamp: 04-14-18 01:56:36 netstat -an |grep tcp : 62 [oracle@lnx1386 scripts]$ sh /home/oracle/scripts/purge_op_fl.sh 50005 TSV_PROD_DB 1>/home/oracle/scripts/logs/purge_op_fl.stout 2>/home/oracle/scripts/logs/purge_op_fl.sterr [oracle@lnx1386 scripts]$ cat /home/oracle/scripts/purge_op_fl.sh /usr/bin/mclient -p ${1} -d ${2} < /home/oracle/scripts/purge_op_fl.sql [oracle@lnx1386 scripts]$ cat /home/oracle/scripts/purge_op_fl.sql select * from storage(); [oracle@lnx1386 scripts]$ [oracle@lnx1386 scripts]$ sh 10min_ofc.sh lsof -u oracle|wc -l Timestamp: 04-14-18 02:09:49 Open Files: 862 lsof -u oracle|grep -i tcp|wc -l Timestamp: 04-14-18 02:09:49 Open Files Of TCP: 25 lsof -u oracle | grep -i /data/MYDBFARM_DIR/SV11DMP_DBFARM/TSV_PROD_DB | wc -l Timestamp: 04-14-18 02:09:50 Open Files - DB Files : 163 lsof -u oracle | grep -i protocol: TCP | wc -l Timestamp: 04-14-18 02:09:50 Open Files Of - protocol: TCP : 2 lsof -u oracle | grep -i CLOSE_WAIT | wc -l Timestamp: 04-14-18 02:09:50 Open Files of TCP - CLOSE_WAIT: 0 lsof -u oracle | grep -i ESTABLISHED | wc -l Timestamp: 04-14-18 02:09:50 Open Files Of TCP - ESTABLISHED: 19 netstat -an |grep tcp|wc -l Timestamp: 04-14-18 02:09:50 netstat -an |grep tcp : 66 [oracle@lnx1386 scripts]$
Thank You, Gautham