Multiple Web Search Errors

[Steven]

I'm seeing multiple issues with the https://crt.sh website today. 

The "Advanced..." link returns a 502 Bad Gateway error.

https://crt.sh/?a=1

https://crt.sh/?q=*.google.com fails with a 404 Not Found, even though this Google Groups page uses it.

https://crt.sh/?q=*.crt.sh succeeds.

https://crt.sh/?q=youtube.com and https://crt.sh/?q=%.crt.sh returned this error:

FATAL:  terminating connection due to conflict with recovery
DETAIL:  User query might have needed to see row versions that must be removed.
CONTEXT:  PL/pgSQL function web_apis(text,text[],text[]) line 3603 at FOR over EXECUTE statement
ERROR:  server conn crashed?
server closed the connection unexpectedly
 This probably means the server terminated abnormally
 before or while processing the request.

https://github.com/crtsh/certwatch_db/blob/7a4a71d7b9627af1b3df58cc63f385899f2e1480/web_apis.fnc#L3603

I did not test the Postgres connection.

[Rob Stradling]

Hi.  The postgres query planner has started doing some...interesting things.  For some reason it's now preferring a sequential scan on the "ca" table instead of an index scan on the primary key.  This was really, really hurting performance.

As a (hopefully) temporary measure to workaround this, I've reworked the main SQL queries used by https://crt.sh/ so that they don't access the "ca" table at all.  That's helping.  However, all but one of the front-end slave databases are offline at the moment, so we're not yet back to full strength.

I'll update this thread when there's more to report.

[Rob Stradling]

The front-ends have been running fine for the last couple of days.

[Aozhuo]

Hello, I found some problems with the https://crt.sh website today. 

When I search "%.google.com" exculde expired certificates (url: https://crt.sh/?Identity=%.google.com&exclude=expired), the result returned "None found".

Then I search some domains with a lot of certificates, like "%.microsoft.com","%.apple.com","%.att.com","%.amazon.com". The number of results returned is very small, significantly less than the amount of certificates that a domain should have.

The certificates obtained through Postgresql is normal.

在 2019年11月1日星期五 UTC+8下午8:24:25，Rob Stradling写道：

[Rob Stradling]

Yeah, sorry about that.  It's a temporary hack to keep crt.sh afloat while I finish working on a bunch of other changes (that I'm not quite ready to announce yet).

Too many people were doing too many https://crt.sh/ searches for domains with lots of certificates, and the underlying SQL queries were attempting to retrieve all of the results every time.  These long-running queries were hurting performance for everyone and were usually being killed off by the database replication before they could complete.

The temporary hack limits the number of certificate_identity records considered by the query to a maximum of 10000.  You can see the SQL here:

https://crt.sh/?Identity=%.google.com&exclude=expired&showSQL=y

It's a hack because there's no control over _which_ 10000 certificate_identity records are considered (so presumably the %.google.com search is picking up 10000 records relating to expired certs, which are then all being filtered out).  There aren't currently any suitable indexes, so this is the best I can do for the time being, I'm afraid.