Re: Huge number of INSERTs

On Sat, Nov 19, 2011 at 1:29 AM, Steve Crawford
<scrawford(at)pinpointresearch(dot)com> wrote:
> On 11/18/2011 04:30 AM, Phoenix Kiula wrote:
>>
>> On Fri, Nov 18, 2011 at 9:25 AM, Steve Crawford
>> <scrawford(at)pinpointresearch(dot)com>  wrote:
>>
>>
>>>> Database only? Or is it also your webserver?
>>
>> It's my webserver and DB. Webserver is nginx, proxying all PHP
>> requests to apache in the backend.
>
> You still didn't answer what "massive traffic" means.

Thousands of website hits per minute. (At peak time)

Average is a few hundred per minute.

> 3Com? Perhaps you mean 3Ware? And (again) what are the RAID cache
> *settings*? In particular, the write-back/write-through setting.

Yes 3Ware.

RAID cache settings:

----------------------------------------------------------------------
Logical device information
----------------------------------------------------------------------
Logical device number 0
Logical device name : RAID10-A
RAID level : 10
Status of logical device : Optimal
Size : 1906678 MB
Stripe-unit size : 256 KB
Read-cache mode : Enabled
MaxIQ preferred cache setting : Disabled
MaxIQ cache setting : Disabled
Write-cache mode : Enabled (write-back)
Write-cache setting : Enabled (write-back) when protected by battery/ZMM
Partitioned : Yes
Protected by Hot-Spare : No
Bootable : Yes
Failed stripes : No
Power settings : Disabled
--------------------------------------------------------
Logical device segment information
--------------------------------------------------------
Group 0, Segment 0 : Present (0,0) 9QJ00FMB
Group 0, Segment 1 : Present (0,1) 9QJ1R3NW
Group 1, Segment 0 : Present (0,2) 9QJ00L58
Group 1, Segment 1 : Present (0,3) 9QJ01JJ5

> So most of your selects aren't hitting the database. Since we are talking db
> tuning, it would have been nice to know how many queries are hitting the
> database, not the number of requests hitting the webserver. But the question
> was "what is the typical duration of the queries" - specifically the queries
> hitting the database.

Right now single SELECTs with just that one "WHERE indexed_column =
'Value' LIMIT 1" type queries are taking 3.0 of CPU, and so on. Why
should these queries be taking so much time and resources?

> Earlier you said you were doing 200 inserts/minute. Is that an average
> throughout the day or is that at peak time. Peak load is really what is of
> interest. 200 inserts/minute is not even 4/second.

As above.

> Look at your log. If it isn't set to record request time, set it to do so. I
> set my Apache servers to log request time in microseconds.

Could you specify how precisely you have set up this log? Through
CustomLog? Thanks!

Thanks!