|
|
Posted by Brian Dunning on 07/07/05 03:56
Hi Richard - like I said, whatever the merits of the situation,
that's the query that the ISP is not permitting. As soon as I change
that, they'll reactivate the account. The ISP is PowWeb if anyone
else wants to take it up with them; I've already talked myself red in
the face.
On Jul 6, 2005, at 5:51 PM, Richard Lynch wrote:
> On Wed, July 6, 2005 1:43 pm, Brian Dunning said:
>
>> I am cross-posting this to the PHP and the MySQL lists because I'm
>> not sure in which technology my solution will lie.
>>
>> I have a pretty busy PHP/MySQL site that executes the following query
>> a lot:
>>
>
> Define "a lot"
>
> Every page hit?
>
>
>> select count(*) as `count` from terms;
>>
>> My MySQL account was disabled by my ISP because this query was
>> causing resource issues, so I need to find a different way to know
>> the record count in that table. A few records are added once every 5
>> minutes by a cron job. The record count is constant the rest of the
>> time. No records are ever deleted.
>>
>
> A few records every 5 minutes is approximately one INSERT per minute.
>
> That's *WAY* more resource-intensive than a hell of a lot of count(*)
> queries...
>
> Did your ISP specifically say it was that query, or is that your
> analysis
> of what's causing the problem?
>
> Cuz I'm suspecting that the count(*) might be a red herring you are
> following.
>
> Course, it could be a red herring handed to you *BY* the ISP. They
> may
> have less experience with a high-volume site than you do. :-v
>
>
>> Is it possible to create some kind of server-side variable, in which
>> the cron job could store the record count, which would be accessible
>> to all scripts, and would stay the same until it gets reset? Or is
>> there a less-intense MySQL query I should be using instead?
>>
>
> Dumping it in a .txt file, as suggested, may move the SQL performance
> problem to a hard drive performance problem...
>
> Doing include('count.txt') as often as you had to be doing SQL count
> (*)
> may cause disk trashing. Or not, depending on the disk cache and
> hardware
> and other users and a few zillion other factors...
>
> That may or may not get you into or out of more hot water with the
> ISP...
>
> Another thing to MAYBE look into, would be shared memory, if that's in
> your PHP... Not for the faint of heart, but at least you'd have
> the speed
> you need.
>
> You may just need to change hosts or upgrade your package.
>
> There comes a point where your problem actually *IS* hardware, not
> software.
>
> --
> Like Music?
> http://l-i-e.com/artists.htm
>
>
Navigation:
[Reply to this message]
|