Read Comments (0)
Tags:
It is difficult at times to correlate the SQL from the general_query.log file with the optimizer logs in the Infobright server log, bh.err by default. And it is impossible if general query logging is not enabled in the instance. It would be helpful if the SQL statement was also written to the bh.err file.
The challenge is that queries are parsed (validated) by the MySQL parser before they flow to the Infobright optimizer. This is the part that is written to the general query log, general_query.log by default, in an Infobright installation. The Infobright optimizer works dynamically, potentially adjusting its plan based on intermiate results and statistics, so that is logged in real-time to the Infobright server log. Obviously, these two events are sequential in nature (for a single query) so they are not written at the same time ... but they are still written to two diferent logs.Why not capture them in a single log file?
Here's what I've done to address this. Write a simple script that does the following:
[root@localhost data]# nohup tail -f general_query.log >> combined.log &
[root@localhost data]# nohup tail -f bh.err >> combined.log &
Execute the script after the engine has been started. Log in and run some queries. Then check the combined.log file.
[root@localhost data]# more combined.log
100607 9:58:17 11 Connect root@localhost on carsales
11 Query select @@version_comment limit 1
100607 9:58:27 11 Query select * from dim_dates
2010-06-07 09:58:27 [11] [1002M/712M, 1/0.89] T:-1 = TABLE_ALIAS(T:0,"dim_dates")
T:-2 = TMP_TABLE(T:-1)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:0))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,LIST,"trans_date","ALL")
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:1))
A:-2 = T:-2.ADD_COLUMN(VC:-2.1,LIST,"trans_year","ALL")
VC:-2.2 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:2))
A:-3 = T:-2.ADD_COLUMN(VC:-2.2,LIST,"trans_month","ALL")
VC:-2.3 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:3))
A:-4 = T:-2.ADD_COLUMN(VC:-2.3,LIST,"trans_day","ALL")
VC:-2.4 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:4))
A:-5 = T:-2.ADD_COLUMN(VC:-2.4,LIST,"trans_time","ALL")
RESULT(T:-2)
2010-06-07 09:58:27 [11] [1002M/710M, 1/0.89] Result: 4017 rows.
2010-06-07 09:58:27 [11] [1002M/710M, 1/0.89] Total data packs actually loaded (approx.): 5
2010-06-07 09:58:27 [11] [1002M/710M, 1/0.89] ----------------------------------------------------------------------------
100607 9:58:29 11 Quit
[root@localhost data]#
Notice that entries from both log files are written to a single file.
CAVEATS:
- this is a Linux example
- general query logging must be enabled
- this script must be started after the Infbright server has been started (and should be interrupted/aborted when the server is stopped)
- assumes default naming conventions - change/write your scripts accordingly
