Re: Differences between numbers of rows in tables
Hi Dan, all! Dan Trainor wrote: Thanks for the prompt reply, Augusto - I completely understand what you're saying. To have anything such as a real-time measurement to the exact number of tables would be an incredible preformance degration, not to mention overhead and the like. Right. This is the conflict between fast operation (wanted by everybody) and maintaining statistics (shared data = bottleneck). I think I'm willing to accept the fact that while data is being sent to the database server, I won't get an exact reading of database/table/row size. This makes complete sense. However, what I am after, is how to get the exact size of a database/table/row when NO connections are being made. What is the exact size you refer too? Is it a) the resources consumed on the disk (file size etc), b) the data, index, and metadata stored (not including any gaps), c) the valid user data which would be returned by SELECT statements? Remember: In order to speed up further operations (inserts), a DBMS may not shrink disk structures even if data get deleted (freeing up space). So as long as the data are only growing, these three aspects may correlate closely, but when updates and deletes start, this may change. (Compare the use of heap space by malloc() and free() in C.) In the MySQL case, different table handlers will show different behaviour, further complicating the picture. Say, if I started MySQL with no networking. This way, I could positively ensure that no data modification would be taking place. Is this possible? For a), I recommend using the operating system means (Unix: du). For b) and c), I do not know the answer, but there may be ways. Depending on the database size, getting any exact answer to b) or c) may take longer than the typical user (a vague term, I know) is willing to wait. Regards, Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Differences between numbers of rows in tables
Joerg Bruehe wrote: Hi Dan, all! Dan Trainor wrote: Thanks for the prompt reply, Augusto - I completely understand what you're saying. To have anything such as a real-time measurement to the exact number of tables would be an incredible preformance degration, not to mention overhead and the like. Right. This is the conflict between fast operation (wanted by everybody) and maintaining statistics (shared data = bottleneck). I think I'm willing to accept the fact that while data is being sent to the database server, I won't get an exact reading of database/table/row size. This makes complete sense. However, what I am after, is how to get the exact size of a database/table/row when NO connections are being made. What is the exact size you refer too? Is it a) the resources consumed on the disk (file size etc), b) the data, index, and metadata stored (not including any gaps), c) the valid user data which would be returned by SELECT statements? Remember: In order to speed up further operations (inserts), a DBMS may not shrink disk structures even if data get deleted (freeing up space). So as long as the data are only growing, these three aspects may correlate closely, but when updates and deletes start, this may change. (Compare the use of heap space by malloc() and free() in C.) In the MySQL case, different table handlers will show different behaviour, further complicating the picture. Say, if I started MySQL with no networking. This way, I could positively ensure that no data modification would be taking place. Is this possible? For a), I recommend using the operating system means (Unix: du). For b) and c), I do not know the answer, but there may be ways. Depending on the database size, getting any exact answer to b) or c) may take longer than the typical user (a vague term, I know) is willing to wait. Regards, Jörg Good morning, Jörg - I think what I was going for, was resources consumed on disk. However, when talking about the ndbcluster table type, in a Cluster environment, resources consumed in memory. I believe that's more appropriate for the cluster@ list, which I'll check in a few here. I believe that 'du' would give some appropriate numbers, and a close estimate as to what I'll be needing to look for here. I appreciate your help. Thanks! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Differences between numbers of rows in tables
Hello, all - Still kidna new to MySQL, so please forgive me if this is somewhat dumb question... When issuing 'SHOW TABLE STATUS', I can see clearly that under the Rows column for my table, I see 17333. However, when issuing a 'COUNT (*) FROM table', I see 17203 - a difference of 130. We're really pounding this database, but I wouldn't expect any sort of data to become out of sync. Is this a corruption of tables? We're using the InnoDB storage engine for this particular table, if that helps. Thanks! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Differences between numbers of rows in tables
Dan Trainor wrote: Hello, all - Still kidna new to MySQL, so please forgive me if this is somewhat dumb question... When issuing 'SHOW TABLE STATUS', I can see clearly that under the Rows column for my table, I see 17333. However, when issuing a 'COUNT (*) FROM table', I see 17203 - a difference of 130. We're really pounding this database, but I wouldn't expect any sort of data to become out of sync. Is this a corruption of tables? We're using the InnoDB storage engine for this particular table, if that helps. Thanks! -dant Hi - For the record - http://bugs.mysql.com/bug.php?id=5755 I now understand the problem. Thanks! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Differences between numbers of rows in tables
Hi - Anyone else get the following message with *ever*single*post* to the list? Anyone have a procmail filter established already? ;) Thanks -dant [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] If you really intend for this to get to me, remove the 'no' in the eMail address above I'll send you my new one. rs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Differences between numbers of rows in tables
Dan Trainor wrote: Hello, all - Still kidna new to MySQL, so please forgive me if this is somewhat dumb question... When issuing 'SHOW TABLE STATUS', I can see clearly that under the Rows column for my table, I see 17333. However, when issuing a 'COUNT (*) FROM table', I see 17203 - a difference of 130. We're really pounding this database, but I wouldn't expect any sort of data to become out of sync. Is this a corruption of tables? We're using the InnoDB storage engine for this particular table, if that helps. Thanks! -dant Hi - So, now this has made me think here. If 'SHOW TABLE STATUS' only shows an estimate of the number of rows contained in a table, how accurate is the rest of the data in 'SHOW TABLE STATUS'? What I'm looking for is to try to find some exact numbers, such as physical data size on disk, queries/second, etc etc. An estimate would not give me a very good idea of what I'm looking at. Is there a more accurate way of getting table information, aside from 'SHOW TABLE STATUS'? THanks! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Differences between numbers of rows in tables
I suppose this is an inherent limitation of transactional tables - you might see x rows, but at the same time a use who has just inserted some rows will see x+y rows. However I don't see that the numbers are going to be hugely inaccurate. After all, if the table was MyISAM and you get an exact number of rows someone could still add another 20 rows in the second after you run SHOW TABLE STATUS. James Harvard At 5:54 pm -0700 1/2/06, Dan Trainor wrote: So, now this has made me think here. If 'SHOW TABLE STATUS' only shows an estimate of the number of rows contained in a table, how accurate is the rest of the data in 'SHOW TABLE STATUS'? What I'm looking for is to try to find some exact numbers, such as physical data size on disk, queries/second, etc etc. An estimate would not give me a very good idea of what I'm looking at. Is there a more accurate way of getting table information, aside from 'SHOW TABLE STATUS'? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Differences between numbers of rows in tables
James Harvard wrote: I suppose this is an inherent limitation of transactional tables - you might see x rows, but at the same time a use who has just inserted some rows will see x+y rows. However I don't see that the numbers are going to be hugely inaccurate. After all, if the table was MyISAM and you get an exact number of rows someone could still add another 20 rows in the second after you run SHOW TABLE STATUS. James Harvard At 5:54 pm -0700 1/2/06, Dan Trainor wrote: So, now this has made me think here. If 'SHOW TABLE STATUS' only shows an estimate of the number of rows contained in a table, how accurate is the rest of the data in 'SHOW TABLE STATUS'? What I'm looking for is to try to find some exact numbers, such as physical data size on disk, queries/second, etc etc. An estimate would not give me a very good idea of what I'm looking at. Is there a more accurate way of getting table information, aside from 'SHOW TABLE STATUS'? Thanks for the response, James - Well, doing these two calculations happens maybe 10 minutes after the last person has sent any data to the db. Is there any way to show the physical disk space used by a table, or even a column? Maybe there's a whole list of functions which I'm overseeing here that might explain this. Thanks! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Differences between numbers of rows in tables
Thanks for the prompt reply, Augusto - I completely understand what you're saying. To have anything such as a real-time measurement to the exact number of tables would be an incredible preformance degration, not to mention overhead and the like. I think I'm willing to accept the fact that while data is being sent to the database server, I won't get an exact reading of database/table/row size. This makes complete sense. However, what I am after, is how to get the exact size of a database/table/row when NO connections are being made. Say, if I started MySQL with no networking. This way, I could positively ensure that no data modification would be taking place. Is this possible? Thanks for your wonderful insight. Both of you. It is much appreciated. Thanks! -dant Augusto Bott wrote: Dan, I'm afraid I must quote James: I suppose this is an inherent limitation of transactional tables Think of something like that: Some of my databases have transactional tables with several Gbs and a lot of clients simultaneosly connecting and inserting/updating a lot of data, on the sames tables. Why would your transaction see 'exactly' at the same moment the data altered by some other session? I mean, if we can have different transaction isolation levels on different storage engines, why can't the tables 'status scoreboard' be updated itself by 'polling' the storage engines? Why would it be imediatly updated at every transaction commit? The MyISAM storage engine, with a very tight relationship with the server itself, probably has a higher 'update rate' than, let's say, InnoDB (with also may 'suffer' some same consequences as James said related to transactions). -- Augusto Bott augusto.bott (at) gmail.com On 2/1/06, Dan Trainor [EMAIL PROTECTED] wrote: James Harvard wrote: I suppose this is an inherent limitation of transactional tables - you might see x rows, but at the same time a use who has just inserted some rows will see x+y rows. However I don't see that the numbers are going to be hugely inaccurate. After all, if the table was MyISAM and you get an exact number of rows someone could still add another 20 rows in the second after you run SHOW TABLE STATUS. James Harvard At 5:54 pm -0700 1/2/06, Dan Trainor wrote: So, now this has made me think here. If 'SHOW TABLE STATUS' only shows an estimate of the number of rows contained in a table, how accurate is the rest of the data in 'SHOW TABLE STATUS'? What I'm looking for is to try to find some exact numbers, such as physical data size on disk, queries/second, etc etc. An estimate would not give me a very good idea of what I'm looking at. Is there a more accurate way of getting table information, aside from 'SHOW TABLE STATUS'? Thanks for the response, James - Well, doing these two calculations happens maybe 10 minutes after the last person has sent any data to the db. Is there any way to show the physical disk space used by a table, or even a column? Maybe there's a whole list of functions which I'm overseeing here that might explain this. Thanks! -dant -- 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]
