List:General Discussion« Previous MessageNext Message »
From:Wm Mussatto Date:April 11 2008 3:38pm
Subject:Re: MySQl 5.0 optimization problem
View as plain text  
On Fri, April 11, 2008 06:47, Ben Clewett wrote:
> Are you using MyIsam or InnoDB?  Or something else?
>
> In either case the speed to get a COUNT() is largely down to the speed
> if your disks and size of disk caching.  A COUNT() forces the system to
> read every row in order to count them, and any large table is probably
> larger than your caches.
>
> In some ways this is not so important, since it is unusual for a query
> to want to read every row of a table.  (I have 250GB tables which have
> excellent performance but would take minutes to count every row :)
>
> It might be better to consider the type of queries you will be using,
> and the type of table, and optimise for that...
>
> Ben
>
> Victor Danilchenko wrote:
>>     Hi,
>>
>>     I am trying to optimize our DB server. We have one table which has
>> 1.3M entries, and the keys are GUIDs (so the key space is large).
>> However, I have it all indexed. The performance was iffy, though, so I
>> increased memory allocation, and the searches on the indexed fields seem
>> to be OK now. Still, a simple count(field) on this table still takes,
>> like, 2 minutes! I am guessing i am missing something obvious, but I
>> read through a few MySQL optimization guides, and it seems like i am
>> covering my bases.
>>
>>     Here is the relevant slice of my my.cnf:
>>
>> #
>> # * Fine Tuning
>> #
>> key_buffer              = 256M
>> max_allowed_packet      = 64M
>> thread_stack            = 512K
>> thread_cache_size       = 32
>> #
>> # * Query Cache Configuration
>> #
>> query_cache_limit       = 32M
>> query_cache_size        = 256M
>> query_cache_type        = 1
>> table_cache             = 512
>> sort_buffer_size        = 32M
>>
>>
>>     I am running Ubuntu 7.04, with 1GB of RAM. The relevant fields are
>> all indexed, but I can't imagine why a simple count() would take so
>> long, when the actual query by value on the same field is effectively
>> instant (after my cache setting expansion).
>>
>>     Does anyone have an idea of what I am missing? Also, if you think
>> any of the above settings seem wrong for a server with 1GB of RAM,
>> please let me know.
If the field you are counting is the first field in an index I would think
it would go much faster (system will just use the index).  If some of your
counts are fast and some are slow (you said iffy) that might explain the
difference.

------
William R. Mussatto
Systems Engineer
http://www.csz.com
909-920-9154

Thread
MySQl 5.0 optimization problemVictor Danilchenko11 Apr
  • Re: MySQl 5.0 optimization problemBen Clewett11 Apr
    • Re: MySQl 5.0 optimization problemWm Mussatto11 Apr
      • Re: MySQl 5.0 optimization problemBen Clewett11 Apr
        • Re: MySQl 5.0 optimization problemVictor Danilchenko11 Apr
    • Re: MySQl 5.0 optimization problemRob Wultsch11 Apr