www.infinidb.org

InfiniDB exhausted all memory and swap with one SQL and I must reboot the machine.

The SQL is 'select METRIC_NAME from dim_metrics a, ft_metric_host b where a.metric_id=b.metric_id;'.

This machine has 144G memory.

How to limit memory usage of InfiniDB?

How to prevent InfiniDB eat up all memory?

Lets see if we can get some more information to help determine the problem/solution

Can you provide the following information:

1. Run this in the mysql console:
select count(*) from dim_metrics ;
select count(*) from ft_metric_host;
desc dim_metrics;
desc ft_metric_host;

2. Run this on a unix command line, provides current InfiniDB configuration settings:
egrep "Pm|Um|MaxOut|NumBlocksPct" /usr/local/Calpont/etc/Calpont.xml

3. Run this on the unix command line, will provide available system memroy:
free -g

4. Can you have top (shift m - to sort by memory usage) running when you execute the
query and then provide memory usage of the InfiniDB Processes at the time
you run out, which should show which process is using the memory.

Thanks, David H

Thanks for your reply and sorry for my delay.

mysql> select count(*) from dim_metrics ;
+----------+
| count(*) |
+----------+
| 238340 |
+----------+
1 row in set (1.30 sec)

mysql> select count(*) from ft_metric_host;
+-----------+
| count(*) |
+-----------+
| 351585450 |
+-----------+
1 row in set (7.69 sec)

mysql> desc dim_metrics;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| ID | int(30) | YES | | NULL | |
| METRIC_ID | int(10) | YES | | NULL | |
| METRIC_GUID | varchar(32) | YES | | NULL | |
| COLUMN_NAME | varchar(64) | YES | | NULL | |
| COLUMN_LABEL | varchar(64) | YES | | NULL | |
| METRIC_NAME | varchar(64) | YES | | NULL | |
| METRIC_LABEL | varchar(64) | YES | | NULL | |
| KEY_VALUE | varchar(512) | YES | | NULL | |
| TARGET_TYPE | varchar(64) | YES | | NULL | |
| ALL_CATEGORY | varchar(20) | YES | | NULL | |
| ALL_CATEGORY_ID | int(22) | YES | | NULL | |
| TARGET_TYPE_ID | int(22) | YES | | NULL | |
| METRIC_LEVEL_ID | int(10) | YES | | NULL | |
| IS_PUBLICRULE | int(1) | YES | | NULL | |
| IS_CURRENT | int(1) | YES | | NULL | |
| START_DATE | datetime | YES | | NULL | |
| END_DATE | datetime | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
17 rows in set (0.01 sec)

mysql> desc ft_metric_host;
+---------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+-------+
| ID | int(30) | YES | | NULL | |
| DATE_ID | int(10) | YES | | NULL | |
| TIME_ID | int(10) | YES | | NULL | |
| TARGET_ID | int(10) | YES | | NULL | |
| METRIC_ID | int(30) | YES | | NULL | |
| VALUE_AVERAGE | float | YES | | NULL | |
| VALUE_MAXIMUM | float | YES | | NULL | |
| VALUE_MINIMUM | float | YES | | NULL | |
| VALUE_SDEV | float | YES | | NULL | |
+---------------+---------+------+-----+---------+-------+
9 rows in set (0.00 sec)

egrep "Pm|Um|MaxOut|NumBlocksPct" /usr/local/Calpont/etc/Calpont.xml
<NumBlocksPct>66</NumBlocksPct>
<PmMaxMemorySmallSide>64M</PmMaxMemorySmallSide>
<UmMaxMemorySmallSide>4096M</UmMaxMemorySmallSide>
<TotalUmMaxMemorySmallSide>8G</TotalUmMaxMemorySmallSide>
less than MaxOutstandingRequests. Otherwise, default value 1 is used. -->
<MaxOutstandingRequests>5</MaxOutstandingRequests> <!-- Number of outstanding extents -->


free -g
total used free shared buffers cached
Mem: 141 51 90 0 0 41
-/+ buffers/cache: 9 132
Swap: 7 0 7


