9 messages in com.mysql.lists.clusterRe: Do not save tables in ram?| From | Sent On | Attachments |
|---|---|---|
| Jake Conk | 28 Sep 2007 18:15 | |
| Matthew Montgomery | 28 Sep 2007 20:27 | |
| Jake Conk | 01 Oct 2007 15:28 | |
| Matthew Montgomery | 01 Oct 2007 18:35 | |
| Jake Conk | 01 Oct 2007 19:24 | |
| Matthew Montgomery | 01 Oct 2007 20:02 | |
| Jake Conk | 01 Oct 2007 21:52 | |
| Stewart Smith | 02 Oct 2007 20:31 | |
| Matthew Montgomery | 02 Oct 2007 20:58 |
| Subject: | Re: Do not save tables in ram? |
|---|---|
| From: | Matthew Montgomery (mmon...@mysql.com) |
| Date: | 10/02/2007 08:58:34 PM |
| List: | com.mysql.lists.cluster |
On Mon, 2007-10-01 at 21:52 -0700, Jake Conk wrote:
Matthew,
Say you have a table that uses 3 table spaces, the first 2 are full and you only care to see the percentage of the 3rd table filling up so you can create another table space before time runs out. How would you create a query that only shows the table spaces which are filling up (excluding ones that are already filled)?
Basically in the same way.
mysql> alter tablespace ts_1 add datafile 'ts_2.dat' engine=ndbcluster; Query OK, 0 rows affected (10.24 sec)
mysql> select TABLESPACE_NAME, FILE_NAME, (FREE_EXTENTS/TOTAL_EXTENTS)*100 as
PERCENT_FREE, EXTENT_SIZE, EXTRA from information_schema.FILES where
FILE_TYPE='DATAFILE';
+-----------------+-----------+--------------+-------------+----------------+
| TABLESPACE_NAME | FILE_NAME | PERCENT_FREE | EXTENT_SIZE | EXTRA |
+-----------------+-----------+--------------+-------------+----------------+
| ts_1 | ts_1.dat | 0.0000 | 1048576 | CLUSTER_NODE=3 |
| ts_1 | ts_1.dat | 0.0000 | 1048576 | CLUSTER_NODE=4 |
| ts_1 | ts_2.dat | 100.0000 | 1048576 | CLUSTER_NODE=3 |
| ts_1 | ts_2.dat | 100.0000 | 1048576 | CLUSTER_NODE=4 |
+-----------------+-----------+--------------+-------------+----------------+
4 rows in set (0.01 sec)
mysql> select TABLESPACE_NAME, FILE_NAME, (FREE_EXTENTS/TOTAL_EXTENTS)*100 as
PERCENT_FREE, EXTENT_SIZE, EXTRA from information_schema.FILES where
FILE_TYPE='DATAFILE' and (FREE_EXTENTS/TOTAL_EXTENTS)*100 > 0;
+-----------------+-----------+--------------+-------------+----------------+
| TABLESPACE_NAME | FILE_NAME | PERCENT_FREE | EXTENT_SIZE | EXTRA |
+-----------------+-----------+--------------+-------------+----------------+
| ts_1 | ts_2.dat | 100.0000 | 1048576 | CLUSTER_NODE=3 |
| ts_1 | ts_2.dat | 100.0000 | 1048576 | CLUSTER_NODE=4 |
+-----------------+-----------+--------------+-------------+----------------+
2 rows in set (0.00 sec)
Also, do undo files get filled up? I don't see in the information_schema files page examples on querying the size of undo files and not sure if we have to add undo files if the ones we created initially get filled up?
When you have a high volume of writes to the disk data tables the log file can be overrun in the same way as main UNDO logs that are controlled by the NoOfFragmentLogFiles and RedoBuffer, UndoDataBuffer and UndoIndexBuffer can be. The data from this logfile is flushed to the datafile by a background thread.
I can't think of a way right off to see the fill rate for these UNDO logs.
See the files in a logfile group using.
mysql> SELECT LOGFILE_GROUP_NAME, FILE_NAME, LOGFILE_GROUP_NUMBER, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_NAME IS NOT NULL AND FILE_TYPE='UNDO LOG'; +--------------------+-----------+----------------------+-----------------------------------------+ | LOGFILE_GROUP_NAME | FILE_NAME | LOGFILE_GROUP_NUMBER | EXTRA | +--------------------+-----------+----------------------+-----------------------------------------+ | lg_1 | lg_1.log | 5 | CLUSTER_NODE=3;UNDO_BUFFER_SIZE=8388608 | | lg_1 | lg_1.log | 5 | CLUSTER_NODE=4;UNDO_BUFFER_SIZE=8388608 | +--------------------+-----------+----------------------+-----------------------------------------+ 2 rows in set (0.01 sec)
Btw, thanks so much for being patient with me and answering all my questions. I don't know what way to thank but I really do appreciate all the information you've given me and hopefully it will be useful to someone else as well :)
Thanks, - Jake
On 10/1/07, Matthew Montgomery <mmon...@mysql.com> wrote:
On Mon, 2007-10-01 at 19:25 -0700, Jake Conk wrote:
Matthew,
Ok so we have to keep adding more data files as the end of tablespace is reached.
Last 2 questions... When creating tablespaces. data files, log files, etc, are they automatically replicated on each data node or must you create them each individually on each node or do they only live on the node they are created?
These files are created on all data nodes. There are no table space data or log files on the SQL nodes.
... And lastly, is there something out there to warn you when your usage is almost up so you can add more data files etc so your cluster does not fail when users are trying to input data?
You can query the information schema to see how many free extents are in a data file.
mysql> select TABLESPACE_NAME, FILE_NAME, (FREE_EXTENTS/TOTAL_EXTENTS)*100 as PERCENT_FREE , EXTRA from information_schema.FILES where FILE_TYPE='DATAFILE'; +-----------------+-----------+--------------+----------------+ | TABLESPACE_NAME | FILE_NAME | PERCENT_FREE | EXTRA | +-----------------+-----------+--------------+----------------+ | ts_1 | ts_1.dat | 100.0000 | CLUSTER_NODE=3 | | ts_1 | ts_1.dat | 100.0000 | CLUSTER_NODE=4 | +-----------------+-----------+--------------+----------------+ 2 rows in set (0.00 sec)
By default you will have a 1MB extent size. So it will only be that precise.
-- Matthew Montgomery, Support Engineer San Antonio, TX MySQL Inc, www.mysql.com
-- Matthew Montgomery, Support Engineer San Antonio, TX MySQL Inc, www.mysql.com