ERROR: ERROR 3587: Insufficient resources to execute plan on pool general

AxelK

Just downloaded "HP Vertica Analytic Database Server" (to be exact - vertica-7.1.2-0.x86_64.RHEL5). Played a little. M-m-m, strange. Trying to understand Vertica's logic of contraints handling, faced with problem:

Simple sequence causes an issue reported in subject:

 

create table Employees (EmployeeID int primary key, FirstName varchar(30), LastName varchar(30), SSN varchar(30) unique);
create table HealthPlans (HealthPlanID int primary key, EmployeeID int NOT NULL references Employees (EmployeeID), name varchar(30));
CREATE PROJECTION EMP_HEALTH (EmployeeID, FirstName, LastName, SSN)
AS (SELECT e.EmployeeID, e.FirstName, e.LastName, e.SSN
FROM Employees e, HealthPlans hp
WHERE e.EmployeeID = hp.EmployeeID);

 

insert into Employees (EmployeeID, FirstName, LastName, SSN) values (1, 'John', 'Doe', '1111');
insert into Employees (EmployeeID, FirstName, LastName, SSN) values (3, 'Richard', 'Roe', '3333');
commit;
insert into HealthPlans (HealthPlanID, EmployeeID) values (1, 1);
insert into HealthPlans (HealthPlanID, EmployeeID) values (3, 3);
commit;

select * from EMP_HEALTH;

/*As expectable, 2 rows*/

delete from HealthPlans where EmployeeID = 1;

/*delete from child table - OK*/

delete from Employees where EmployeeID = 1;

/*

ERROR: ERROR 3587: Insufficient resources to execute plan on pool
general [Request Too Large:Memory(KB) Exceeded: Requested =
594635, Free = 512827 (Limit = 512827, Used = 0)] Error Code:
3587
Query = delete from Employees where EmployeeID = 1

*/

 

All settings are from default installation (VM was downloaded). Default settings prevent me to add 4 and delete 1-2 records??? 

 

Thanks in advance,

  Axel

SruthiA

Hi,

 

   Can you check  settings for the following parameters in  your nodes

 

/sys/kernel/mm/redhat_transparent_hugepage/defrag

/sys/kernel/mm/redhat_transparent_hugepage/khugepaged/defrag

 

You can find detailed information about those settings in the following URL

 

http://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/InstallationGuide/BeforeYouInstall/transparenthugepages.htm?Highlight=hugepage

 

 

Thanks,

 Sruthi

AxelK

[dbadmin@localhost ~]$ ls /sys/kernel/mm/transparent_hugepage/                  
defrag  enabled  khugepaged  use_zero_page
[dbadmin@localhost ~]$ cat /sys/kernel/mm/transparent_hugepage/enabled          
always madvise [never]
[dbadmin@localhost ~]$ cat /sys/kernel/mm/transparent_hugepage/defrag
always madvise [never]
[dbadmin@localhost ~]$ cat /sys/kernel/mm/transparent_hugepage/khugepaged/defrag

1

 

Seems ok.

 

Thank you,

  Axel

SruthiA

Hi,

 

 

    Can you share me the output of following queries

 

1. select * from host_resources;

2. select * from resource_pools;

3. select * from resource_pool_status;

 

 

Sruthi

AxelK

select * from host_resources;

 

host_name    open_files_limit    threads_limit    core_file_limit_max_size_bytes    processor_count    processor_core_count    processor_description    opened_file_count    opened_socket_count    opened_nonfile_nonsocket_count    total_memory_bytes    total_memory_free_bytes    total_buffer_memory_bytes    total_memory_cache_bytes    total_swap_memory_bytes    total_swap_memory_free_bytes    disk_space_free_mb    disk_space_used_mb    disk_space_total_mb
127.0.0.1    65536               4096             0                                 2                  2                       Common KVM processor     7                    11                     6                                 1042182144            68186112                   50089984                     437104640                   2164256768                 1991012352                      40330                 7412                  47743              

select * from resource_pools;

 

