logs incredibly slow?

Need help? Ask here.

Moderator: Moderators

Post Reply
tvierling
Posts: 14
Joined: Fri May 05, 2006 10:50 am
Contact:

logs incredibly slow?

Post by tvierling »

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?
RollerNetSupport
Site Admin
Posts: 598
Joined: Wed Nov 17, 2004 10:05 pm
Location: Nevada
Contact:

Post by RollerNetSupport »

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.
Technical Support support@rollernet.us
Roller Network LLC
tvierling
Posts: 14
Joined: Fri May 05, 2006 10:50 am
Contact:

Post by tvierling »

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.
PostgreSQL R0q$ my world.

(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. :-P )
RollerNetSupport
Site Admin
Posts: 598
Joined: Wed Nov 17, 2004 10:05 pm
Location: Nevada
Contact:

Post by RollerNetSupport »

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
RollerNetSupport
Site Admin
Posts: 598
Joined: Wed Nov 17, 2004 10:05 pm
Location: Nevada
Contact:

Post by RollerNetSupport »

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.
Technical Support support@rollernet.us
Roller Network LLC
Post Reply