5 added 1792 characters in body
source | link

I could reproduce the issue with this new script. Handler read next counter went up like crazy and it's been stuck on the query for more than 10 minutes now.

I will installsaw that while this new script and testis not normal, this doesn't necessarily crash the server. What does is the rise of the semaphore wait time counter: see the rise causing a crash (the 11pm restart of mysql was mine):

Correlation between semaphore wait time counter and server crashes

For now this counter is 0 so I'm not risking any mysql crash. However it is stuck, in orderand thanks to @jkavalik suggestion, I could see what is the query causing it:

update trips t
set stops_taken = (
  select md5(group_concat(stop_iid))
  from stop_times
  where trip_iid = t.id
  group by trip_iid
  order by stop_sequence asc
);

So that's indeed the md5/stop sequence generation.

What puzzles me is if this can cause massive fullI'm doing the same query without updating the trips table scans and huge performance drops, as described initiallyit goes nicely:

mysql> select t.id, tt.*
    -> from
    -> trips t
    -> left join (
    ->   select trip_iid, md5(group_concat(stop_iid))
    ->   from stop_times
    ->   group by trip_iid
    ->   order by stop_sequence asc
    -> ) tt on tt.trip_iid = t.id;
+-------+----------+----------------------------------+
| id    | trip_iid | md5(group_concat(stop_iid))      |
+-------+----------+----------------------------------+
|     1 |        1 | 06e9d5dbd52703144d4244c6720cdeb2 |
|     2 |        2 | 6e6b7899a4668bf0a0c88e07d9adc337 |
|     3 |        3 | 6e6b7899a4668bf0a0c88e07d9adc337 |
...
| 11726 |    11726 | adebbf5bb888e38fb55a97b5a9c83763 |
| 11728 |    11728 | 1b76be03f1202110f62c74e6ddac2119 |
+-------+----------+----------------------------------+
11729 rows in set (2.79 sec)

Finished in this questionless than 3 seconds. I'll edit this paragraph with the resultsThe other query is still stuck, now it's been stuck for 15 minutes.

I will install this new script and test it, in order to see if this can cause massive full table scans and huge performance drops, as described initially in this question. I'll edit this paragraph with the results.


I could reproduce the issue with this new script. Handler read next counter went up like crazy and it's been stuck on the query for more than 10 minutes now.

I saw that while this is not normal, this doesn't necessarily crash the server. What does is the rise of the semaphore wait time counter: see the rise causing a crash (the 11pm restart of mysql was mine):

Correlation between semaphore wait time counter and server crashes

For now this counter is 0 so I'm not risking any mysql crash. However it is stuck, and thanks to @jkavalik suggestion, I could see what is the query causing it:

update trips t
set stops_taken = (
  select md5(group_concat(stop_iid))
  from stop_times
  where trip_iid = t.id
  group by trip_iid
  order by stop_sequence asc
);

So that's indeed the md5/stop sequence generation.

What puzzles me is if I'm doing the same query without updating the trips table, it goes nicely:

mysql> select t.id, tt.*
    -> from
    -> trips t
    -> left join (
    ->   select trip_iid, md5(group_concat(stop_iid))
    ->   from stop_times
    ->   group by trip_iid
    ->   order by stop_sequence asc
    -> ) tt on tt.trip_iid = t.id;
+-------+----------+----------------------------------+
| id    | trip_iid | md5(group_concat(stop_iid))      |
+-------+----------+----------------------------------+
|     1 |        1 | 06e9d5dbd52703144d4244c6720cdeb2 |
|     2 |        2 | 6e6b7899a4668bf0a0c88e07d9adc337 |
|     3 |        3 | 6e6b7899a4668bf0a0c88e07d9adc337 |
...
| 11726 |    11726 | adebbf5bb888e38fb55a97b5a9c83763 |
| 11728 |    11728 | 1b76be03f1202110f62c74e6ddac2119 |
+-------+----------+----------------------------------+
11729 rows in set (2.79 sec)

Finished in less than 3 seconds. The other query is still stuck, now it's been stuck for 15 minutes.

4 added 1813 characters in body
source | link

I have done some benchmarking to improve the query: create an external (non temp) table to store a mapping between trip IDs and trip types (group_concat of used stops)

  1. Store trip ID + trip type as is, in a text field (sequence of stops: 1,543,343,394,10,...)
  2. Store trip ID + trip type as md5 of the stops sequence, in a char(32)

When used by the application, the trip typed is used in a group by statement. Joining the external table and grouping made the performance drop by a factor of 5x to 10x.

I have successfully optimized the trip type generation though, by computing the stop sequence only once:

  1. Add a column stops_taken to trips as a text or a char(32) (depending on the risk of md5 collision)
  2. Fill this column
  3. Fill a temp table with these values to generate IDs (auto increment)
  4. Put back the trip type ID in the trips table.

