[Drizzle-discuss] the status of filesystem storage engine in drizzle
http://www.dazuiniu.com/blog/2010/07/11/the-status-of-filesystem-storage-engine-in-drizzle.html The file system storage engine is in shape now. There are lots of storage engines in Drizzle already. What’s this new storage engine used for? To give you a rough idea, you can use the below SQL statement to read the file ‘/proc/loadavg’ directly. drizzle CREATE TABLE LoadAverage ( load1 DECIMAL(3, 2), load5 DECIMAL(3, 2), load15 DECIMAL(3, 2), active_processes VARCHAR(12), lastpid INTEGER) ENGINE=FILESYSTEM, FILE=/proc/loadavg; drizzle select * from LoadAverage; +---+---++--+-+ | load1 | load5 | load15 | active_processes | lastpid | +---+---++--+-+ | 0.00 | 0.00 | 0.00 | 1/97 |6061 | +---+---++--+-+ The file system storage engine eases our manipulation on disk file, especially those file under /proc directory. This engine tries to be small and useful, like the calculator at your hand. There are several options available for this storage engine for now. Extra options provides extra functionalities. 1. *FILE*. This option specifies which file composes the corresponding table. This is the most important and useful option, it’s a MUST option for this storage engine. 2. *ROW_SEPARATOR*. This option specifies which characters should be taken as end-of-line. The default is “\n”. 3. *COL_SEPARATOR*. This option specifies which characters should be taken as the delimiter for columns. The default is ” \t” (space and tab), it’s reasonable to make it as default. 4. *SEPARATOR_MODE*. It has three values, STRICT, GENERAL, WEAK. This one is a little tricky to understand. It shows the rule how to treat continuous separators. For example, if we have the line “111SPACESPACESPACE222″, the columns in WEAK mode would be only two columns “111″ and “222″; the columns in other modes would be four columns “111″, NULL, NULL, “222″. GENERAL mode will omit empty lines in the file; while STRICT mode will ruthlessly add an entire empty tuple into this table. 5. *FORMAT*. This is a newly added feature. It’s written to process the file “/proc/meminfo”. This file has the similar format as key and value each line. What about we transpose these columns and rows and make all these keys as the column in a table? That should be cool. Yes, we can do this. Specify “KEY_VALUE” as the value of this option and set the FILE to ‘/proc/meminfo’, here you go: CREATE TABLE t1 (a int) ENGINE=FILESYSTEM,FILE=/proc/meminfo,FORMAT=KEY_VALUE,COL_SEPARATOR=: ; SELECT * FROM t1; Active AnonPages Bounce Buffers Cached CommitLimit Committed_ASDirectMap2M DirectMap4k Dirty HugePages_Free HugePages_Rsvd HugePages_Surp HugePages_Total Hugepagesize InactiveMapped MemFree MemTotalNFS_UnstablePageTables SReclaimableSUnreclaim Slab SwapCached SwapFree SwapTotal VmallocChunkVmallocTotalVmallocUsed Writeback WritebackTmp 1526364 198768 0 221004 2829356 2600136 495728 4186112 704019160 0 0 020481722772 51064 502844 4059680 0 27124 198000 12076 210076 0 570296 570296 34359655499 34359738367 82808 0 0 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `Active` varchar(8) DEFAULT NULL, `AnonPages` varchar(7) DEFAULT NULL, ...some lines omitted... `Writeback` varchar(2) DEFAULT NULL, `WritebackTmp` varchar(2) DEFAULT NULL ) ENGINE=FILESYSTEM FILE='/proc/meminfo' FORMAT='KEY_VALUE' COL_SEPARATOR=': ' There are still some TODOs on my list: 1. enclosing and quotation is not there. 2. more test cases to cover some corner cases. 3. … I would like my project to be useful, and I encourage all DBAs, administrators to give a try and see whether it fits your daily use. Any feedback is welcome! [image: :-)] --Zimin ___ Mailing list: https://launchpad.net/~drizzle-discuss Post to : drizzle-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
Re: [Drizzle-discuss] the status of filesystem storage engine in drizzle
This is very cool! Is this pushed anywhere and/or proposed for merge yet? On 07/11/2010 09:49 AM, ZQ wrote: http://www.dazuiniu.com/blog/2010/07/11/the-status-of-filesystem-storage-engine-in-drizzle.html The file system storage engine is in shape now. There are lots of storage engines in Drizzle already. What’s this new storage engine used for? To give you a rough idea, you can use the below SQL statement to read the file ‘/proc/loadavg’ directly. drizzle CREATE TABLE LoadAverage ( load1 DECIMAL(3, 2), load5 DECIMAL(3, 2), load15 DECIMAL(3, 2), active_processes VARCHAR(12), lastpid INTEGER) ENGINE=FILESYSTEM, FILE=/proc/loadavg; drizzle select * from LoadAverage; +---+---++--+-+ | load1 | load5 | load15 | active_processes | lastpid | +---+---++--+-+ | 0.00 | 0.00 | 0.00 | 1/97 |6061 | +---+---++--+-+ The file system storage engine eases our manipulation on disk file, especially those file under /proc directory. This engine tries to be small and useful, like the calculator at your hand. There are several options available for this storage engine for now. Extra options provides extra functionalities. 1. *FILE*. This option specifies which file composes the corresponding table. This is the most important and useful option, it’s a MUST option for this storage engine. 2. *ROW_SEPARATOR*. This option specifies which characters should be taken as end-of-line. The default is “\n”. 3. *COL_SEPARATOR*. This option specifies which characters should be taken as the delimiter for columns. The default is ” \t” (space and tab), it’s reasonable to make it as default. 4. *SEPARATOR_MODE*. It has three values, STRICT, GENERAL, WEAK. This one is a little tricky to understand. It shows the rule how to treat continuous separators. For example, if we have the line “111SPACESPACESPACE222″, the columns in WEAK mode would be only two columns “111″ and “222″; the columns in other modes would be four columns “111″, NULL, NULL, “222″. GENERAL mode will omit empty lines in the file; while STRICT mode will ruthlessly add an entire empty tuple into this table. 5. *FORMAT*. This is a newly added feature. It’s written to process the file “/proc/meminfo”. This file has the similar format as key and value each line. What about we transpose these columns and rows and make all these keys as the column in a table? That should be cool. Yes, we can do this. Specify “KEY_VALUE” as the value of this option and set the FILE to ‘/proc/meminfo’, here you go: CREATE TABLE t1 (a int) ENGINE=FILESYSTEM,FILE=/proc/meminfo,FORMAT=KEY_VALUE,COL_SEPARATOR=: ; SELECT * FROM t1; Active AnonPages Bounce Buffers Cached CommitLimit Committed_ASDirectMap2M DirectMap4k Dirty HugePages_Free HugePages_Rsvd HugePages_Surp HugePages_Total Hugepagesize InactiveMapped MemFree MemTotalNFS_UnstablePageTables SReclaimableSUnreclaim Slab SwapCached SwapFree SwapTotal VmallocChunkVmallocTotalVmallocUsed Writeback WritebackTmp 1526364 198768 0 221004 2829356 2600136 495728 4186112 7040 19160 0 0 020481722772 51064 502844 4059680 0 27124 198000 12076 210076 0 570296 570296 34359655499 34359738367 82808 0 0 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `Active` varchar(8) DEFAULT NULL, `AnonPages` varchar(7) DEFAULT NULL, ...some lines omitted... `Writeback` varchar(2) DEFAULT NULL, `WritebackTmp` varchar(2) DEFAULT NULL ) ENGINE=FILESYSTEM FILE='/proc/meminfo' FORMAT='KEY_VALUE' COL_SEPARATOR=': ' There are still some TODOs on my list: 1. enclosing and quotation is not there. 2. more test cases to cover some corner cases. 3. … I would like my project to be useful, and I encourage all DBAs, administrators to give a try and see whether it fits your daily use. Any feedback is welcome! :-) --Zimin ___ Mailing list: https://launchpad.net/~drizzle-discuss Post to : drizzle-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp ___ Mailing list: https://launchpad.net/~drizzle-discuss Post to : drizzle-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~drizzle-discuss More help : https://help.launchpad.net/ListHelp
Re: [Drizzle-discuss] the status of filesystem storage engine in drizzle
Real cool stuff..Thanks Zimin. This is an excellent! Now DBAs can monitor the host server performance though SQL interface.. connecting remotely. and probably DBA can athorize a user to monitor too. Writing a monitoring tool just like Oracle's Grid Control is a quite simple job now. Well Done. Awaiting your code to reach trunk. Jobin. On Sun, Jul 11, 2010 at 10:23 PM, Monty Taylor mord...@inaugust.com wrote: This is very cool! Is this pushed anywhere and/or proposed for merge yet? On 07/11/2010 09:49 AM, ZQ wrote: http://www.dazuiniu.com/blog/2010/07/11/the-status-of-filesystem-storage-engine-in-drizzle.html The file system storage engine is in shape now. There are lots of storage engines in Drizzle already. What’s this new storage engine used for? To give you a rough idea, you can use the below SQL statement to read the file ‘/proc/loadavg’ directly. drizzle CREATE TABLE LoadAverage ( load1 DECIMAL(3, 2), load5 DECIMAL(3, 2), load15 DECIMAL(3, 2), active_processes VARCHAR(12), lastpid INTEGER) ENGINE=FILESYSTEM, FILE=/proc/loadavg; drizzle select * from LoadAverage; +---+---++--+-+ | load1 | load5 | load15 | active_processes | lastpid | +---+---++--+-+ | 0.00 | 0.00 | 0.00 | 1/97 |6061 | +---+---++--+-+ The file system storage engine eases our manipulation on disk file, especially those file under /proc directory. This engine tries to be small and useful, like the calculator at your hand. There are several options available for this storage engine for now. Extra options provides extra functionalities. 1. *FILE*. This option specifies which file composes the corresponding table. This is the most important and useful option, it’s a MUST option for this storage engine. 2. *ROW_SEPARATOR*. This option specifies which characters should be taken as end-of-line. The default is “\n”. 3. *COL_SEPARATOR*. This option specifies which characters should be taken as the delimiter for columns. The default is ” \t” (space and tab), it’s reasonable to make it as default. 4. *SEPARATOR_MODE*. It has three values, STRICT, GENERAL, WEAK. This one is a little tricky to understand. It shows the rule how to treat continuous separators. For example, if we have the line “111SPACESPACESPACE222″, the columns in WEAK mode would be only two columns “111″ and “222″; the columns in other modes would be four columns “111″, NULL, NULL, “222″. GENERAL mode will omit empty lines in the file; while STRICT mode will ruthlessly add an entire empty tuple into this table. 5. *FORMAT*. This is a newly added feature. It’s written to process the file “/proc/meminfo”. This file has the similar format as key and value each line. What about we transpose these columns and rows and make all these keys as the column in a table? That should be cool. Yes, we can do this. Specify “KEY_VALUE” as the value of this option and set the FILE to ‘/proc/meminfo’, here you go: CREATE TABLE t1 (a int) ENGINE=FILESYSTEM,FILE=/proc/meminfo,FORMAT=KEY_VALUE,COL_SEPARATOR=: ; SELECT * FROM t1; Active AnonPages Bounce Buffers Cached CommitLimit Committed_ASDirectMap2M DirectMap4k Dirty HugePages_Free HugePages_Rsvd HugePages_Surp HugePages_Total Hugepagesize InactiveMapped MemFree MemTotalNFS_UnstablePageTables SReclaimableSUnreclaim Slab SwapCached SwapFree SwapTotal VmallocChunkVmallocTotalVmallocUsed Writeback WritebackTmp 1526364 198768 0 221004 2829356 2600136 495728 4186112 704019160 0 0 020481722772 51064 502844 4059680 0 27124 198000 12076 210076 0 570296 570296 34359655499 34359738367 82808 0 0 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `Active` varchar(8) DEFAULT NULL, `AnonPages` varchar(7) DEFAULT NULL, ...some lines omitted... `Writeback` varchar(2) DEFAULT NULL, `WritebackTmp` varchar(2) DEFAULT NULL ) ENGINE=FILESYSTEM FILE='/proc/meminfo' FORMAT='KEY_VALUE' COL_SEPARATOR=': ' There are still some TODOs on my list: 1. enclosing and quotation is not there. 2. more test cases to cover some corner cases. 3. … I would like my project to be useful, and I encourage all DBAs, administrators to give a try and see whether it fits your daily use. Any feedback is welcome! :-) --Zimin ___ Mailing list: https://launchpad.net/~drizzle-discusshttps://launchpad.net/%7Edrizzle-discuss Post to
Re: [Drizzle-discuss] the status of filesystem storage engine in drizzle
Hi Monty, All the work is pushed to my branch lp:~ziminq/drizzle/filesystem-storage-engine. I will propose for merge again. On Mon, Jul 12, 2010 at 12:53 AM, Monty Taylor mord...@inaugust.com wrote: This is very cool! Is this pushed anywhere and/or proposed for merge yet? On 07/11/2010 09:49 AM, ZQ wrote: http://www.dazuiniu.com/blog/2010/07/11/the-status-of-filesystem-storage-engine-in-drizzle.html The file system storage engine is in shape now. There are lots of storage engines in Drizzle already. What’s this new storage engine used for? To give you a rough idea, you can use the below SQL statement to read the file ‘/proc/loadavg’ directly. drizzle CREATE TABLE LoadAverage ( load1 DECIMAL(3, 2), load5 DECIMAL(3, 2), load15 DECIMAL(3, 2), active_processes VARCHAR(12), lastpid INTEGER) ENGINE=FILESYSTEM, FILE=/proc/loadavg; drizzle select * from LoadAverage; +---+---++--+-+ | load1 | load5 | load15 | active_processes | lastpid | +---+---++--+-+ | 0.00 | 0.00 | 0.00 | 1/97 |6061 | +---+---++--+-+ The file system storage engine eases our manipulation on disk file, especially those file under /proc directory. This engine tries to be small and useful, like the calculator at your hand. There are several options available for this storage engine for now. Extra options provides extra functionalities. 1. *FILE*. This option specifies which file composes the corresponding table. This is the most important and useful option, it’s a MUST option for this storage engine. 2. *ROW_SEPARATOR*. This option specifies which characters should be taken as end-of-line. The default is “\n”. 3. *COL_SEPARATOR*. This option specifies which characters should be taken as the delimiter for columns. The default is ” \t” (space and tab), it’s reasonable to make it as default. 4. *SEPARATOR_MODE*. It has three values, STRICT, GENERAL, WEAK. This one is a little tricky to understand. It shows the rule how to treat continuous separators. For example, if we have the line “111SPACESPACESPACE222″, the columns in WEAK mode would be only two columns “111″ and “222″; the columns in other modes would be four columns “111″, NULL, NULL, “222″. GENERAL mode will omit empty lines in the file; while STRICT mode will ruthlessly add an entire empty tuple into this table. 5. *FORMAT*. This is a newly added feature. It’s written to process the file “/proc/meminfo”. This file has the similar format as key and value each line. What about we transpose these columns and rows and make all these keys as the column in a table? That should be cool. Yes, we can do this. Specify “KEY_VALUE” as the value of this option and set the FILE to ‘/proc/meminfo’, here you go: CREATE TABLE t1 (a int) ENGINE=FILESYSTEM,FILE=/proc/meminfo,FORMAT=KEY_VALUE,COL_SEPARATOR=: ; SELECT * FROM t1; Active AnonPages Bounce Buffers Cached CommitLimit Committed_ASDirectMap2M DirectMap4k Dirty HugePages_Free HugePages_Rsvd HugePages_Surp HugePages_Total Hugepagesize InactiveMapped MemFree MemTotalNFS_UnstablePageTables SReclaimableSUnreclaim Slab SwapCached SwapFree SwapTotal VmallocChunkVmallocTotalVmallocUsed Writeback WritebackTmp 1526364 198768 0 221004 2829356 2600136 495728 4186112 704019160 0 0 020481722772 51064 502844 4059680 0 27124 198000 12076 210076 0 570296 570296 34359655499 34359738367 82808 0 0 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `Active` varchar(8) DEFAULT NULL, `AnonPages` varchar(7) DEFAULT NULL, ...some lines omitted... `Writeback` varchar(2) DEFAULT NULL, `WritebackTmp` varchar(2) DEFAULT NULL ) ENGINE=FILESYSTEM FILE='/proc/meminfo' FORMAT='KEY_VALUE' COL_SEPARATOR=': ' There are still some TODOs on my list: 1. enclosing and quotation is not there. 2. more test cases to cover some corner cases. 3. … I would like my project to be useful, and I encourage all DBAs, administrators to give a try and see whether it fits your daily use. Any feedback is welcome! :-) --Zimin ___ Mailing list: https://launchpad.net/~drizzle-discusshttps://launchpad.net/%7Edrizzle-discuss Post to : drizzle-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~drizzle-discusshttps://launchpad.net/%7Edrizzle-discuss More help : https://help.launchpad.net/ListHelp
Re: [Drizzle-discuss] the status of filesystem storage engine in drizzle
Thanks Jobin. It's glad to see the useful value in my project. :-) On Mon, Jul 12, 2010 at 2:49 AM, Jobin Augustine jobi...@gmail.com wrote: Real cool stuff..Thanks Zimin. This is an excellent! Now DBAs can monitor the host server performance though SQL interface.. connecting remotely. and probably DBA can athorize a user to monitor too. Writing a monitoring tool just like Oracle's Grid Control is a quite simple job now. Well Done. Awaiting your code to reach trunk. Jobin. On Sun, Jul 11, 2010 at 10:23 PM, Monty Taylor mord...@inaugust.comwrote: This is very cool! Is this pushed anywhere and/or proposed for merge yet? On 07/11/2010 09:49 AM, ZQ wrote: http://www.dazuiniu.com/blog/2010/07/11/the-status-of-filesystem-storage-engine-in-drizzle.html The file system storage engine is in shape now. There are lots of storage engines in Drizzle already. What’s this new storage engine used for? To give you a rough idea, you can use the below SQL statement to read the file ‘/proc/loadavg’ directly. drizzle CREATE TABLE LoadAverage ( load1 DECIMAL(3, 2), load5 DECIMAL(3, 2), load15 DECIMAL(3, 2), active_processes VARCHAR(12), lastpid INTEGER) ENGINE=FILESYSTEM, FILE=/proc/loadavg; drizzle select * from LoadAverage; +---+---++--+-+ | load1 | load5 | load15 | active_processes | lastpid | +---+---++--+-+ | 0.00 | 0.00 | 0.00 | 1/97 |6061 | +---+---++--+-+ The file system storage engine eases our manipulation on disk file, especially those file under /proc directory. This engine tries to be small and useful, like the calculator at your hand. There are several options available for this storage engine for now. Extra options provides extra functionalities. 1. *FILE*. This option specifies which file composes the corresponding table. This is the most important and useful option, it’s a MUST option for this storage engine. 2. *ROW_SEPARATOR*. This option specifies which characters should be taken as end-of-line. The default is “\n”. 3. *COL_SEPARATOR*. This option specifies which characters should be taken as the delimiter for columns. The default is ” \t” (space and tab), it’s reasonable to make it as default. 4. *SEPARATOR_MODE*. It has three values, STRICT, GENERAL, WEAK. This one is a little tricky to understand. It shows the rule how to treat continuous separators. For example, if we have the line “111SPACESPACESPACE222″, the columns in WEAK mode would be only two columns “111″ and “222″; the columns in other modes would be four columns “111″, NULL, NULL, “222″. GENERAL mode will omit empty lines in the file; while STRICT mode will ruthlessly add an entire empty tuple into this table. 5. *FORMAT*. This is a newly added feature. It’s written to process the file “/proc/meminfo”. This file has the similar format as key and value each line. What about we transpose these columns and rows and make all these keys as the column in a table? That should be cool. Yes, we can do this. Specify “KEY_VALUE” as the value of this option and set the FILE to ‘/proc/meminfo’, here you go: CREATE TABLE t1 (a int) ENGINE=FILESYSTEM,FILE=/proc/meminfo,FORMAT=KEY_VALUE,COL_SEPARATOR=: ; SELECT * FROM t1; Active AnonPages Bounce Buffers Cached CommitLimit Committed_ASDirectMap2M DirectMap4k Dirty HugePages_Free HugePages_Rsvd HugePages_Surp HugePages_Total Hugepagesize InactiveMapped MemFree MemTotalNFS_UnstablePageTables SReclaimableSUnreclaim Slab SwapCached SwapFree SwapTotal VmallocChunkVmallocTotalVmallocUsed Writeback WritebackTmp 1526364 198768 0 221004 2829356 2600136 495728 4186112 704019160 0 0 020481722772 51064 502844 4059680 0 27124 198000 12076 210076 0 570296 570296 34359655499 34359738367 82808 0 0 SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `Active` varchar(8) DEFAULT NULL, `AnonPages` varchar(7) DEFAULT NULL, ...some lines omitted... `Writeback` varchar(2) DEFAULT NULL, `WritebackTmp` varchar(2) DEFAULT NULL ) ENGINE=FILESYSTEM FILE='/proc/meminfo' FORMAT='KEY_VALUE' COL_SEPARATOR=': ' There are still some TODOs on my list: 1. enclosing and quotation is not there. 2. more test cases to cover some corner cases. 3. … I would like my project to be useful, and I encourage all DBAs, administrators to give a try and see whether it fits your daily use. Any feedback is welcome! :-) --Zimin