List:General Discussion« Previous MessageNext Message »
From:Victor Danilchenko Date:April 11 2008 1:37pm
Subject:MySQl 5.0 optimization problem
View as plain text  
	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.

-- 
	Victor Danilchenko
	Senior Software Engineer, AskOnline.net
	victor@stripped - 617-273-0119
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