Re: Performance boost by splitting up large table?
You've already had some good advice, but there's something much more simpler that will also give you a significant boost: a covering index. Simply put, the engine is smart enough to not bother with row lookups if everything you asked for is already in the index it was using. You'll need to keep the index requirements in mind, of course (most selective fields first, order fields after selection fields, etc) and then append any other fields to the same index. Hard to say more without actual use cases, of course, but it's well worth looking into as it requires no other changes in application or schema. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Big innodb tables, how can I work with them?
Hi, I have in my server database some tables that are too much big and produce some slow query, even with correct indexes created. For my application, it's necessary to have all the data because we make an authentication process with RADIUS users (AAA protocol) to determine if one user can or not navigate in Internet (Depending on the time of all his sessions). So, with 8GB of data in one table, what are your advices to follow? Fragmentation and sharding discarted because we are working with disk arrays, so not apply. Another option is to delete rows, but in this case, I can't. For the other hand, maybe de only possible solution is increase the resources (RAM). Any ideas? Thanks in advance. Regards, Antonio.
Re: Big innodb tables, how can I work with them?
Am 15.05.2014 14:26, schrieb Antonio Fernández Pérez: I have in my server database some tables that are too much big and produce some slow query, even with correct indexes created. For my application, it's necessary to have all the data because we make an authentication process with RADIUS users (AAA protocol) to determine if one user can or not navigate in Internet (Depending on the time of all his sessions). So, with 8GB of data in one table, what are your advices to follow? Fragmentation and sharding discarted because we are working with disk arrays, so not apply. Another option is to delete rows, but in this case, I can't. For the other hand, maybe de only possible solution is increase the resources (RAM) rule of thumbs is innodb_buffer_pool = database-size or at least as much RAM that frequently accessed data stays always in the pool signature.asc Description: OpenPGP digital signature
Re: Performance boost by splitting up large table?
On Thu, May 15, 2014 at 4:14 AM, Johan De Meersman vegiv...@tuxera.be wrote: You've already had some good advice, but there's something much more simpler that will also give you a significant boost: a covering index. Simply put, the engine is smart enough to not bother with row lookups if everything you asked for is already in the index it was using. You'll need to keep the index requirements in mind, of course (most selective fields first, order fields after selection fields, etc) and then append any other fields to the same index. Hard to say more without actual use cases, of course, but it's well worth looking into as it requires no other changes in application or schema. This table is queried based on requests from the users. There are 10 different lookup columns they can specify, and they can provide any or all of these. Currently each one of the columns has an index on it. Would it be beneficial to create an index with all 10? Rarely are all 10 specified in the query. Typically it's 3 or 4. Would it be worthwhile to see which are much commonly specified and create an index with just those? Or would it be better to put the commonly selected columns on the index with the lookup columns? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Performance boost by splitting up large table?
- Original Message - From: Larry Martell larry.mart...@gmail.com Subject: Re: Performance boost by splitting up large table? This table is queried based on requests from the users. There are 10 different lookup columns they can specify, and they can provide any or That makes it rather more of a bother, as MySQL can't (yet) skip columns in an index, as far as I'm aware. Someone please correct me if I'm wrong here. all of these. Currently each one of the columns has an index on it. I'm a bit fuzzy on multiple-index queries, but I think support isn't all too sharp. Would it be beneficial to create an index with all 10? Rarely are all 10 specified in the query. Typically it's 3 or 4. Would it be worthwhile to see which are much commonly specified and create an index with just those? Or would it be better to put the commonly selected columns on the index with the lookup columns? You may want to grab a day or week's worth of queries (either general_log with all the overhead and disk space that entails, or tcpdump) and pump that through pt-query-digest (Percona Toolkit) to see which combinations of fields are most often used, and add the necessary covering indices to help those queries. A few points to keep in mind during analysis: * order of fields in the where clause is largely irrelevant (although most-selective-first is preferred) * not all the fields in the index must be queried; but you MUST query a full prefix set - ie, for index (a, b, c, d) you can query (a, b) but for (a, c) only (a) will be used * every index constitutes a (small) performance penalty upon table updates, so don't go too wild either :-) Also helpful: * plenty of memory, at least enough to keep the working set in memory * faster disks (SSD, or FusionIO things) helps bundles for data that doesn't fit in memory * if you notice a tendency for multiple users (say, a dozen) to run identical queries (and that means /bitwise/ identical query text, down to the last space), the query cache might help. Don't make it too big, though, a couple of meg should suffice. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Performance boost by splitting up large table?
On Thu, May 15, 2014 at 11:01 AM, Johan De Meersman vegiv...@tuxera.be wrote: - Original Message - From: Larry Martell larry.mart...@gmail.com Subject: Re: Performance boost by splitting up large table? This table is queried based on requests from the users. There are 10 different lookup columns they can specify, and they can provide any or That makes it rather more of a bother, as MySQL can't (yet) skip columns in an index, as far as I'm aware. Someone please correct me if I'm wrong here. all of these. Currently each one of the columns has an index on it. I'm a bit fuzzy on multiple-index queries, but I think support isn't all too sharp. Would it be beneficial to create an index with all 10? Rarely are all 10 specified in the query. Typically it's 3 or 4. Would it be worthwhile to see which are much commonly specified and create an index with just those? Or would it be better to put the commonly selected columns on the index with the lookup columns? You may want to grab a day or week's worth of queries (either general_log with all the overhead and disk space that entails, or tcpdump) and pump that through pt-query-digest (Percona Toolkit) to see which combinations of fields are most often used, and add the necessary covering indices to help those queries. We have a log going back over 2 years with over 200,000 queries, and I've written python scripts to give the frequency of the column pulled for the report and the column used for filtering. Not surprising, a very small number of each are used most of the time, and a huge number used just 1 or 2 times. I'll be digging into that next week. A few points to keep in mind during analysis: * order of fields in the where clause is largely irrelevant (although most-selective-first is preferred) * not all the fields in the index must be queried; but you MUST query a full prefix set - ie, for index (a, b, c, d) you can query (a, b) but for (a, c) only (a) will be used * every index constitutes a (small) performance penalty upon table updates, so don't go too wild either :-) Also helpful: * plenty of memory, at least enough to keep the working set in memory * faster disks (SSD, or FusionIO things) helps bundles for data that doesn't fit in memory The db host is running with 250GB memory, db size is 470GB, spinning 2.5” 15k rpm drives, and 40 cores. * if you notice a tendency for multiple users (say, a dozen) to run identical queries (and that means /bitwise/ identical query text, down to the last space), the query cache might help. Don't make it too big, though, a couple of meg should suffice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
