Table of Contents
- Optimizing Queue Configuration
- Monitoring MicroStrategy Workload within Amazon Redshift
- Query Prioritization
Amazon Redshift offers a feature called WLM (WorkLoad Management). This feature provides the ability to create multiple query queues and queries are routed to an appropriate queue at runtime based on their user group or query group. Each queue is defined by concurrency level, user groups, query groups, wild cards, memory percent to be used, and query timeout. Out of the box, Redshift has one queue for a “superuser” group with concurrency level defined as 1 and a “default” queue with concurrency of 5 where all other user groups’ queries are queued.
One can customize Redshift workload management using the Amazon Redshift management console, CLI or API.
Optimizing Queue Configuration
The WLM feature allows up to 8 separate workloads. You can modify WLM to an optimal queuing configuration based on history stats. Define separate queues for ETL user group and MSTR user group; then define concurrency level and memory allocation for each queue.
The total number of concurrent level should be less than 15. The recommended concurrent level for reporting/querying queue is 5-7. While increasing concurrency allows more queries to run, each query will get a smaller share of the memory allocated to its queue (unless you increase it). You may find that by increasing concurrency, some queries must use temporary disk storage to complete, which is also sub-optimal. For the query slots in each queue, the amount of resources will be allocated evenly for each slot. If more resources are needed for a specific report/query slot, add the following command to allocate more resources before running the report:
set wlm_query_slot_count to 3;
Note that the number used in the above command should be lower than the concurrency level in the queue.
Monitoring of Strategy Workload within Amazon Redshift
Redshift tracks all system activity and SQL statement execution statistics in system tables. These are global to all databases. One of the most commonly used tables to look at query history is STL_QUERY, which is utilized in the procedure below to show how to track specific Strategy queries using labels and hints.
The Strategy query labels and hints can be used to identify the Strategy submitted SQL statements within Redshift system tables.
Use the /*(myquerylabelhint)*/ hint to pass a user-defined label to a Redshift query. You can then identify those queries later for profiling and debugging purposes. This hint can be sent to the Redshift server through the use of the following report/project level Strategy VLDB property. Strategy hints would only label SELECT, Create table as, and insert select statements; statements like parameterized inserts (insert into table values (?,?) ) would have no labels hence, are not recommended for multi-pass SQL reports which use parameterized inserts, explicit create and insert statements.
X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; |
X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; |
Strategy query labels are used to set Redshift query group for queue assignment however, they can also be used for Strategy SQL identification in Redshift ecosystem. Unlike Strategy hints, query labels can be used with all SQL statement types and hence, recommended for multi pass SQL reports. When the label of a query is stored in the system view stl_query, it is truncated to 15 characters (30 characters are stored in all other system tables). For this reason one has to be cautious when choosing the value for query label.
You can set the following as a Report Pre-statement:
set query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’
This will collect information on the server side about various variables like Project name, report name, user, etc.
To clean up the query group and release resources use the Clean-up Post statement:
reset query_group;
Strategy allows the use of wildcards that are replaced by values retrieved at a report’s run time as shown in the Pre/Post Statements.
X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; |
X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; |
X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; |
Example:
Strategy generated SQL pass with VLDB Property SQL Hint = “”
select a11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
a11.ITEM_ID ITEM_ID,
a11.ITEM_NAME ITEM_NAME,
a12.CATEGORY_ID CATEGORY_ID,
a13.CATEGORY_DESC CATEGORY_DESC,
a11.UNIT_COST WJXBFS1,
a11.UNIT_PRICE WJXBFS2,
(a11.UNIT_PRICE - a11.UNIT_COST) WJXBFS3
from items a11
join subcat a12
on (a11.SUBCAT_ID = a12.SUBCAT_ID)
join cat a13
on (a12.CATEGORY_ID = a13.CATEGORY_ID)
VLDB Property SQL Hint = “/*(MSTR_!r)*/”
select /*(MSTR_D1AE5A6B11D5C4D04C200E8820504F4F)*/ a11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
a11.ITEM_ID ITEM_ID,
a11.ITEM_NAME ITEM_NAME,
a12.CATEGORY_ID CATEGORY_ID,
a13.CATEGORY_DESC CATEGORY_DESC,
a11.UNIT_COST WJXBFS1,
a11.UNIT_PRICE WJXBFS2,
(a11.UNIT_PRICE - a11.UNIT_COST) WJXBFS3
from items a11
join subcat a12
on (a11.SUBCAT_ID = a12.SUBCAT_ID)
join cat a13
on (a12.CATEGORY_ID = a13.CATEGORY_ID)
VLDB Property Report Pre Statement = set query_group to 'MSTRReport=!o;'
set query_group to 'MSTRReport=Cost, Price, and Profit per Unit;'
select /*(MSTR_D1AE5A6B11D5C4D04C200E8820504F4F)*/ a11.SUBCAT_ID SUBCAT_ID,
a12.SUBCAT_DESC SUBCAT_DESC,
a11.ITEM_ID ITEM_ID,
a11.ITEM_NAME ITEM_NAME,
a12.CATEGORY_ID CATEGORY_ID,
a13.CATEGORY_DESC CATEGORY_DESC,
a11.UNIT_COST WJXBFS1,
a11.UNIT_PRICE WJXBFS2,
(a11.UNIT_PRICE - a11.UNIT_COST) WJXBFS3
from items a11
join subcat a12
on (a11.SUBCAT_ID = a12.SUBCAT_ID)
join cat a13
on (a12.CATEGORY_ID = a13.CATEGORY_ID)
Below is an example query using the Redshift table stl_wlm_query that gives the execution time, queue time, class’s queue time and the total time of the query spent on the Redshift side, and how you can use the label to track the query
select a.pid, max(a.label) as label,
sum(b.total_queue_time / 1000000.000) as queueTime,
sum(b.total_exec_time / 1000000.000) as ExecTime,
sum(case when b.service_class=2 then b.total_queue_time / 1000000.000 else 0.000 end) as Class2_QueueTime,
sum(case when b.service_class=3 then b.total_queue_time / 1000000.000 else 0.000 end) as Class3_QueueTime,
sum(case when b.service_class=2 then b.total_exec_time / 1000000.000 else 0.000 end) as Class2_ExecTime,
sum(case when b.service_class=3 then b.total_exec_time / 1000000.000 else 0.000 end) as Class3_ExecTime,
sum ((b.total_queue_time + b.total_exec_time ) / 1000000.000) as TotalTime,
min(b.exec_start_time) as starttime
from stl_wlm_query b
left join svl_qlog a
on a.query= b.query
and a.xid = b.xid
where b.exec_start_time >= '2013-01-15 16:47:57'
and b.exec_start_time <= '2013-01-30 20:00:00'
and a.label = 'MSTRReport=;'
group by a.pid
Query Prioritization
In general, there will be multiple applications submitting queries to Redshift in addition to Strategy. Redshift query group are leveraged to identify Strategy submitted SQL to Redshift, along with its assignment to the appropriate Redshift queue.
Redshift query group for a Strategy report is set and reset through the use of the following report level Strategy VLDB property.
X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; |
X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; |
X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; | X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM 1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550VLDB CategoryVLDB Property SettingValue Select / InsertSQL Hint/*MSTRProject=!p;MSTRReport=!o;MSTRUser=!u;MSTRJob=!j;*/VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR=!o;Project=!p;User=!u;Job=!j;’Pre/Post StatementsClean-up Post statementreset query_group;VLDB CategoryVLDB Property SettingValue E.g.Pre/Post StatementsReport Pre Statementset query_group to ‘MSTR_High=!o;’Pre/Post StatementsClean-up Post statementreset query_group; |
A Strategy report job can submit one or more queries to Redshift. In such case all queries for a Strategy report would be labeled with the same query group and thus, would be assigned to same queue on Redshift.
An example implementation of Strategy Redshift WLM:
- High Priority Strategy Reports are set with Report Pre Statement “MSTR_HIGH=!o;”, medium priority reports with “MSTR_MEDIUM=!o;” and low priority reports with “MSTR_LOW=!o;”.
- Redshift WLM queues are created and associated with corresponding query groups e.g. “MSTR_HIGH_QUEUE” queue is associated with “MSTR_HIGH=*; “ query group. (Where * is a Redshift wildcard)
Each Redshift queue is assigned with appropriate concurrency levels, memory percent to be used and query timeout.