top - 09:43:42 up 9 days, 23:39, 2 users, load average: 0.08, 0.38, 0.20
Tasks: 446 total, 1 running, 445 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.1%us, 0.1%sy, 0.0%ni, 99.8%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 148424244k total, 53982960k used, 94441284k free, 468568k buffers
Swap: 8385920k total, 0k used, 8385920k free, 43798984k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
9369 oracle 15 0 12.1g 5.0g 5.0g S 0.0 3.5 107:42.67 oracle
9361 oracle 15 0 12.1g 5.0g 5.0g S 0.0 3.5 107:55.98 oracle
9365 oracle 16 0 12.1g 4.9g 4.9g S 0.0 3.5 107:56.65 oracle
16842 mysql 15 0 7977m 4.6g 4608 S 0.0 3.2 154:06.12 mysqld
28067 root 18 -1 3319m 1.7g 6940 S 0.0 1.2 0:16.43 PrimProc
15269 oracle 15 0 12.0g 667m 661m S 0.0 0.5 1:15.62 oracle
15277 oracle 15 0 12.0g 650m 644m S 0.0 0.4 1:21.35 oracle
15297 oracle 15 0 12.0g 642m 637m S 0.0 0.4 0:46.33 oracle
15261 oracle 15 0 12.0g 632m 627m S 0.0 0.4 0:54.33 oracle
15285 oracle 15 0 12.0g 632m 627m S 0.0 0.4 0:53.54 oracle
15253 oracle 15 0 12.0g 627m 622m S 0.0 0.4 0:57.20 oracle
15289 oracle 15 0 12.0g 627m 622m S 0.0 0.4 0:53.03 oracle
15265 oracle 15 0 12.0g 625m 619m S 0.0 0.4 0:48.80 oracle
15281 oracle 15 0 12.0g 624m 619m S 0.0 0.4 0:58.26 oracle
15301 oracle 15 0 12.0g 623m 618m S 0.0 0.4 1:58.49 oracle
15321 oracle 15 0 12.0g 621m 616m S 0.0 0.4 0:53.89 oracle
15273 oracle 15 0 12.0g 618m 613m S 0.0 0.4 1:00.81 oracle
15293 oracle 15 0 12.0g 618m 613m S 0.0 0.4 0:54.20 oracle
15305 oracle 15 0 12.0g 618m 613m S 0.0 0.4 0:55.93 oracle
15337 oracle 15 0 12.0g 617m 611m S 0.0 0.4 0:51.66 oracle
15313 oracle 15 0 12.0g 616m 611m S 0.0 0.4 0:49.16 oracle
15317 oracle 15 0 12.0g 616m 611m S 0.0 0.4 0:53.34 oracle
15309 oracle 15 0 12.0g 616m 611m S 0.0 0.4 0:46.53 oracle
15329 oracle 15 0 12.0g 615m 610m S 0.0 0.4 0:55.77 oracle

thanks

The data you have doesn't look large enough to cause the problem you are describing. Oracle is using a lot of memory, but it still shows plenty available for other users like
our InfiniDB processes.

On the 'top' command, is this the output of 'top' on the system that is idle or when you run the query and you are out of memory? If the former, we would really be interested in seeing top, sorted by memory users, at the time the problem occurred. We are assuming it's one of our InfiniDB processes, ExeMgr or PrimProc, that is using up the memory. If we can determine that, it would help figure out which, if any, settings might need to be decreased.

Also when the problem occurs, can you check the InfiniDB log files for any related
errors. Log files are located /var/log/Calpont/

And is you wanted to tweak down on settings, you could start with:
<NumBlocksPct>66</NumBlocksPct>

Here is how to change that setting to 50% usage.

1. stop infinidb service
2. /usr/local/Calpont/bin/setConfig DBBC NumBlocksPct 50
3. start infinidb service

If you have the permission, you could shutdown Oracle and re-run the query to see if
this frees up enough memory as a test.

Thanks, David H

Thanks a lot.

The Oracle database is being moved to another machine. After that I will do the test again.