Here's the new script:

update trips t
set stops_taken = (
  select md5(group_concat(stop_iid))
  from stop_times
  where trip_iid = t.id
  group by trip_iid
  order by stop_sequence asc
);
create temporary table trip_types (
    id int unsigned not null auto_increment primary key,
    stops_taken char(32) not null,
    key st (stops_taken)
);
insert into trip_types(stops_taken) select distinct stops_taken from trips;
update trips t join trip_types tt on tt.stops_taken = t.stops_taken
set t.trip_type = tt.id;

On one DB, this reduces the execution time from ~32-40s to ~23s. That's a nice ~35% drop.

I will install this new script and test it, in order to see if this can cause massive full table scans and huge performance drops, as described initially in this question. I'll edit this paragraph with the results.


I have done some benchmarking to improve the query: create an external (non temp) table to store a mapping between trip IDs and trip types (group_concat of used stops)

  1. Store trip ID + trip type as is, in a text field (sequence of stops: 1,543,343,394,10,...)
  2. Store trip ID + trip type as md5 of the stops sequence, in a char(32)

When used by the application, the trip typed is used in a group by statement. Joining the external table and grouping made the performance drop by a factor of 5x to 10x.

I have successfully optimized the trip type generation though, by computing the stop sequence only once:

  1. Add a column stops_taken to trips as a text or a char(32) (depending on the risk of md5 collision)
  2. Fill this column
  3. Fill a temp table with these values to generate IDs (auto increment)
  4. Put back the trip type ID in the trips table.

Here's the new script:

update trips t
set stops_taken = (
  select md5(group_concat(stop_iid))
  from stop_times
  where trip_iid = t.id
  group by trip_iid
  order by stop_sequence asc
);
create temporary table trip_types (
    id int unsigned not null auto_increment primary key,
    stops_taken char(32) not null,
    key st (stops_taken)
);
insert into trip_types(stops_taken) select distinct stops_taken from trips;
update trips t join trip_types tt on tt.stops_taken = t.stops_taken
set t.trip_type = tt.id;

On one DB, this reduces the execution time from ~32-40s to ~23s. That's a nice ~35% drop.

I will install this new script and test it, in order to see if this can cause massive full table scans and huge performance drops, as described initially in this question. I'll edit this paragraph with the results.

    Tweeted twitter.com/StackDBAs/status/653931847710343168
3 added info about the trips table and the usefulness of stop_sequence
source | link

Clarifications about the table schema / application flow.

Three tables are chained: trips, stop_times and stops.

  • a stop is basically an ID and lat/lon
  • a stop_time is a stop + a timestamp, say 11am
  • a trip is a collection of stop_times, chained by stop_sequence

So if I have 4 stops: A, B, C and D, I could have many stop_times:

  • stop_time #1: A at 11am
  • stop_time #2: A at 11:05am
  • stop_time #3: A at 11:10am
  • stop_time #4: B at 11:06am
  • stop_time #5: C at 11:07am
  • stop_time #6: D at 11:08am

I could have several trips like this:

  • trip #1: uses stop_times #1, #4, #5 and #6 (stops ABCD starting at 11am)
  • trip #2: uses stop_times #1 and #6 (stops AD starting at 11am)
  • trip #3: uses stop_times #2, #7, #8... (stops ABCD starting at 11:05am)
  • etc

I want to identify identical trips in terms of stops taken. Here, trips #1 and #3 share the same stop sequence: ABCD. Trip #2 uses the same path from A to D, however it's not the same trip type because it doesn't make the stops at B and C.


Clarifications about the table schema / application flow.

Three tables are chained: trips, stop_times and stops.

  • a stop is basically an ID and lat/lon
  • a stop_time is a stop + a timestamp, say 11am
  • a trip is a collection of stop_times, chained by stop_sequence

So if I have 4 stops: A, B, C and D, I could have many stop_times:

  • stop_time #1: A at 11am
  • stop_time #2: A at 11:05am
  • stop_time #3: A at 11:10am
  • stop_time #4: B at 11:06am
  • stop_time #5: C at 11:07am
  • stop_time #6: D at 11:08am

I could have several trips like this:

  • trip #1: uses stop_times #1, #4, #5 and #6 (stops ABCD starting at 11am)
  • trip #2: uses stop_times #1 and #6 (stops AD starting at 11am)
  • trip #3: uses stop_times #2, #7, #8... (stops ABCD starting at 11:05am)
  • etc

I want to identify identical trips in terms of stops taken. Here, trips #1 and #3 share the same stop sequence: ABCD. Trip #2 uses the same path from A to D, however it's not the same trip type because it doesn't make the stops at B and C.

2 fixed the wrong query picked and analysed
source | link
1
source | link