RE: Performance issues when deleting and reading on large table
>> An index on 'gender' may have a cardinality of >> only two or three (male/female(/unknown)) for example. Never b-tree index such columns ! Oracle (db2 ...rdbms) has bitmap indexes which work fine fork such data. Look at BIN(myset+0) in http://dev.mysql.com/tech-resources/articles/mysql-set-datatype.html. Massive load is better without indexes, which are only good for selects. Mathias -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: dimanche 24 avril 2005 19:46 To: Almar van Pel; mysql@lists.mysql.com Cc: 'mathias fatene' Subject: Re: Performance issues when deleting and reading on large table > > It's a probably a case of not having the cardinality of indexes right and > thus making wrong decisions for queries. > - Currently there is not a single query in the application that does not use > the correct index. We only have key-reads. Wich would mean that MySQL is > creating these incorrect indexes? The indexes are not necessarily incorrect, but MySQL also keeps a property called 'cardinality' for each index. It is an estimate of the number of different items in the index. An index on 'gender' may have a cardinality of only two or three (male/female(/unknown)) for example. I've noticed that the cardinality on MyISAM tables can be very wrong and will be updated to a correct value after an OPTIMIZE TABLE. InnoDB seems to estimate the cardinality constantly. > > Deleting a lot of records will have impact on the indexes, so it's quite a > job. The inserts/updates/deletes will also block the table for reading in > case of MyISAM. > - During deletion of records from the table there is no user interaction. > The only person manipulating the table/database is me. That's the reason why > i'm finding this 'strange'. It will still be a massive operation on indexes. If you have many indexes the task will be even harder... > Changing to Innodb would be a great risk I think. Maybe we should think this > over again, but the way the system is configured right now should in my > opion be sufficient enough. It's not a risk, but may take a while to complete (rebuilding the tables). Anyway, you should test it on a seperate database or even a different server. You may also need to redesign the index(es). Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance issues when deleting and reading on large table
> > It's a probably a case of not having the cardinality of indexes right and > thus making wrong decisions for queries. > - Currently there is not a single query in the application that does not use > the correct index. We only have key-reads. Wich would mean that MySQL is > creating these incorrect indexes? The indexes are not necessarily incorrect, but MySQL also keeps a property called 'cardinality' for each index. It is an estimate of the number of different items in the index. An index on 'gender' may have a cardinality of only two or three (male/female(/unknown)) for example. I've noticed that the cardinality on MyISAM tables can be very wrong and will be updated to a correct value after an OPTIMIZE TABLE. InnoDB seems to estimate the cardinality constantly. > > Deleting a lot of records will have impact on the indexes, so it's quite a > job. The inserts/updates/deletes will also block the table for reading in > case of MyISAM. > - During deletion of records from the table there is no user interaction. > The only person manipulating the table/database is me. That's the reason why > i'm finding this 'strange'. It will still be a massive operation on indexes. If you have many indexes the task will be even harder... > Changing to Innodb would be a great risk I think. Maybe we should think this > over again, but the way the system is configured right now should in my > opion be sufficient enough. It's not a risk, but may take a while to complete (rebuilding the tables). Anyway, you should test it on a seperate database or even a different server. You may also need to redesign the index(es). Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance issues when deleting and reading on large table
If you have no active transactions and want a cron delete, an example is : * Create table tmp as select * from your_table where ... <- here indexes are used * drop indexes * delete from you_table where ... * insert into your_table select * from tmp * create index on you_table. You must test it to unsure that index creation is not slow when you have a lot of indexes. You can also disable constraints when deleting and optimize your table at the end of the deletion. In myisam storage, since an update,insert or delete means lock table there is a big transactional problem. Innodb offers row loocking, but you seem having a problem using it. Unfortunaltly ! To simulate transaction, you must split your queries. I remember had worked on a load problem which take days to finish (or not) because the load operation was combined with a lot of select (verify) data. My solution was to do a lot of selects (using indexes), spool results to files, delete rows, and load data from files. It took 1.5 hour to finish a 650Mo data with all the checking operations. Mathias -Original Message- From: Almar van Pel [mailto:[EMAIL PROTECTED] Sent: dimanche 24 avril 2005 18:44 To: mysql@lists.mysql.com Cc: 'Jigal van Hemert'; 'mathias fatene' Subject: RE: Performance issues when deleting and reading on large table Hi Jigal, Mathias, Thanks the time you took to reply to my issue's! I would like to clear out some things. > It's a probably a case of not having the cardinality of indexes right and thus making wrong decisions for queries. - Currently there is not a single query in the application that does not use the correct index. We only have key-reads. Wich would mean that MySQL is creating these incorrect indexes? > Depending on the size of the resulting record sets, your system must have enough memory to handle it. Otherwise a lot of temporary tables will end up on disk (slow) and also indexes cannot be loaded in memory (slow). - The system runs with a key-buffer of 382 M, wich is most of the time not filled 100 %. Created temp. tables is very low. > Deleting a lot of records will have impact on the indexes, so it's quite a job. The inserts/updates/deletes will also block the table for reading in case of MyISAM. - During deletion of records from the table there is no user interaction. The only person manipulating the table/database is me. That's the reason why i'm finding this 'strange'. Changing to Innodb would be a great risk I think. Maybe we should think this over again, but the way the system is configured right now should in my opion be sufficient enough. Mathias, what do you mean by: > If you want to do a massive delete with a cron, it's better to : > * select the rows to delete (using indexes) > * delete indexes > * delete rows (already marked) > * recreate indexes I don't really understand how you 'mark' the records for deletion before deleting indexes. However I'm very interested. Regards, Almar van Pel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance issues when deleting and reading on large table
Hi Jigal, Mathias, Thanks the time you took to reply to my issue's! I would like to clear out some things. > It's a probably a case of not having the cardinality of indexes right and thus making wrong decisions for queries. - Currently there is not a single query in the application that does not use the correct index. We only have key-reads. Wich would mean that MySQL is creating these incorrect indexes? > Depending on the size of the resulting record sets, your system must have enough memory to handle it. Otherwise a lot of temporary tables will end up on disk (slow) and also indexes cannot be loaded in memory (slow). - The system runs with a key-buffer of 382 M, wich is most of the time not filled 100 %. Created temp. tables is very low. > Deleting a lot of records will have impact on the indexes, so it's quite a job. The inserts/updates/deletes will also block the table for reading in case of MyISAM. - During deletion of records from the table there is no user interaction. The only person manipulating the table/database is me. That's the reason why i'm finding this 'strange'. Changing to Innodb would be a great risk I think. Maybe we should think this over again, but the way the system is configured right now should in my opion be sufficient enough. Mathias, what do you mean by: > If you want to do a massive delete with a cron, it's better to : > * select the rows to delete (using indexes) > * delete indexes > * delete rows (already marked) > * recreate indexes I don't really understand how you 'mark' the records for deletion before deleting indexes. However I'm very interested. Regards, Almar van Pel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance issues when deleting and reading on large table
Hi all, Know that indexes are good for select(s), but very bad for massive insert,update and delete. If you want to do a massive delete with a cron, it's better to : * select the rows to delete (using indexes) * delete indexes * delete rows (already marked) * recreate indexes Another way if you want to delete a big percentage of your table, is to copy the stating records, drop table and recreate it with those record. Then recreate indexes. I assume that you're not in a massive transactional situation, and maybe myisam storage. If not, show processlist may help you to track using or not of internal temporary tables, ... Mathias -Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Sent: dimanche 24 avril 2005 17:05 To: Almar van Pel; mysql@lists.mysql.com Subject: Re: Performance issues when deleting and reading on large table From: "Almar van Pel" > After some time (sometimes a week sometimes a month) it appears that the > index of the table gets stuck. > It tries to read from the table but does not get response. This causes the > connectionqueue to fill up > and the load on the system increases dramatically. In other words, unless I > do an optimize table , the system > hangs. Most of the times you see that the index is getting 20 Mb off. > When I do check table (before optimizing) there are no errors. > > Is there any way to see this problem coming, so I can outrun it? (Without > having to schedule optimize, wich = downtime, every week..) You should run optimize table regularly (once a week or so) in some cases: http://dev.mysql.com/doc/mysql/en/optimize-table.html It's a probably a case of not having the cardinality of indexes right and thus making wrong decisions for queries. > Trying to get the previous table clean, I created some jobs deleting old > records. When I delete a lot of records at in one job, > the system also nearly hangs. (+/- 10 to 15.000 records) The load again > increases dramatically. I tried every trick in the book, but cannot > understand, > why this action is so heavy for the system. Deleting a lot of records will have impact on the indexes, so it's quite a job. The inserts/updates/deletes will also block the table for reading in case of MyISAM. Such a large table in a high concurrency situation (many writes and many reads) can be a job for InnoDB tables. They seem slow for small tables, but have the tendency to keep the same speed for large tables, while MyISAM will probably get slower the bigger the table is under these circumstances. If you can use the PRIMARY index in a query and keep the 'PRIMARY' index as short as possible, InnoDB can be a very fast table handler. Depending on the size of the resulting record sets, your system must have enough memory to handle it. Otherwise a lot of temporary tables will end up on disk (slow) and also indexes cannot be loaded in memory (slow). Running large databases is sometimes a bit of a challenge; finding the right queries, setting up the right index(es), etc. Regards, Jigal. -- 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: Performance issues when deleting and reading on large table
From: "Almar van Pel" > After some time (sometimes a week sometimes a month) it appears that the > index of the table gets stuck. > It tries to read from the table but does not get response. This causes the > connectionqueue to fill up > and the load on the system increases dramatically. In other words, unless I > do an optimize table , the system > hangs. Most of the times you see that the index is getting 20 Mb off. > When I do check table (before optimizing) there are no errors. > > Is there any way to see this problem coming, so I can outrun it? (Without > having to schedule optimize, wich = downtime, every week..) You should run optimize table regularly (once a week or so) in some cases: http://dev.mysql.com/doc/mysql/en/optimize-table.html It's a probably a case of not having the cardinality of indexes right and thus making wrong decisions for queries. > Trying to get the previous table clean, I created some jobs deleting old > records. When I delete a lot of records at in one job, > the system also nearly hangs. (+/- 10 to 15.000 records) The load again > increases dramatically. I tried every trick in the book, but cannot > understand, > why this action is so heavy for the system. Deleting a lot of records will have impact on the indexes, so it's quite a job. The inserts/updates/deletes will also block the table for reading in case of MyISAM. Such a large table in a high concurrency situation (many writes and many reads) can be a job for InnoDB tables. They seem slow for small tables, but have the tendency to keep the same speed for large tables, while MyISAM will probably get slower the bigger the table is under these circumstances. If you can use the PRIMARY index in a query and keep the 'PRIMARY' index as short as possible, InnoDB can be a very fast table handler. Depending on the size of the resulting record sets, your system must have enough memory to handle it. Otherwise a lot of temporary tables will end up on disk (slow) and also indexes cannot be loaded in memory (slow). Running large databases is sometimes a bit of a challenge; finding the right queries, setting up the right index(es), etc. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
