This is a longstanding problem, but it continues to worry me because it seems to lock up the account control center at times -- and if that's locking out other users, then "ick."
Anyway, log searches on my domains, some of which get almost no mail at all, can take upwards of 10 minutes to complete (or never complete at all) -- indexing problem perhaps?
logs incredibly slow?
Moderator: Moderators
-
- Site Admin
- Posts: 598
- Joined: Wed Nov 17, 2004 10:05 pm
- Location: Nevada
- Contact:
Yes, this is something we're aware of, but haven't resolved yet. The tables are indexed, but it's still slow. The problem ultimately lies with the fact that there are literally millions upon millions of entries (100298607 rows as of right now for smtpd alone) in the logs and the server is only dual processor. We keep six months worth of logs.
Although it does lock up your session to the account control center (something to do with web browsers trying to reuse an existing connection), it doesn't affect anyone else. The ultimate solution we're considering is changing the log interface as a job submission tool where you you queue up queries that get processed in the background and read the reports when they're done.
Or it could be all MySQL's falut and we need to move the log database to Postgres or something else that likes processing 100+ million rows. =) It does return results rather quickly without group/sort clauses, so it's the copy to temp table and sort stage that's causing the headaches.
Although it does lock up your session to the account control center (something to do with web browsers trying to reuse an existing connection), it doesn't affect anyone else. The ultimate solution we're considering is changing the log interface as a job submission tool where you you queue up queries that get processed in the background and read the reports when they're done.
Or it could be all MySQL's falut and we need to move the log database to Postgres or something else that likes processing 100+ million rows. =) It does return results rather quickly without group/sort clauses, so it's the copy to temp table and sort stage that's causing the headaches.
Technical Support support@rollernet.us
Roller Network LLC
Roller Network LLC
PostgreSQL R0q$ my world.glendale2x wrote:Or it could be all MySQL's falut and we need to move the log database to Postgres or something else that likes processing 100+ million rows. =) It does return results rather quickly without group/sort clauses, so it's the copy to temp table and sort stage that's causing the headaches.
(In truth, my employer has switched almost entirely to pgsql at this point, and most of the software folks now see MySQL as a "beginner database" now that the flexibility of pgsql really set in with them. The attitude seems to be viral. )
-
- Site Admin
- Posts: 598
- Joined: Wed Nov 17, 2004 10:05 pm
- Location: Nevada
- Contact:
The reason I didn't go with postgres in the beginning is because the replication seemed kind of half-assed. I would rather have a native replication scheme, not something randomly tacked on, and postgres seems to be lacking in this one critical (for me) area. I can probably use it for logging though, since that's not critical data that needs to be replicated. I haven't looked at the state of postgres replication lately, so my feelings on it are probably outdated.
Technical Support support@rollernet.us
Roller Network LLC
Roller Network LLC
-
- Site Admin
- Posts: 598
- Joined: Wed Nov 17, 2004 10:05 pm
- Location: Nevada
- Contact:
I made some changes to the indexes on the smtp and smtpd log tables, tweaked the queries to use said indexes better, and all of the test queries started coming back in under 30 seconds (even for the biggest queries I could find). The text search functions are still slow because they are just a linear search on the result from the SQL query, but we'll work on speeding that up, too. I'm tempted to say it's finally fixed, but at the risk of jinxing it, I won't. =)
Edit - I forgot to mention - the lockup problem is caused by the web browser using keepalives to talk to the same Apache process that's busy with the query.
Edit - I forgot to mention - the lockup problem is caused by the web browser using keepalives to talk to the same Apache process that's busy with the query.
Technical Support support@rollernet.us
Roller Network LLC
Roller Network LLC