pool_id              name        is_internal    memorysize    maxmemorysize    executionparallelism    priority    runtimepriority    runtimeprioritythreshold    queuetimeout    plannedconcurrency    maxconcurrency    runtimecap    singleinitiator    cpuaffinityset    cpuaffinitymode    cascadeto
45035996273720528    general     1                            Special: 95%     AUTO                    0           MEDIUM             2                           300             AUTO                                                  0                                    ANY                         
45035996273720530    sysquery    1              64M                            AUTO                    110         HIGH               0                           300             AUTO                                                  0                                    ANY                         
45035996273720532    sysdata     1              100M          10%                                                                                                                                                                                                                                        
45035996273720534    wosdata     1              0%            25%                                                                                                                 AUTO                                                                                                                   
45035996273720536    tm          1              200M                           AUTO                    105         MEDIUM             60                          300             AUTO                  3                               1                                    ANY                         
45035996273720538    refresh     1              0%                             AUTO                    -10         MEDIUM             60                          300             AUTO                                                  1                                    ANY                         
45035996273720540    recovery    1              0%                             AUTO                    107         MEDIUM             60                          300             AUTO                  1                               1                                    ANY                         
45035996273720542    dbd         1              0%                             AUTO                    0           MEDIUM             0                           0               AUTO                                                  1                                    ANY                         
45035996273720624    jvm         1              0%            10%              AUTO                    0           MEDIUM             2                           300             AUTO                                                  0                                    ANY                         

select * from resource_pool_status;

 

node_name          pool_oid             pool_name    is_internal    memory_size_kb    memory_size_actual_kb    memory_inuse_kb    general_memory_borrowed_kb    queueing_threshold_kb    max_memory_size_kb    running_query_count    planned_concurrency    max_concurrency    is_standalone    queue_timeout_in_seconds    execution_parallelism    priority    runtime_priority    runtime_priority_threshold    single_initiator    query_budget_kb    cpu_affinity_set    cpu_affinity_mask    cpu_affinity_mode
v_test_node0001    45035996273720528    general      1              512827            512827                   0                  0                             487185                   512827                0                      4                                         1                300                         AUTO                     0           MEDIUM              2                             false               121796                                 3                    ANY              
v_test_node0001    45035996273720530    sysquery     1              65536             65536                    0                  0                             549444                   578363                0                      4                                         0                300                         AUTO                     110         HIGH                0                             false               16384                                  3                    ANY              
v_test_node0001    45035996273720532    sysdata      1              87018             87018                    8192               0                             82667                    87018                 0                      1                      0                  1                0                           AUTO                     0           HIGH                0                             false                                                      3                    ANY              
v_test_node0001    45035996273720534    wosdata      1              0                 0                        0                  0                             206667                   217545                0                      2                      0                  0                0                           AUTO                     0           HIGH                0                             false                                                      3                    ANY              
v_test_node0001    45035996273720536    tm           1              204800            204800                   0                  0                             681745                   717627                0                      2                      3                  0                300                         AUTO                     105         MEDIUM              60                            true                102400                                 3                    ANY              
v_test_node0001    45035996273720538    refresh      1              0                 0                        0                  0                             487185                   512827                0                      4                                         0                300                         AUTO                     -10         MEDIUM              60                            true                121796                                 3                    ANY              
v_test_node0001    45035996273720540    recovery     1              0                 0                        0                  0                             487185                   512827                0                      4                      1                  0                300                         AUTO                     107         MEDIUM              60                            true                121796                                 3                    ANY              
v_test_node0001    45035996273720542    dbd          1              0                 0                        0                  0                             487185                   512827                0                      4                                         0                0                           AUTO                     0           MEDIUM              0                             true                121796                                 3                    ANY              
v_test_node0001    45035996273720624    jvm          1              0                 0                        0                  0                             82667                    87018                 0                      4                                         0                300                         AUTO                     0           MEDIUM              2                             false               20666                                  3                    ANY              

Again, all of parameters left unchanged - as they were configured in originally downloaded VM.

 

Thanks,

  Axel

 

SruthiA

Hi,

 

  Vertica recommends a minimum of 4GB of memory per core. i see that you have 2 cores and just 1 GB of memory. Memory allocation is very low. You need to have 2*4B = 8GB of memory. I request you to provided atleast 8GB of memory  to your cluster to solve this issue

 

 

Sruthi

 

 

AxelK

SruthiA, have you read my messages?

 

"All settings are from default installation (VM was downloaded). Default settings prevent me to add 4 and delete 1-2 records??? "

 

"Again, all of parameters left unchanged - as they were configured in originally downloaded VM."

 

I'm talking about preconfigured VM which is distributed to clients. For sure I have increased already RAM size to continue my checks on another VM instance - but still, the question remains: is it OK when failure appears on just two-table-four-rows db with 512827 KB (!) of memory available for query processing? That affects on level of confidence in engine drastically...

 

Bye, Axel