Empty result file
marcschroeter opened this issue · 8 comments
We have 15 EC2 instances in the same region i deplyoed the CF stack. The before336hour-with60min.csv file shows many entries but the results file is completly empty. i checked the cloudwatch logfiles, but i couldnt find any errors.
Hi @marcschroeter,
I've got the same problem did you found a solution since?
Is there a specific scenario that you can share to replicate as the retest in us-east-1 region had the results_rightsizingsozupxnb.csv with data.
Analysis: Unable to Replicate the reported scenario.
Retest Date: 07/03/2017
Retest Region: us-east-1
The Cloudformation template was deployed in us-east-1 region and a bucket with following list of files was generated with data.
a. results_rightsizingsozupxnb.csv
b. ec2pricelist.csv
c. 2018-07-03-before336hour-with60min.csv.gz
Same problem here, i think is something about the "Access CIDR Block" field, where can i see some examples of this input ? Because i´m not sure if i am filling it whit the correct data.
Cost Optimization parameters used for trying to replicate the scenario is attached.
Analysis: Unable to Replicate the reported scenario.
Retest Date: 07/03/2018
Retest Region: us-east-1
The Cloudformation template was deployed in us-east-1 region and a bucket with following list of files was generated with data.
a. results_rightsizingsozupxnb.csv
b. ec2pricelist.csv
c. 2018-07-03-before336hour-with60min.csv.gz
Guys, any update here. One of the issues we noticed that was causing similar behavior:
issue
Upon further investigation we found the issue to be with following lines in the sql query:
https://github.com/awslabs/cost-optimization-ec2-right-sizing/blob/1db8f6558e1e245108267321abcc40f438b31be4/run-rightsizing-redshift.py#L292
https://github.com/awslabs/cost-optimization-ec2-right-sizing/blob/1db8f6558e1e245108267321abcc40f438b31be4/run-rightsizing-redshift.py#L300
fix
change these lines to:
ls_gen_list_sql += " max(to_number(trim(both ' ' from diskreadops), '9999999999999D99999999')/60+to_number(trim(both ' ' from diskwriteops),'9999999999999D99999999')/60) as maxiops, "
reason
the reason for numeric overflow error was due to mismatch with the data-type.
next steps
would it be possible for you guys to make these changes and test it with your setup?
you can chose not to terminate resource at deployment. this will leave ec2 and redshift cluster after initial deployment. you can then ssh into the box and edit the /tmp/run-rightsizing-redshift.py with the recommended change and run it again
please try our updated v2.4 release which addresses this issue
@gsingh04 Same problem here, I am getting empty result. In my file above lines are commented.
ls_gen_list_sql += " from (select instanceid, instancetags, instanceType, az, max(to_number(trim(both ' ' from CPUUtilization),'9999999D99999999')) as maxcpu, "
`#ls_gen_list_sql += " max(to_number(trim(both ' ' from diskreadops), '9999999999999D99999999')/60+to_number(trim(both ' ' from diskwriteops),'9999999999999D99999999')/60) as maxiops, "`
`ls_gen_list_sql += " max(CAST(trim(both ' ' from diskreadops) as decimal(38,10))/60+CAST(trim(both ' ' from diskwriteops) as decimal(38,10))/60) as maxiops, "
ls_gen_list_sql += " max((to_number(trim(both ' ' from networkin),'9999999999999D99999999')/60/1024/1024)*8+(to_number(trim(both ' ' from networkout),'9999999999999D99999999')/60/1024/1024)*8) as maxnetwork "`
ls_gen_list_sql += " from " + cw_tablename
#ls_gen_list_sql += " where accountid like '%" + ACCOUNT_ID + "%' "
ls_gen_list_sql += " where accountid not like '%accountId%' "
ls_gen_list_sql += " group by instanceid, instancetags, instanceType, az) a, " + pricelist_table + " b "
ls_gen_list_sql += " where a.instanceid in (select instanceid from (select instanceid,max(maxcpu) as topcpu from "
ls_gen_list_sql += "(select instanceid, instancetags, instanceType, az, max(to_number(trim(both ' ' from CPUUtilization),'9999999D99999999')) as maxcpu, "
#ls_gen_list_sql += " max(to_number(trim(both ' ' from diskreadops), '9999999999999D99999999')/60+to_number(trim(both ' ' from diskwriteops),'9999999999999D99999999')/60) as maxiops, "
`ls_gen_list_sql += " max(CAST(trim(both ' ' from diskreadops) as decimal(38,10))/60+CAST(trim(both ' ' from diskwriteops) as decimal(38,10))/60) as maxiops, "
`
In current version these two lines are in 292 and 301. I have uncommented to test but got below error:
2019-09-17 11:25:07,913 INFO Finish to import the EC2 pricelist to Redshift table: pricelistdeqkfsat
2019-09-17 11:25:07,913 INFO Analyzing the instances need to be resized Traceback (most recent call last):
File "/tmp/run-rightsizing-redshift.py", line 449, in <module> ls_temp_table = right_sizing(conn, ls_pricelist_tabname, ls_cw_tabname)
File "/tmp/run-rightsizing-redshift.py", line 313, in right_sizing execute_dml_ddl(db_conn, ls_gen_list_sql)
File "/tmp/run-rightsizing-redshift.py", line 101, in execute_dml_ddl cur_dml_ddl.execute(sql_stat)
psycopg2.ProgrammingError: column reference "maxiops" is ambiguous