Re: RE: How to rewrite query
I agree that individual fields have relatively few possible values - hopefully, when those are combined in a multi-column index, he will have a greater number of unique combinations, gaining more out of the index. That's why I suggested putting stype and Is_id as the first two fields in the index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. ls_id is evenly distributed but stype is not. But thought is interesting. And one question. I mentioned that update:select ratio is 3:1. There are no deletes and inserts. Also update does not touch ls_id and stype fields. Is there index updating overhead then? Mindaugas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: RE: How to rewrite query
I'm not certain how MySQL handles the specific case where some columns in a record covered by a multi-column index are updated; it may update the whole index entry, or just part of it, not sure. In any case, yes, there is some overhead associated with having an index on columns that get updated. In my experience, the overhead is not bad (how's that for quantifying it??) even with multiple indices on a table with 100,000 to 150,000 records. Your table is smaller, I think you said around 30,000 records. If you're concerned about it, you could certainly try an index that covers just Is_id and stype, in either order; it would be an improvement over where you are now. That would avoid index update overhead if those fields never change. It's also frankly something that is pretty easy to experiment with, as dropping and creating indices on a 30K record table should be reasonably quick (around a few seconds?). Also, don't forget the datatype mismatch in the one column, that can have an impact too. Dan On 10/18/06, Mindaugas [EMAIL PROTECTED] wrote: I agree that individual fields have relatively few possible values - hopefully, when those are combined in a multi-column index, he will have a greater number of unique combinations, gaining more out of the index. That's why I suggested putting stype and Is_id as the first two fields in the index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. ls_id is evenly distributed but stype is not. But thought is interesting. And one question. I mentioned that update:select ratio is 3:1. There are no deletes and inserts. Also update does not touch ls_id and stype fields. Is there index updating overhead then? Mindaugas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: How to rewrite query
You should strongly consider adding an index on the fields you're querying against. Right now, none of the fields in your query are indexed in the table. I would try something like this for starters: a multi-column index against all the fields in the query you showed. If you have other queries you run regularly, you might evaluate those to see if a different field order in the index makes sense. But I think this may helpl your problem query immensely: ALTER TABLE ipaddr ADD INDEX multi_col_idx (stype, ls_id, pool, allocated); Another suggestion I have is for you to change either your query slightly, or your table structure slightly. Your field ls_id is a VARCHAR field, but you are querying it like it is a numeric field, which may be forcing MySQL to do a type conversion on all the rows in the table. Either change your query to look for Is_id = '3' (note the quotes) or change the column type to an INT and leave your query as-is. (you know which will better fit your data) If you do both of these things, I think it should help a lot. Best, Dan On 10/17/06, Mindaugas [EMAIL PROTECTED] wrote: SHOW CREATE TABLE ipaddr; CREATE TABLE `ipaddr` ( `ip` varchar(15) NOT NULL, `pool` varchar(20) NOT NULL, `stype` varchar(1) NOT NULL, `sclass` varchar(1) NOT NULL, `radserv` varchar(1) NOT NULL, `ls_id` varchar(1) NOT NULL default '0', `allocated` datetime default NULL, `msisdn` varchar(20) default NULL, `imsi` varchar(20) default NULL, `session_id` varchar(30) default NULL, `user_name` varchar(20) default NULL, PRIMARY KEY (`ip`), UNIQUE KEY `ipaddr_msisdn_idx` (`msisdn`) ) ENGINE=NDB DEFAULT CHARSET=latin1 EXPLAIN select ip from ipaddr where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null limit 1; ++-++--+---+--+-+--+---+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++--+---+--+-+--+---+-+ | 1 | SIMPLE | ipaddr | ALL | NULL | NULL | NULL| NULL | 37896 | Using where | ++-++--+---+--+-+--+---+-+ When you say it's too slow, how slow is it? And how fast when it is a memory table? With NDB table during peak loads our scripts often does not get the answer. And mysqladmin proc always shows that query in execution. With MEMORY table most often I see sleeping mysql process. I thought that after we find free ip we change allocated to not null. So at the end beginning of table consists of records with allocated is not null. So every query has to pass ~8000 records to find allocated is null row. Am I right there and how to avoid that? Also, which specific version of 5.0 are you on? 5.0.x ... what is x? 5.0.26-max from mysql RPMs. Thanks, Mindaugas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: How to rewrite query
I agree that individual fields have relatively few possible values - hopefully, when those are combined in a multi-column index, he will have a greater number of unique combinations, gaining more out of the index. That's why I suggested putting stype and Is_id as the first two fields in the index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. Dan On 10/17/06, Jerry Schwartz [EMAIL PROTECTED] wrote: I would think that with so few possible values for all but the ip field, indexing the other fields would accomplish nothing. In fact, I'd be surprised if the optimizer didn't realize that and do a sequential read anyways. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 10:46 AM To: mysql@lists.mysql.com Subject: Re: How to rewrite query At 08:34 AM 10/17/2006, you wrote: Hello, For the Radius server we're using MySQL cluster and the following query looks too slow: select ip from ipaddr where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null limit 1; Table ipaddr is small (~6MB, 38000 records). Fields in WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas Mindaugas, If your queries are always using those fields, why not create a single compound index on those fields? This shouldn't slow down inserts that much, and if they do, you could always use delayed inserts. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: How to rewrite query
I didn't think of that (combinations). You are probably right. Due to my background, I tend not to think a lot about multi-column indices. I would think that you want field with the most possible values first, then the next, etc. Is that what you were thinking? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 12:05 PM To: Jerry Schwartz Cc: mos; mysql@lists.mysql.com Subject: Re: RE: How to rewrite query I agree that individual fields have relatively few possible values - hopefully, when those are combined in a multi-column index, he will have a greater number of unique combinations, gaining more out of the index. That's why I suggested putting stype and Is_id as the first two fields in the index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. Dan On 10/17/06, Jerry Schwartz [EMAIL PROTECTED] wrote: I would think that with so few possible values for all but the ip field, indexing the other fields would accomplish nothing. In fact, I'd be surprised if the optimizer didn't realize that and do a sequential read anyways. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 10:46 AM To: mysql@lists.mysql.com Subject: Re: How to rewrite query At 08:34 AM 10/17/2006, you wrote: Hello, For the Radius server we're using MySQL cluster and the following query looks too slow: select ip from ipaddr where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null limit 1; Table ipaddr is small (~6MB, 38000 records). Fields in WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas Mindaugas, If your queries are always using those fields, why not create a single compound index on those fields? This shouldn't slow down inserts that much, and if they do, you could always use delayed inserts. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: How to rewrite query
Would it not be best to have the field with the fewest repeats (i.e., the closest to unique) first, or is that what you meant. Bill On Tue, October 17, 2006 10:12, Jerry Schwartz said: I didn't think of that (combinations). You are probably right. Due to my background, I tend not to think a lot about multi-column indices. I would think that you want field with the most possible values first, then the next, etc. Is that what you were thinking? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 12:05 PM To: Jerry Schwartz Cc: mos; mysql@lists.mysql.com Subject: Re: RE: How to rewrite query I agree that individual fields have relatively few possible values - hopefully, when those are combined in a multi-column index, he will have a greater number of unique combinations, gaining more out of the index. That's why I suggested putting stype and Is_id as the first two fields in the index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. Dan On 10/17/06, Jerry Schwartz [EMAIL PROTECTED] wrote: I would think that with so few possible values for all but the ip field, indexing the other fields would accomplish nothing. In fact, I'd be surprised if the optimizer didn't realize that and do a sequential read anyways. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 10:46 AM To: mysql@lists.mysql.com Subject: Re: How to rewrite query At 08:34 AM 10/17/2006, you wrote: Hello, For the Radius server we're using MySQL cluster and the following query looks too slow: select ip from ipaddr where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null limit 1; Table ipaddr is small (~6MB, 38000 records). Fields in WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas Mindaugas, If your queries are always using those fields, why not create a single compound index on those fields? This shouldn't slow down inserts that much, and if they do, you could always use delayed inserts. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: RE: How to rewrite query
Yes, it'd be best to have the values with highest cardinality / most uniqueness first. On 10/17/06, William R. Mussatto [EMAIL PROTECTED] wrote: Would it not be best to have the field with the fewest repeats (i.e., the closest to unique) first, or is that what you meant. Bill On Tue, October 17, 2006 10:12, Jerry Schwartz said: I didn't think of that (combinations). You are probably right. Due to my background, I tend not to think a lot about multi-column indices. I would think that you want field with the most possible values first, then the next, etc. Is that what you were thinking? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 12:05 PM To: Jerry Schwartz Cc: mos; mysql@lists.mysql.com Subject: Re: RE: How to rewrite query I agree that individual fields have relatively few possible values - hopefully, when those are combined in a multi-column index, he will have a greater number of unique combinations, gaining more out of the index. That's why I suggested putting stype and Is_id as the first two fields in the index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. Dan On 10/17/06, Jerry Schwartz [EMAIL PROTECTED] wrote: I would think that with so few possible values for all but the ip field, indexing the other fields would accomplish nothing. In fact, I'd be surprised if the optimizer didn't realize that and do a sequential read anyways. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 10:46 AM To: mysql@lists.mysql.com Subject: Re: How to rewrite query At 08:34 AM 10/17/2006, you wrote: Hello, For the Radius server we're using MySQL cluster and the following query looks too slow: select ip from ipaddr where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null limit 1; Table ipaddr is small (~6MB, 38000 records). Fields in WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas Mindaugas, If your queries are always using those fields, why not create a single compound index on those fields? This shouldn't slow down inserts that much, and if they do, you could always use delayed inserts. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: How to rewrite query
That's what Dan (and I) meant. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: William R. Mussatto [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 1:28 PM To: mysql@lists.mysql.com Subject: RE: RE: How to rewrite query Would it not be best to have the field with the fewest repeats (i.e., the closest to unique) first, or is that what you meant. Bill On Tue, October 17, 2006 10:12, Jerry Schwartz said: I didn't think of that (combinations). You are probably right. Due to my background, I tend not to think a lot about multi-column indices. I would think that you want field with the most possible values first, then the next, etc. Is that what you were thinking? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 12:05 PM To: Jerry Schwartz Cc: mos; mysql@lists.mysql.com Subject: Re: RE: How to rewrite query I agree that individual fields have relatively few possible values - hopefully, when those are combined in a multi-column index, he will have a greater number of unique combinations, gaining more out of the index. That's why I suggested putting stype and Is_id as the first two fields in the index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. Dan On 10/17/06, Jerry Schwartz [EMAIL PROTECTED] wrote: I would think that with so few possible values for all but the ip field, indexing the other fields would accomplish nothing. In fact, I'd be surprised if the optimizer didn't realize that and do a sequential read anyways. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 10:46 AM To: mysql@lists.mysql.com Subject: Re: How to rewrite query At 08:34 AM 10/17/2006, you wrote: Hello, For the Radius server we're using MySQL cluster and the following query looks too slow: select ip from ipaddr where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null limit 1; Table ipaddr is small (~6MB, 38000 records). Fields in WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas Mindaugas, If your queries are always using those fields, why not create a single compound index on those fields? This shouldn't slow down inserts that much, and if they do, you could always use delayed inserts. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
