Poor man's query profiler - nova list

Tail the slow query logs while running a 'nova list' for each case (1 vs 5 instances).

NOTE: DATABASE_QUERY_LOGGING=True in devstack's local.conf so all queries are being logged in mariadb-slow.log, not just the slow logs.

# tail -f mariadb-slow.log > /home/diana/one_instance.sql
$ nova list
stop tailing slow logs
$ cat one_instance.sql | grep -v COMMIT | grep -v ROLLBACK | grep -v "#" | grep -v "SET time" | grep -v "SELECT 1;" > nova_list_one_instance.sql

# tail -f mariadb-slow.log > /home/diana/five_instances.sql
$ nova list
stop tailing slow logs
$ cat five_instances.sql | grep -v COMMIT | grep -v ROLLBACK | grep -v "#" | grep -v "SET time" | grep -v "SELECT 1;" > nova_list_five_instances.sql

How many lines are the SQL log files in each case (1 vs 5 instances)?

$ cat nova_list_one_instance.sql | wc -l
225
$ cat nova_list_five_instances.sql | wc -l
245

Approximately how many queries were issued during a 'nova list'?

$ grep FROM nova_list_five_instances.sql | cut -d ' ' -f 2 | sort | wc -l
79
$ grep FROM nova_list_one_instance.sql | cut -d ' ' -f 2 | sort | wc -l
65

Which tables were queried during a 'nova list'? Remember that other services were polling etc at the same time.

$ grep -i FROM nova_list_one_instance.sql | cut -d ' ' -f 2 | sort | uniq -c | sort

      1 block_device_mapping
      1 default_security_group
      1 endpoint
      1 instance_faults
      1 instance_metadata
      1 instances
      1 instance_system_metadata
      1 pci_devices
      1 ports
      1 securitygroups
      2 service
      2 service_provider
      2 subnets
      3 agents
      3 assignment
      3 group
      3 role
      4 services
      6 domain
      6 revocation_event
      6 token
      7 project
      7 user

$ grep -i FROM nova_list_five_instances.sql | cut -d ' ' -f 2 | sort | uniq -c | sort 

      1 default_security_group
      1 endpoint
      1 instance_faults
      1 instance_metadata
      1 migrations
      1 pci_devices
      1 ports
      1 reservations
      1 securitygroups
      2 compute_nodes
      2 instances
      2 instance_system_metadata
      2 service
      2 service_provider
      2 services
      3 assignment
      3 group
      3 role
      4 instance_extra
      5 block_device_mapping
      6 domain
      6 revocation_event
      6 token
      7 project
      7 subnets
      7 user

So not a crazy order of magnitude more queries for 1 instance vs 5 instances. That said, it looks like there are some queries being done in a loop - those could be optimized.