I would expect this to finally be something on the client end,
rather than the server end... is there a search index that gets
rebuilt periodically? Maybe some reports that get generated against
the data? The last example that comes to my mind is if you use a
client that caches data, does the cache get dumped or the client
restarted at some point?
These are the sort of things that we found led to the type of
behavior you are talking about. Let me give clearer examples... Our
databases typically do Discussion Boards... usually very large scale
discussion boards (think eBay or HBO scales). The discussion board
server (in this case the database client) keeps it's own search
index, but need to update it on a regular basis to keep it current.
If that period is too infrequent or the queries poorly optimized,
they can generate a lot of load on the database, and you get the type
of results you are seeing. Or if the discussion board tries to
analyze the stats for the last day (or week or month etc) to provide
information for reports... in our example a million page views a day
means a million stats records a day, and any analysis can be quite
the load generator. Same thing with our cache on our discussion
board... if our discussion board has been up for some time it has all
the messages most frequently used already in local cache, it doesn't
do a query to recover each message in this situation... an instance
of the discussion board going live into production with no data in
the cache can mean a huge database hit for a few minutes while the
caches in the discussion board get populated.
These are just examples from our life, but I'm pretty sure when al is
said and done that the cause will be some process that your client is
generating to do something periodic, rather than the MySQL Server
running some sort of process, which we've never seen.
Take a look at the process list when it is in one of these cycles
(from the mysql command line client type "show processlist;"). it
should give you a pretty good idea of what's doing what at the time
and will give you some idea on where to look.
Best Regards, Bruce
On Nov 15, 2005, at 2:20 AM, Viktor Fougstedt wrote:
Hi.
We have a MySQLd with both MyISAM and InnoDB tables that at uneven
intervals stops responding correctly to connections.
At all times, about one connection per minut fails, regardless of
which database and/or user and/or remote host is connecting. The
same connection parameters (and same queries) work correctly 99.9%
of the time, and it is entirely random which connections time out
and when.
We can live with that problem, which does not seem to have any
explanation.
But some times, MySQLd starts taking all the CPU it can get, and
gets extremely sluggish for a few minutes. At these times, several
connections every second are rejected because of timeouts. These
rejections we can't live with.
To attempt solving the problem, I've started thinking that there
might be some form of periodical cleanup that MySQLd or InnoDB
performs automatically, and that we could force it to perform at
night when the expected load is lower.
Is there any such background cleanup performed? It could be
periodical, when a certain number of queries/updates/inserts have
been run, or when some query cache or similar gets full?
If these problems or descriptions somehow ring a bell, I would
welcome any insight I could get from the list.
Thanks in advance,
/Viktor...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
