search

IntelLabs / Auto-Steer

Auto-Steer
MIT License
40 stars 9 forks source link

The program is stuck at `timed_result = connector.execute(sql_query)` when running `./benchmark/queries/tpch/10.sql`. #7

Open flecia-l opened 12 months ago

flecia-l commented 12 months ago

I ran python main.py --training --database mysql --benchmark ./benchmark/queries/tpch This is the output file of my program running:

Use CUDA: True
2023-11-30 01:17:18 INFO     Run AutoSteer's training mode
2023-11-30 01:17:18 INFO     Found the following SQL files: ['1.sql', '10.sql', '11.sql', '12.sql', '13.sql', '14.sql', '15.sql', '16.sql', '17.sql', '18.sql', '19.sql', '2.sql', '20.sql', '21.sql', '22.sql', '3.sql', '4.sql', '5.sql', '6.sql', '7.sql', '8.sql', '9.sql']
2023-11-30 01:17:18 INFO     run Q1.sql...
2023-11-30 01:17:18 INFO     Approximate query span for query: ./benchmark/queries/tpch/1.sql
2023-11-30 01:17:18 INFO     Default plan hash: #-2038567844489914072
2023-11-30 01:17:18 INFO     Failed query hash: #-7818100269337174304
2023-11-30 01:17:18 INFO     There are 1 alternative plans
2023-11-30 01:17:18 INFO     Found new hint-set:
2023-11-30 01:17:18 INFO     Found new hint-set: condition_fanout_filter
2023-11-30 01:17:18 INFO     Start exploring optimizer configs for query ./benchmark/queries/tpch/1.sql
2023-11-30 01:17:18 INFO     Run 1 different configs
2023-11-30 01:17:23 INFO     Serialize a new measurement for query ./benchmark/queries/tpch/1.sql and the disabled knobs [None]
2023-11-30 01:17:32 INFO     Serialize a new measurement for query ./benchmark/queries/tpch/1.sql and the disabled knobs [None]
2023-11-30 01:17:32 INFO     Enter next DP stage, execute for 1 hint sets/configurations
2023-11-30 01:17:40 INFO     Serialize a new measurement for query ./benchmark/queries/tpch/1.sql and the disabled knobs [condition_fanout_filter]
2023-11-30 01:17:48 INFO     Serialize a new measurement for query ./benchmark/queries/tpch/1.sql and the disabled knobs [condition_fanout_filter]
2023-11-30 01:17:48 INFO     Found 0 duplicated query plans!
2023-11-30 01:17:48 INFO     run Q10.sql...
2023-11-30 01:17:48 INFO     Approximate query span for query: ./benchmark/queries/tpch/10.sql
2023-11-30 01:17:48 INFO     Default plan hash: #-5227821245277693569
2023-11-30 01:17:48 INFO     Failed query hash: #-7818100269337174304
2023-11-30 01:17:48 INFO     There are 2 alternative plans
2023-11-30 01:17:48 INFO     Found new hint-set:
2023-11-30 01:17:48 INFO     Found new hint-set: block_nested_loop
2023-11-30 01:17:48 INFO     Found new hint-set: condition_fanout_filter
2023-11-30 01:17:48 INFO     Start exploring optimizer configs for query ./benchmark/queries/tpch/10.sql
2023-11-30 01:17:48 INFO     Run 1 different configs
2023-11-30 01:17:55 INFO     Serialize a new measurement for query ./benchmark/queries/tpch/10.sql and the disabled knobs [None]
2023-11-30 01:18:02 INFO     Serialize a new measurement for query ./benchmark/queries/tpch/10.sql and the disabled knobs [None]
2023-11-30 01:18:02 INFO     Enter next DP stage, execute for 2 hint sets/configurations

I found through debugging that the program gets stuck at dp_exploration.py in the execute_hint_set when running 10.sql, especially at timed_result = connector.execute(sql_query).

I don't know how to resolve or locate the error. Can you provide me with some suggestions?

christophanneser commented 12 months ago

Unfortunately, I cannot reproduce that error and query 10 executes without problems. Could you have a look in MySQL's log files or provide more information where the program is stuck?

flecia-l commented 12 months ago

I did not see any errors in the MySQL log files, but when I execute 1.sql, it only takes 30 seconds, and it has only 1 hint sets. When executing 10.sql, it took three hours and is still running. The final output is 2023-11-30 01:18:02 INFO Enter next DP stage, execute for 2 hint sets/configurations. Is this reasonable?