MySQl 5.0 optimization problem
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 [EMAIL PROTECTED] - 617-273-0119 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl 5.0 optimization problem
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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl 5.0 optimization problem
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl 5.0 optimization problem
Hi, Can you please post your query? I also need to know your table type as different settings effect different table types? You are right that a SELECT COUNT(*) WHERE field = 'value' should hit the index, but does depend on your query. You might also try EXPLAIN before your query, which will show the approximate number of rows you are hitting. Look at SHOW TABLE STATUS which will give an idea of the size of the rows and indexs. Marry one to the other and it will give an idea of the cache settings to get the query into cache. But still depends a lot on the table type! Ben Wm Mussatto wrote: 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl 5.0 optimization problem
On Fri, Apr 11, 2008 at 6:47 AM, Ben Clewett [EMAIL PROTECTED] wrote: A COUNT() forces the system to read every row in order to count them... That is not strictly the case. A count(field) can use an index scan rather than a sequential scan, which may or may not be faster. Also some count(field) can queries be optimized away if a field is NOT NULL and the type is myisam. Posting a Show status; might be useful. DDL is always helpful... -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQl 5.0 optimization problem
First of all, my bad -- I forgot to mention that I use MyISAM. mysql show table status from example like 'leads'\G *** 1. row *** Name: leads Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 1267995 Avg_row_length: 224 Data_length: 284349972 Max_data_length: 281474976710655 Index_length: 201081856 Data_free: 0 Auto_increment: NULL Create_time: 2008-04-11 14:03:14 Update_time: 2008-04-11 14:04:26 Check_time: 2008-04-11 14:07:51 Collation: utf8_general_ci Checksum: NULL Create_options: Comment: Ben Clewett wrote: Hi, Can you please post your query? I also need to know your table type as different settings effect different table types? The query is simply: select count(email1) from leads; The table structure is as follows: mysql describe leads; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | id | varchar(36) | NO | PRI | | deleted| tinyint(1) | NO | | 0 | converted | tinyint(1) | NO | | 0 | date_entered | datetime | NO | | -00-00 00:00:00 | | | date_modified | datetime | NO | | -00-00 00:00:00 | | | modified_user_id | varchar(36) | YES | | NULL | assigned_user_id | varchar(36) | YES | MUL | NULL | created_by | varchar(36) | YES | | NULL| salutation | varchar(5) | YES | | NULL| first_name | varchar(25) | YES | | NULL| last_name | varchar(25) | YES | MUL | NULL| title | varchar(100) | YES | | NULL| refered_by | varchar(100) | YES | | NULL | lead_source| varchar(100) | YES | | NULL | lead_source_description| mediumtext | YES | | NULL| status | varchar(100) | YES | | NULL| status_description | mediumtext | YES | | NULL| department | varchar(100) | YES | | NULL| reports_to_id | varchar(36) | YES | | NULL| do_not_call | char(3) | YES | | 0 | phone_home | varchar(25) | YES | | NULL | phone_mobile | varchar(25) | YES | | NULL | phone_work | varchar(25) | YES | | NULL| phone_other| varchar(25) | YES | | NULL| phone_fax | varchar(25) | YES | | NULL| email1 | varchar(100) | YES | MUL | NULL| email2 | varchar(100) | YES | MUL | NULL| email_opt_out | char(3) | YES | | 0 | primary_address_street | varchar(150) | YES | | NULL | primary_address_city | varchar(100) | YES | | NULL | primary_address_state | varchar(100) | YES | | NULL | primary_address_postalcode | varchar(20) | YES | | NULL| primary_address_country| varchar(100) | YES | | NULL| alt_address_street | varchar(150) | YES | | NULL| alt_address_city | varchar(100) | YES | | NULL| alt_address_state | varchar(100) | YES | | NULL | alt_address_postalcode | varchar(20) | YES | | NULL | alt_address_country| varchar(100) | YES | | NULL| description| mediumtext | YES | | NULL| account_name | varchar(150) | YES | | NULL| account_description| mediumtext | YES | | NULL| contact_id | varchar(36) | YES | MUL | NULL| account_id | varchar(36) | YES | MUL | NULL| opportunity_id | varchar(36) | YES | MUL | NULL | opportunity_name | varchar(255) | YES | | NULL | opportunity_amount | varchar(50) | YES | | NULL| campaign_id| varchar(36) | YES | | NULL| portal_name| varchar(255) | YES | | NULL| portal_app | varchar(255) | YES | | NULL|
