使用sysbench测试MySQL最大并发连接，出现上面的错误。原因是操作系统对一个进程打开的文件句柄数量有限制，其中包含打开的SOCKET数量。
ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) 4
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) 1024
cpu time (seconds, -t) unlimited
max user processes (-u) 7168
virtual memory (kbytes, -v) unlimited
注意上面open files是1024，因此我发现当建立了1024个连接之后就会报错。
这个值通常不能用ulimit命令来改（除非是在root下测试）。需要修改/etc/security/limits.conf，增加如：
gulei hard nofile 16384
gulei soft nofile 16384
最左侧是用户名，即对gulei用户进程打开文件的限制。
这样设置以后，如果是用ssh登录的，可能还无法生效。需要再执行一下su $USER才能生效。
比如：
ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) 4
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) 1024
cpu time (seconds, -t) unlimited
max user processes (-u) 7168
virtual memory (kbytes, -v) unlimited
su $USER
Password:
ulimit -a
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max locked memory (kbytes, -l) 4
max memory size (kbytes, -m) unlimited
open files (-n) 16384
pipe size (512 bytes, -p) 8
stack size (kbytes, -s) 1024
cpu time (seconds, -t) unlimited
max user processes (-u) 7168
virtual memory (kbytes, -v) unlimited
http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html
In this article, we discuss the XML functionality available in MySQL, with an emphasis on new features coming online in MySQL 5.1 and MySQL 6.0. We assume that you already have a working knowledge of XML, and that you know what the terms “valid” and “well-formed” mean. We also assume that you have some knowledge of XPath.
We cover the following topics:
Methods for outputting MySQL data in XML format, including the use of lib_mysqludf_xql, a third-party library that can be used for this task
Using the functions (new in MySQL 5.1) ExtractValue() and UpdateXML() for working with XML and XPath
Storing data from XML in a MySQL database using the LOAD XML statement (implemented in MySQL 6.0)
Some security considerations to keep in mind when using these techniques
Getting XML into and out of MySQL
In this section, we discuss how to retrieve data from MySQL in XML format, and how to store data obtained from an XML source in a MySQL database.
Exporting Data
In this section, we start with some data already stored in a MySQL table, and demonstrate several different ways to output it in XML format.
Using the --xml option. Both the mysql and mysqldump client programs support a startup option that causes them to produce XML output. Here is a brief example using the mysql client:
shell> mysql -uroot -e "SHOW VARIABLES LIKE '%version%'" --xml
<?xml version="1.0"?>
<resultset statement="SHOW VARIABLES LIKE '%version%'"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="Variable_name">protocol_version</field>
<field name="Value">10</field>
</row>
<row>
<field name="Variable_name">version</field>
<field name="Value">5.1.22-beta-debug</field>
</row>
<row>
<field name="Variable_name">version_comment</field>
<field name="Value">Source distribution</field>
</row>
<row>
<field name="Variable_name">version_compile_machine</field>
<field name="Value">x86_64</field>
</row>
<row>
<field name="Variable_name">version_compile_os</field>
<field name="Value">suse-linux-gnu</field>
</row>
</resultset>
The content of the <field name="Value"> elements corresponds to the values found in the Value column displayed when the same statement is executed in the mysql client without the --xml option, as shown here:
shell> mysql -uroot -e "SHOW VARIABLES LIKE '%version%'"
+-------------------------+---------------------+
| Variable_name | Value |
+-------------------------+---------------------+
| protocol_version | 10 |
| version | 5.1.22-beta-debug |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | suse-linux-gnu |
+-------------------------+---------------------+
Naturally, the values themselves depend on the MySQL version that you are using and the machine on which it is running, so if you run the same statement, your results are likely to be different from what is shown here.
Example table for XML export. The remainder of this section uses a table created and populated by the following SQL statements:
CREATE SCHEMA xmltest;
CREATE TABLE xmltest.cities (
name CHAR(35) NOT NULL DEFAULT '',
country CHAR(52) NOT NULL DEFAULT '',
population int(11) NOT NULL DEFAULT '0'
);
INSERT INTO cities VALUES ('Mumbai (Bombay)','India',10500000);
INSERT INTO cities VALUES ('Seoul','South Korea',9981619);
INSERT INTO cities VALUES ('São Paulo','Brazil',9968485);
INSERT INTO cities VALUES ('Shanghai','China',9696300);
INSERT INTO cities VALUES ('Jakarta','Indonesia',9604900);
INSERT INTO cities VALUES ('Karachi','Pakistan',9269265);
INSERT INTO cities VALUES ('Istanbul','Turkey',8787958);
INSERT INTO cities VALUES ('Ciudad de México','Mexico',8591309);
INSERT INTO cities VALUES ('Moscow','Russian Federation',8389200);
INSERT INTO cities VALUES ('New York','United States',8008278);
Note
This table was originally created using the following SQL statement on the venerable world example database:
CREATE TABLE xmltest.cities
SELECT i.Name AS name,
o.Name AS country,
i.Population AS population
FROM City i JOIN Country o ON i.CountryCode=o.Code
ORDER BY i.Population DESC LIMIT 10;
You can obtain a copy of the world database from http://dev.mysql.com/doc/.
Beginning with MySQL 5.1.12, the <field> and <row> format produced by the mysql client matches that produced by mysqldump. However, the root element in the output of mysql --xml, is <resultset>, whose statement attribute contains the SQL statement passed to mysql, as shown here:
shell> mysql -uroot --xml -e 'SELECT * FROM xmltest.cities ORDER BY name'
<?xml version="1.0"?>
<resultset statement="SELECT * FROM xmltest.cities ORDER BY name"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="name">Ciudad de Méico</field>
<field name="country">Mexico</field>
<field name="population">8591309</field>
</row>
<row>
<field name="name">Istanbul</field>
<field name="country">Turkey</field>
<field name="population">8787958</field>
</row>
<row>
<field name="name">Jakarta</field>
<field name="country">Indonesia</field>
<field name="population">9604900</field>
</row>
<row>
<field name="name">Karachi</field>
<field name="country">Pakistan</field>
<field name="population">9269265</field>
</row>
<row>
<field name="name">Moscow</field>
<field name="country">Russian Federation</field>
<field name="population">8389200</field>
</row>
<row>
<field name="name">Mumbai (Bombay)</field>
<field name="country">India</field>
<field name="population">10500000</field>
</row>
<row>
<field name="name">New York</field>
<field name="country">United States</field>
<field name="population">8008278</field>
</row>
<row>
<field name="name">São Paulo</field>
<field name="country">Brazil</field>
<field name="population">9968485</field>
</row>
<row>
<field name="name">Seoul</field>
<field name="country">South Korea</field>
<field name="population">9981619</field>
</row>
<row>
<field name="name">Shanghai</field>
<field name="country">China</field>
<field name="population">9696300</field>
</row>
</resultset>
The output of mysqldump--xml is structured somewhat differently, as shown here:
shell> mysqldump --xml xmltest cities
<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<database name="xmltest">
<table_structure name="cities">
<field Field="name" Type="char(35)" Null="NO" Key="" Default=""
Extra=""/>
<field Field="country" Type="char(52)" Null="NO" Key="" Default=""
Extra=""/>
<field Field="population" Type="int(11)" Null="NO" Key="" Default="0"
Extra=""/>
<options Name="cities" Engine="MyISAM" Version="10" Row_format="Fixed"
Rows="10" Avg_row_length="92" Data_length="920"
Max_data_length="25895697857380351" Index_length="1024"
Data_free="0" Create_time="2007-08-24 14:19:42"
Update_time="2007-08-24 14:19:42" Collation="latin1_swedish_ci"
Create_options="" Comment="" />
</table_structure>
<table_data name="cities">
<row>
<field name="name">Mumbai (Bombay)</field>
<field name="country">India</field>
<field name="population">10500000</field>
</row>
<row>
<field name="name">Seoul</field>
<field name="country">South Korea</field>
<field name="population">9981619</field>
</row>
<row>
<field name="name">São Paulo</field>
<field name="country">Brazil</field>
<field name="population">9968485</field>
</row>
<row>
<field name="name">Shanghai</field>
<field name="country">China</field>
<field name="population">9696300</field>
</row>
<row>
<field name="name">Jakarta</field>
<field name="country">Indonesia</field>
<field name="population">9604900</field>
</row>
<row>
<field name="name">Karachi</field>
<field name="country">Pakistan</field>
<field name="population">9269265</field>
</row>
<row>
<field name="name">Istanbul</field>
<field name="country">Turkey</field>
<field name="population">8787958</field>
</row>
<row>
<field name="name">Ciudad de México</field>
<field name="country">Mexico</field>
<field name="population">8591309</field>
</row>
<row>
<field name="name">Moscow</field>
<field name="country">Russian Federation</field>
<field name="population">8389200</field>
</row>
<row>
<field name="name">New York</field>
<field name="country">United States</field>
<field name="population">8008278</field>
</row>
</table_data>
</database>
</mysqldump>
Note
The formatting of some of the XML output has been altered slightly to fit the space available on a printed page.
mysqldump--xml employs the following elements:
- The root element of the XML document representing the dump is <mysqldump>
- The table definitions and data belonging to each database represented in the dump are wrapped together in a <database> element whose name attribute value is the name of that database
- Each table definition is wrapped in a <table_structure> element
- The data from each table is wrapped in a <table_data> element, and is comprised of <field> and <row> elements
To save the output of either mysql or mysqldump to a file, simply use the > operator with the desired filename, as shown here:
shell> mysql -uroot --xml xmltest -e 'SELECT name FROM cities LIMIT 2' > /tmp/2cities.xml
shell> more /tmp/2cities.xml
<?xml version="1.0"?>
<resultset statement="SELECT name,country FROM cities LIMIT 2"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="name">Mumbai (Bombay)</field>
<field name="country">India</field>
</row>
<row>
<field name="name">Seoul</field>
<field name="country">South Korea</field>
</row>
</resultset>
Note
An XML namespace declaration is included in the XML output of both mysql and mysqldump, beginning with MySQL 5.1.18.
There may be times when your application expects an XML format that is different from what is normally obtain from mysql or mysqldump. Suppose, for example, that your application expects a format such as this one:
<cities>
<city>name1<city>
<city>name2<city>
<-- etc. -->
</cities>
One way to obtain such output would be to use concatenation with the CONCAT() and GROUP_CONCAT() functions, like this:
mysql> SELECT CONCAT('\n<cities>\n',
-> GROUP_CONCAT(' <city>', name, '</city>\n' SEPARATOR ''),
-> '</cities>') AS xmldoc
-> FROM cities\G
*************************** 1. row ***************************
xmldoc:
<cities>
<city>Mumbai (Bombay)</city>
<city>Seoul</city>
<city>Sã Paulo</city>
<city>Shanghai</city>
<city>Jakarta</city>
<city>Karachi</city>
<city>Istanbul</city>
<city>Ciudad de Méico</city>
<city>Moscow</city>
<city>New York</city>
</cities>
1 row in set (0.01 sec)
Another such example is this format:
<cities>
<city name="name1" population="population1"/>
<city name="name2" population="population2"/>
<-- etc. -->
</cities>
This can be produced as shown here:
mysql> SELECT CONCAT(
-> '\n<cities>',
-> GROUP_CONCAT(
-> '\n\t<city name="', name, '" population="', population, '"/>'
-> SEPARATOR ''
-> ),
-> '\n</cities>'
-> ) AS xmldoc
-> FROM cities\G
*************************** 1. row ***************************
xmldoc:
<cities>
<city name="Mumbai (Bombay)" population="10500000"/>
<city name="Seoul" population="9981619"/>
<city name="Sã Paulo" population="9968485"/>
<city name="Shanghai" population="9696300"/>
<city name="Jakarta" population="9604900"/>
<city name="Karachi" population="9269265"/>
<city name="Istanbul" population="8787958"/>
<city name="Ciudad de Méico" population="8591309"/>
<city name="Moscow" population="8389200"/>
<city name="New York" population="8008278"/>
</cities>
1 row in set (0.01 sec)
You can see that generating even relatively simple XML output by such means can rapidly grow very complicated. While you can wrap such SELECT statements in stored procedures, the fact that MySQL stored routines cannot (at least at present) take a variable number of arguments. Fortunately, there is a third-party library that can be used with MySQL to make this task easier.
Using the lib_mysqludf_xqllibrary. The lib_mysqludf_xql library is a set of UDFs originally written and placed under the GPL by Arnold Daniels. The source for these is now housed at http://www.xcdsql.org/.
mysql> SELECT xql_element('city', name) FROM cities;
+-------------------------------+
| xql_element('city', name) |
+-------------------------------+
| <city>Mumbai (Bombay)</city> |
| <city>Seoul</city>|
| <city>Sã Paulo</city> |
| <city>Shanghai</city> |
| <city>Jakarta</city> |
| <city>Karachi</city> |
| <city>Istanbul</city> |
| <city>Ciudad de Méico</city> |
| <city>Moscow</city> |
| <city>New York</city> |
+--------------------------------+
10 rows in set (0.00 sec)
mysql> SELECT xql_element('city', NULL, name AS 'name') FROM cities;
+-------------------------------------------+
| xql_element('city', NULL, name AS 'name') |
+-------------------------------------------+
| <city name="Mumbai (Bombay)"/>|
| <city name="Seoul"/> |
| <city name="S㯠Paulo"/> |
| <city name="Shanghai"/> |
| <city name="Jakarta"/> |
| <city name="Karachi"/> |
| <city name="Istanbul"/> |
| <city name="Ciudad de M鸩co"/> |
| <city name="Moscow"/> |
| <city name="New York"/> |
+-------------------------------------------+
10 rows in set (0.00 sec)
mysql> SELECT xql_element('city', NULL, name AS 'name', population AS 'population')
-> FROM cities;
+-----------------------------------------------------------------------+
| xql_element('city', NULL, name AS 'name', population AS 'population') |
+-----------------------------------------------------------------------+
| <city name="Mumbai (Bombay)" population="10500000"/> |
| <city name="Seoul" population="9981619"/> |
| <city name="S㯠Paulo" population="9968485"/> |
| <city name="Shanghai" population="9696300"/> |
| <city name="Jakarta" population="9604900"/> |
| <city name="Karachi" population="9269265"/> |
| <city name="Istanbul" population="8787958"/> |
| <city name="Ciudad de M鸩co" population="8591309"/> |
| <city name="Moscow" population="8389200"/> |
| <city name="New York" population="8008278"/> |
+-----------------------------------------------------------------------+
10 rows in set (0.00 sec)
Importing XML and XML Data
In this section, we discuss some techniques for importing data from XML into a MySQL table.
Using LOAD_FILE(). The simplest way to store XML in MySQL is to use the LOAD_FILE() function to open an entire XML document, make it available as a string, and insert this string into a table column. Using the 2cities.xml file created earlier, it is possible to do something like this:
mysql> USE xmltest;
mysql> CREATE TABLE xmldocs (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> doc_content BLOB NOT NULL,
-> comment VARCHAR(100) NOT NULL DEFAULT ''
-> );
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO xmldocs VALUES
-> (NULL, LOAD_FILE('/tmp/2cities.xml'), '2 cities file');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM xmldocs\G
*************************** 1. row ***************************
id: 1
doc_content: <?xml version="1.0"?>
<resultset statement="SELECT name,country FROM cities LIMIT 2
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="name">Mumbai (Bombay)</field>
<field name="country">India</field>
</row>
<row>
<field name="name">Seoul</field>
<field name="country">South Korea</field>
</row>
</resultset>
comment: 2 cities file
1 row in set (0.00 sec)
Importing mysqldump XML data into MySQL table columns using a stored procedure. Loading an entire XML file into a single row of a MySQL table solves the problem of getting the XML into MySQL, where it can be parsed using MySQL 5.1's XPath functions (see the section called “XPath Functionality”); however having to do so every time you want to access the data s not terribly convenient. However, MySQL developer Alexander Barkov has written a stored procedure xmldump_load that extracts data from the XML elements and attributes found in a file created using mysqldump --xml, and inserts this data into the columns of a MySQL table. The source of this stored procedure is shown here:
DELIMITER |
DROP PROCEDURE IF EXISTS xmldump_load |
CREATE PROCEDURE xmldump_load(file_name VARCHAR(128),
database_name VARCHAR(128),
table_name VARCHAR(128))
BEGIN
DECLARE xml TEXT;
DECLARE nrows INT;
DECLARE rownum INT DEFAULT 1;
DECLARE ncols INT;
DECLARE colnum INT DEFAULT 1;
DECLARE ins_list TEXT DEFAULT '';
DECLARE val_list TEXT DEFAULT '';
DECLARE tmp VARCHAR(255);
# load the XML file's contents into a string
SET xml = LOAD_FILE(file_name);
# get the number of <row>s in this table
SET nrows = ExtractValue(xml,
'count(/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row)');
# get the number of <field>s (columns) in this table
SET ncols = ExtractValue(xml,
'count(/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[1]/field)');
# for each <row>
WHILE rownum <= nrows DO
# for each <field> (column)
WHILE colnum <= ncols DO
SET tmp = ExtractValue(xml,
'/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[$rownum]/field[$colnum]/@name');
SET ins_list = CONCAT(ins_list, tmp, IF(colnum<ncols, ',', ''));
SET tmp = ExtractValue(xml,
'/mysqldump/database[@name=$database_name]/table_data[@name=$table_name]/row[$rownum]/field[$colnum]');
SET val_list = CONCAT(val_list, '''', tmp ,'''', IF(colnum<ncols, ',', ''));
SET colnum = colnum + 1;
END WHILE;
SET @ins_text = CONCAT('INSERT INTO t1 (', ins_list, ') VALUES (', val_list, ')');
SET ins_list = '';
SET val_list = '';
PREPARE stmt FROM @ins_text;
EXECUTE stmt;
SET rownum = rownum + 1;
SET colnum = 1;
END WHILE;
END |
DELIMITER ;
This stored procedure employs XPath user variables (see User variables in XPath expressions) and so runs only under MySQL 5.1.20 or later. You can find a copy of it in the file create-xmpdump-load.sql included with this article.
Important
The caller of this stored procedure must have the MySQL FILE privilege.
You can test xmldump_load using the supplied test.sh script, shown here:
# Demo for xmldump_load()
DB="test"
# Change "root" and "mypass" in the following 2 lines to
# a user and password appropriate to your installation
MYSQL="mysql -uroot -pmypass --socket=/tmp/mysql.sock"
MYSQLDUMP="mysqldump -uroot -pmypass --socket=/tmp/mysql.sock"
# Creates a test table with two columns and fills it with some data
$MYSQL $DB <> END
SELECT VERSION();
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a INT, b VARCHAR(128));
INSERT INTO t1 VALUES (1,'11111');
INSERT INTO t1 VALUES (2,'22222');
INSERT INTO t1 VALUES (3,'33333');
INSERT INTO t1 VALUES (4,'44444');
INSERT INTO t1 VALUES (5,'55555');
INSERT INTO t1 VALUES (6,'66666');
INSERT INTO t1 VALUES (7,'77777');
INSERT INTO t1 VALUES (8,'88888');
INSERT INTO t1 VALUES (9,'99999');
END
# Dumps data into an XML file
$MYSQLDUMP --xml $DB t1 > /tmp/t1.xml
# Empties the table
$MYSQL --execute="DELETE FROM t1" $DB
# Creates the procedure, calls it, and
# makes sure we've restored all records
$MYSQL $DB << END
\. create-xmldump-load.sql
CALL xmldump_load('/tmp/t1.xml', 'test', 't1');
SELECT * FROM t1;
END
# Performs cleanup
# Comment out the remaining lines if you wish to
# preserve the stored procedure, table, and XML
# file following the test run
$MYSQL $DB << END
DROP PROCEDURE xmldump_load;
DROP TABLE t1;
END
rm /tmp/t1.xml
Using LOAD XML. An implementation contributed by Erik Wetterberg of a new SQL statement has been accepted for MySQL 6.0, and is available beginning with version 6.0.3. LOAD XML greatly simplifies the task of importing data from an XML file into a MySQL table, without having to use the stored procedure discussed previously (see Importing mysqldump XML data into MySQL table columns using a stored procedure). The syntax for this statement is as shown here:
LOAD XML [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'filename'
[REPLACE | IGNORE]
INTO TABLE [db_name.]tbl_name
[CHARACTER SET charset_name]
[ROWS IDENTIFIED BY '<tagname>']
[IGNORE number [LINES | ROWS]]
[(column_or_user_var,...)]
[SET col_name = expr,...]
This statement reads data from an XML file into a table, and acts as the complement mysql or mysqldump in XML output mode (that is, using the --xml option). The filename must be given as a literal string. The tagname in the optional ROWS IDENTIFIED BY clause must also be given as a literal string, and must be surrounded by angle brackets (< and >).
LOAD DATA clauses used in LOAD XML. If you have used MySQL's LOAD DATA statement before, then you should find the following clauses familiar, since they work in essentially the same way for LOAD XML as they do for LOAD DATA:
- LOW_PRIORITY or CONCURRENT
- LOCAL
- REPLACE or IGNORE
- CHARACTER SET
- The IGNORE number LINES clause is analogous to the LOAD DATA statement's IGNORE ... LINES clause. LOAD XML also accepts IGNORE number ROWS. In either case, the clause causes the first number rows in the XML file to be skipped, and not to be imported.
- (column_or_user_var,...)
- SET
See LOAD DATA INFILE Syntax in the MySQL 5.1 Manual for more information about these clauses.
ROWS IDENTIFIED BY '<tagname>'.
Accepted input formats.LOAD XML supports three different XML formats:
- Attributes are interpreted as column names, and attribute values as interpreted as column values:
<row column1="value1" column2="value2" .../> - Tag names are interpreted as column names, and the content of these tags are interpreted as column values:
<row>
<column1>value1</column1>
<column2>value2</column2>
</row> - Table column names are derived from the name attributes of <field> tags, and column values are taken from the contents of these tags:
<row>
<field name='column1'>value1</field>
<field name='column2'>value2</field>
</row>This is the format used by MySQL tools such as mysqldump
The import routine used by LOAD XML automatically detects the format used for each row and interprets it correctly, matching based on the tag or attribute name and the column name. You can easily verify this for yourself, by creating an XML file that uses any two or even all three formats and then using LOAD XML to import it into a table.
Important
You must have the FILE privilege to use LOAD XML.
XPath Functionality
In this section, we look at the XPath functions added in MySQL 5.1.5. ExtractValue() allows you to use an XPath expression on a fragment of XML in order to return the content of one or more elements. UpdateXML() makes it possible to replace an existing XML fragment with a new one, using XPath to specify the fragment to be replaced.
The ExtractValue() Function
The example in Using LOAD_FILE() demonstrates how to get the content of an XML file into a MySQL database, but the problem of getting at the actual data remains. One way to accomplish this is to use the ExtractValue() function.
The syntax for this function is shown here:
ExtractValue(xml_fragment, xpath_expression)
ExtractValue() takes two arguments. The first of these is the XML fragment to be tested; the second is the XPath expression to be matched.
ExtractValue() example. Let' see how we might obtain the name of the first city in the document (which we saved as 2cities.xml). We do this in two steps. First, we get the XML from the xmldocs table and place into a user variable:
mysql> SELECT doc_content FROM xmldocs LIMIT 1 INTO @xml;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @xml\G
*************************** 1. row ***************************
@xml: <?xml version="1.0"?>
<resultset statement="SELECT name,country FROM cities LIMIT 2
" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
<field name="name">Mumbai (Bombay)</field>
<field name="country">India</field>
</row>
<row>
<field name="name">Seoul</field>
<field name="country">South Korea</field>
</row>
</resultset>
1 row in set (0.00 sec)
Now we use ExtractValue() with @xml as the first argument. For the second argument, we employ an XPath expression that means “the content of the first <field> element contained in the second <row> element found anywhere in the document”:
mysql> SELECT ExtractValue(@xml, '//row[2]/field[1]');
+-----------------------------------------+
| ExtractValue(@xml, '//row[2]/field[1]') |
+-----------------------------------------+
| Seoul |
+-----------------------------------------+
1 row in set (0.00 sec)
Another way to do this would be to use an XPath expression that means “the content of the second <field> having the name attribute "name"”:
mysql> SELECT ExtractValue(@xml, '//field[@name="name"][2]');
+------------------------------------------------+
| ExtractValue(@xml, '//field[@name="name"][2]') |
+------------------------------------------------+
| Seoul |
+------------------------------------------------+
1 row in set (0.00 sec)
As you can see, the result (Seoul) is the same as before, which is exactly what we would expect.
The UpdateXML() Function
This function allows you to do replace a portion of an XML fragment (identified by an XPath locator) with different XML markup. For example, consider the XML fragment <book><chapter/></book>. Now suppose you wish to change this to <book><part><chapter/></part></book>. This shows how you can do so using UpdateXML(), saving the result into a user variable @new_xml:
mysql> SELECT @new_xml:=UpdateXML('<book><chapter/></book>',
-> '//chapter',
-> '<part><chapter/></part>')
-> AS uxml;
+--------------------------------------------------------------------+
| uxml |
+--------------------------------------------------------------------+
| <book><part><chapter/></part></book> |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @new_xml;
+--------------------------------------+
| @new_xml |
+--------------------------------------+
| <book><part><chapter/></part></book> |
+--------------------------------------+
1 row in set (0.00 sec)
The syntax for this function is shown here:
UpdateXML(xml, locator, replacement)
xml is the XML markup to be tested for a match. locator is the XPath expression used to obtain the match. replacement is the markup to be used to replace the XML that matches the locator . Unlike ExtractValue(), UpdateXML() matches both elements and content, then returns the updated XML as a string. If no match for the locator is found, then the original xml is returned.
XPath support in MySQL 5.1 and 6.0
In this section, we discuss the XPath functionality provided for use with the ExtractValue() and UpdateXML() function in MySQL 5.1 and MySQL 6.0.
Supported functions and operators. Basic XPath expressions (known as locators) are supported. This includes locators using the following operators:
- / (slash) operator. This operator acts in a manner similar to how a slash behaves in a Unix filesystem path, where the leading / represents the root of the document, and the identifier following it is the name of an XML element. For example, /book matches a top-level book element. Multiple slashes can be used to trace a branch; for example, /book/chapter matches <book><chapter/></book> where book is a top-level element and chapter is a child of that element. A leading double-slash (//) means that the pattern is matched anywhere in the XML document; for example, the locator //section matches a section element wherever it is found, regardless of this element' relationship to any other elements in the document.
- * (wildcard) operator. This operator matches any element. For example, //chapter/* matches any element that is a child of a chapter element, anywhere in the document, and /*/section matches any section that is a child of a top-level element.
- | (UNION) operator. This operator can be used to combine locators when you wish to match against any of them. For example, //section|//paragraph matches any section or paragraph element, anywhere in the document.
- Attribute matching. You can match an element by the value of one (or more) of its attributes using the syntax element [@ attribute = value ]. For example, //section[@id='xpath-locators'] matches a section anywhere in the document having an id attribute with the value “ xpath-locators ”.
Note
You can match against multiple attribute values by merely combining them. For instance, the locator //paragraph[@role='intro'][@title='XPath support'] matches any paragraph element (anywhere in the XML document to be tested) having a role attribute whose value is “ intro” and a title attribute whose value is “ XPath support”.
To find elements for which the same attribute matches one of several values, you can use multiple locators joined by the | operator. For example, to match all paragraph elements, anywhere in the XML to be tested, whose title attributes have either of the values “ Example” or “ Syntax ”, you would use the expression //paragraph[@title="Example"]|//paragraph[@title="Syntax"]. You can also use the logical or operator for this purpose: //paragraph[@title="Example" or @title="Syntax"].
The difference between or and | is that or joins conditions, while | joins result sets.
- Namespaces are not explicitly supported. However, element names containing : are allowed, so you can work with XML markup that uses namespace notation. For example, //person:biography matches the tag <person:biography/> anywhere in the document.
- All of the standard XPath comparison operators (or, and, =, !=, <=, <, >=, and >) are supported. For example, consider the following:
mysql> SET @xml = '<foo bar="2">123</foo><foo bar="6">456</foo>';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT ExtractValue(@xml, '//foo[@bar="2"]');
+---------------------------------------+
| extractvalue(@xml, '//foo[@bar="2"]') |
+---------------------------------------+
| 123 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT ExtractValue(@xml, '//foo[@bar>"2"]');
+---------------------------------------+
| extractvalue(@xml, '//foo[@bar>"2"]') |
+---------------------------------------+
| 456 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT ExtractValue(@xml, '//foo[@bar>="2"]');
+----------------------------------------+
| extractvalue(@xml, '//foo[@bar>="2"]') |
+----------------------------------------+
| 123 456 |
+----------------------------------------+
1 row in set (0.01 sec)Notice that when multiple matches are found, ExtractValue() returns them in a single space-delimited string.
- You can also use indexing in locators to identify particular elements of interest, as we showed earlier with //row[2]/field[1].
ExtractValue(), as it name suggests, obtains a value; it does not return any XML elements. Here is an example that should help make this clear:
mysql> SET @xml = '<a>3<b><c>5<d/></c></b></a>';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT ExtractValue(@xml, '//a'), ExtractValue(@xml, '//b'), ExtractValue(@xml, '//c');
+---------------------------+---------------------------+---------------------------+
| ExtractValue(@xml, '//a') | ExtractValue(@xml, '//b') | ExtractValue(@xml, '//c') |
+---------------------------+---------------------------+---------------------------+
| 3 | | 5 |
+---------------------------+---------------------------+---------------------------+
1 row in set (0.01 sec)
ExtractValue(@xml, '//a') returns only the content of any <a> elements; it does not return any of the elements <b>, <c>, or <d>, or any of the content of these elements. ExtractValue(@xml, '//c') returns only the content of any <c> elements. Because the sole <b> element contains no text, but rather only other XML elements, ExtractValue(@xml, '//b') returns an empty string.
Unsupported XPath functions and operators. Many XPath functions and operators are supported; however, MySQL's XPath support is still under development, and so some of these are not yet implemented. These include the following limitations:
-
Relative locator expressions are resolved in the context of the root node. For example, consider the following query and result:
mysql> SELECT ExtractValue(
-> '<book>
-> <paragraph title="Example">P1</paragraph>
-> <paragraph title="Syntax">P2</paragraph>
-> </book>',
-> 'book/paragraph'
-> ) AS paras;
+-------+
| paras |
+-------+
| P1 P2 |
+-------+
1 row in set (0.03 sec)
In this case, the locator book/paragraph is resolved as /book/paragraph.
Relative locators are also supported within predicates. In the following example, ../@title="Example" is resolved as /book/paragraph/@title="Example":
mysql> SELECT ExtractValue(
-> '<book>
-> <paragraph title="Example"><body>P1</body></paragraph>
-> <paragraph title="Syntax"><body>P2</body></paragraph>
-> </book>',
-> 'book/paragraph/body[../@title="Example"]')
-> AS para;
+------+
| para |
+------+
| P1 |
+------+
1 row in set (0.00 sec) -
The :: operator is not supported in combination with node types such as axis ::comment(), axis ::text(), axis ::processing-instructions(), and axis ::node().
However, name tests (such as axis :: name and axis ::*) are supported, as shown in these examples:
mysql> SELECT ExtractValue('<a><b>x</b><c>y</c></a>','/a/child::b');
+-------------------------------------------------------+
| ExtractValue('<a><b>x</b><c>y</c></a>','/a/child::b') |
+-------------------------------------------------------+
| x |
+-------------------------------------------------------+
1 row in set (0.02 sec)
mysql> SELECT ExtractValue('<a><b>x</b><c>y</c></a>','/a/child::*');
+-------------------------------------------------------+
| ExtractValue('<a><b>x</b><c>y</c></a>','/a/child::*') |
+-------------------------------------------------------+
| x y |
+-------------------------------------------------------+
1 row in set (0.01 sec) -
“Up-and-down” navigation is not supported in cases where the path would lead “above” the root element. That is, you cannot use expressions which match on descendants of ancestors of a given element, where one or more of the ancestors of the current element is also an ancestor of the root element (see Bug #16321).
-
The following XPath functions are not supported: id(), lang(), local-name(), name() (a patch has recently been submitted to implement this function, and is likely to appear in MySQL 6.0), namespace-uri(), normalize-space(), starts-with(), string(), substring-after(), substring-before(), and translate().
-
The following axes are not supported: following-sibling, following, preceding-sibling, and preceding.
Error handling. For both ExtractValue() and UpdateXML(), the XPath locator used must be valid and the XML to be searched must be well-formed. If the locator is invalid, an error is generated:
mysql> SELECT ExtractValue('<foo bar="2">123</foo>', '//foo[@bar>="2"]');
+------------------------------------------------------------+
| ExtractValue('<foo bar="2">123</foo>', '//foo[@bar>="2"]') |
+------------------------------------------------------------+
| 123 |
+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT ExtractValue('<foo bar="2">123</foo>', '//foo@bar>="2"]');');
ERROR 1105 (HY000): XPATH syntax error: '@bar>="2"]'
If the XML to be searched is not well-formed, then NULL is returned, and a warning is issued:
mysql> SELECT ExtractValue('<foo bar="2"123</foo>', '//foo[@bar>="2"]');
+-----------------------------------------------------------+
| ExtractValue('<foo bar="2"123</foo>', '//foo[@bar>="2"]');') |
+-----------------------------------------------------------+
| NULL |
+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------------------------+
| Level | Code | Message|
+---------+------+--------------------------------------------------------------------------------------------+
| Warning | 1522 | Incorrect XML value: 'parse error at line 1 pos 13: unknown token unexpected ('>' wanted)' |
+---------+------+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
However, the replacement XML used as the third argument to UpdateXML() is not checked for well-formedness.
Note
Space does not permit a complete discussion of XPath syntax and usage here. For definitive information, see the XML Path Language (XPath) 1.0 standard. The Zvon.org XPath Tutorial also provides a useful resource for those who are new to XPath or who would like a refresher in XPath basics.
User variables in XPath expressions. Beginning with MySQL 5.1.20, you can employ user variables in XPath locators in either (or both) of two forms:
-
Weakly checked. A variable using the syntax $@ variable_name is not checked for type or for whether it has previously been assigned a value. No warnings or errors are issued by the server if a variable has the wrong type or is undefined. In other words, you are responsible for any typographical errors or omissions. For example, if you use $@myvairable instead of $@myvariable, and $@myvairable has not been assigned a value, then MySQL assumes that $@myvairable has a “none” value of the appropriate type, such as 0 or an empty string.
Example.
mysql> SET @xml = '<a><b>X</b><b>Y</b></a>';
Query OK, 0 rows affected (0.00 sec)
mysql> SET @i =1; @j = 2;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @i, ExtractValue(@xml, '//b[$@i]');
+------+--------------------------------+
| @i | ExtractValue(@xml, '//b[$@i]') |
+------+--------------------------------+
| 1 | X |
+------+--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @j, ExtractValue(@xml, '//b[$@j]');
+------+--------------------------------+
| @j | ExtractValue(@xml, '//b[$@j]') |
+------+--------------------------------+
| 2 | Y |
+------+--------------------------------+
1 row in set (0.00 sec)
mysql> SELECT @k, ExtractValue(@xml, '//b[$@k]');
+------+--------------------------------+
| @k | ExtractValue(@xml, '//b[$@k]') |
+------+--------------------------------+
| NULL | |
+------+--------------------------------+
1 row in set (0.00 sec) -
Strongly checked. A variable using the syntax $ variable_name can be declared and used with MySQL's XPath functions when they are called inside stored procedures. Such a variable is local to the stored procedure in which it is defined, and it is checked for type and value.
Example.
mysql> DELIMITER |
mysql> CREATE PROCEDURE myproc ()
-> BEGIN
-> DECLARE i INT DEFAULT 1;
-> DECLARE xml VARCHAR(25) DEFAULT '<a>X</a><a>Y</a><a>Z</a>';
->
-> WHILE i < 4 DO
-> SELECT xml, i, ExtractValue(xml, '//a[$i]');
-> SET i = i+1;
-> END WHILE;
-> END |
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> CALL myproc;
+--------------------------+---+------------------------------+
| xml | i | ExtractValue(xml, '//a[$i]') |
+--------------------------+---+------------------------------+
| <a>X</a><a>Y</a><a>Z</a> | 1 | X |
+--------------------------+---+------------------------------+
1 row in set (0.00 sec)
+--------------------------+---+------------------------------+
| xml | i | ExtractValue(xml, '//a[$i]') |
+--------------------------+---+------------------------------+
| <a>X</a><a>Y</a><a>Z</a> | 2 | Y |
+--------------------------+---+------------------------------+
1 row in set (0.01 sec)
+--------------------------+---+------------------------------+
| xml | i | ExtractValue(xml, '//a[$i]') |
+--------------------------+---+------------------------------+
| <a>X</a><a>Y</a><a>Z</a> | 3 | Z |
+--------------------------+---+------------------------------+
1 row in set (0.01 sec)
Expressions containing user-defined variables of either sort must otherwise (except for notation) conform to the rules for XPath expressions containing variables as given in the XPath specification.
Security Considerations
With any database functionality, you must be mindful of its security implications, and working with MySQL's XML and XPath capabilities is no different in this regard. There are a number of points to consider, including the ability to read from files using LOAD XML INFILE, the fact that the MySQL privilege system does not apply to the content of XML documents, and the possibilities for subversive user input to have unintended consequences.
Loading Data from Files
As with the LOAD DATA statement, the transfer of the XML file from the client host to the server host is initiated by the MySQL server. In theory, a patched server could be built that would tell the client program to transfer a file of the server's choosing rather than the file named by the client in the LOAD XML statement. Such a server could access any file on the client host to which the client user has read access.
In a Web environment, clients usually connect to MySQL from a Web server. A user that can run any command against the MySQL server can use LOAD XML LOCAL to read any files to which the Web server process has read access. In this environment, the client with respect to the MySQL server actually is the Web server, not the remote program being run by the user who connects to the Web server.
You can disable LOAD XML on the server by starting it with --local-infile=0 or --local-infile=OFF. The result is shown in this example:
shell> mysqld_safe --local-infile=OFF &
shell> mysql -uroot xtest
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.4-alpha-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> LOAD XML LOCAL INFILE '/home/jon/person.xml'
-> INTO TABLE person
-> ROWS IDENTIFIED BY '<person>';
ERROR 1148 (42000): The used command is not allowed with this MySQL version
This option can also be used when starting the mysql client to disable LOAD XML for the duration of the client session.
To prevent a client from loading XML files from the server, do not grant the FILE privilege to the corresponding MySQL user account, or revoke this privilege if the client user account already has it:
shell> mysql -uroot -p
Password: ********
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.4-alpha-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> REVOKE FILE ON *.* FROM jon@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
shell> mysql -ujon -p
Enter password: ********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 6.0.4-alpha-debug Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> USE xmltest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> LOAD XML INFILE '/home/jon/person.xml'
-> INTO TABLE person
-> ROWS IDENTIFIED BY '>person>';
ERROR 1045 (28000): Access denied for user 'jon'@'localhost' (using password: YES)
Important
Revoking the FILE privilege (or not granting it in the first place) keeps the user only from executing the LOAD XML INFILE statement or the LOAD_FILE() function; it does not prevent the user from executing LOAD XML LOCAL INFILE. To disallow this statement, you must start the server or the client with --local-infile=OFF, as discussed in the previous section (see the section called “Loading Data from Files”).
In other words, the FILE privilege affects only whether the client can read files on the server; it has no bearing on whether the client can read files on the local filesystem.
XML and MySQL Privileges
The granularity found in MySQL's privilege system with regard to most database objects does not extend to XML documents. A MySQL client having access to an XML document can access the entire document or any part of it, and there is no way in which this can be restricted to particular XML fragments or elements.
Keep in mind that MySQL does not have preventative privileges; that is, you cannot disallow access of a given type on a particular database object. Instead, if you wish to prevent a client from accessing XML found in a database table, then you must grant privileges to this client in such a way that the client has no access to the table at all, or only to columns of that table which do not contain the XML that you do not wish the client to read.
XPath Injection
One of the greatest security threats to applications is code injection whereby malicious code is introduced into the system to gain unauthorized access to privileges and data. These are known to exist in any number of programming and scripting languages; what they all have in common is the exploiting of the assumptions made by developers about the type and content of data input from users. XPath is no exception in this regard.
Let's suppose that your application handles authorization by matching the combination the combination of a login names and password with those found in an XML file named users.xml, whose contents are shown here:
<?xml version="1.0" encoding="UTF-8"?>
<users>
<user id="00327">
<login>douglas42</login>
<password>24ph0d</password>
</user>
<user id="13579">
<login>cherrygarcia</login>
<password>1c3cr34m</password>
</user>
<user id="02403">
<login>jimbob</login>
<password>p4nc4k35</password>
</user>
<user id="42354">
<login>kitten</login>
<password>m3330w</password>
</user>
<user id="28570">
<login>lucyvanpelt</login>
<password>f0076411</password>
</user>
</users>
Assuming that each user's combination of login name and password is unique, your application could use an XPath expression like this one to validate the user, begin a user session, and associate the session with the user's unique ID:
//user[login/text()='cherrygarcia' and password/text()='1c3cr34m']/attribute::id
This is the XPath equivalent of an SQL statement like this one:
SELECT id FROM users WHERE login='cherrygarcia' AND password='1c3cr34m';
A PHP application employing XPath and the users.xml file might handle the login process via a Web form like this:
<?php
$file = "users.xml";
$login = $POST["login"];
$password = $POST["password"];
$xpath = "//user[login/text()=$login and password/text()=$password]/attribute::id";
if( file_exists($file) )
{
$xml = simplexml_load_file($file);
if($result = $xml->xpath($xpath))
echo "You are now logged in as user $result[0].";
else
echo "Invalid login name or password.";
}
else
exit("Failed to open $file.");
?>
The input is completely unchecked, which means that a malevolent user can short-circuit the test by entering ' or 1=1 for both the login name and password, resulting in $xpath being evaluated as shown here:
//user[login/text()='' or 1=1 and password/text()='' or 1=1]/attribute::id
The expression inside the square brackets evaluates as true, and so is effectively the same as this one, which matches the id attribute of every user element in the XML document:
//user/attribute::id
One way in which this particular attack can be circumvented is simply by quoting the variable names to be interpolated in the definition of $xpath:
$xpath = "//user[login/text()='$login' and password/text()='$password']/attribute::id";
This forces the values passed from the user to be converted to strings.
If this “fix” seems familiar, that is because it is the same one that is often recommended for helping to prevent SQL injection attacks. In general, the practices you should follow for preventing XPath injection attacks are the same as for preventing SQL injection:
-
Never accepted untested data from users in your application.
-
Check all user-submited data for type; reject or convert data that is of the wrong type
-
Test numerical data for out of range values; truncate, round, or reject values that are out of range. Test strings for illegal characters and either strip them out or reject input containing them.
-
Do not output explicit error messages that might provide an unauthorized user with clues that could be used to compromise the system; log these to a file or database table instead.
Just as SQL injection attacks can be used to obtain information about database schemas, so can XPath injection be used to traverse XML files to uncover their structure. Space does not permit us to go into detail here, but you can read more about such techniques in Amit Klein's paper Blind XPath Injection (PDF, 46KB).
It is also important to check the output being sent back to the client. For an example, let's revisit our previous example; however, this time, instead of using PHP's XPath functionality, we use the MySQL ExtractValue() function:
mysql> SELECT ExtractValue(
-> LOAD_FILE('users.xml'),
-> '//user[login/text()="" or 1=1 and password/text()="" or 1=1]/attribute::id'
-> ) AS id;
+-------------------------------+
| id |
+-------------------------------+
| 00327 13579 02403 42354 28570 |
+-------------------------------+
1 row in set (0.01 sec)
Because ExtractValue() returns multiple matches as a single space-delimited string, this injection attack provides to the user in a single row every valid ID contained within users.xml. As an extra safeguard, you should also test output before returning it to the user. Here is a simple example:
mysql> SELECT @id = ExtractValue(
-> LOAD_FILE('users.xml'),
-> '//user[login/text()="" or 1=1 and password/text()="" or 1=1]/attribute::id'
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT IF(
-> INSTR(@id, ' ') = 0,
-> @id,
-> 'Unable to retrieve user ID')
-> AS singleID;
+----------------------------+
| singleID |
+----------------------------+
| Unable to retrieve user ID |
+----------------------------+
1 row in set (0.00 sec)
In general, the guidelines for returning data to users securely are the same as for accepting user input. These can be summed up as:
-
Always test outgoing data for type and allowable values.
-
Never allow unauthorized users to view error messages that might provide information about the application that could be used to exploit it.
Following these principles in architecting an XML application can help to insure that it is a secure application.
以下设置都是在 CompizConfig Settings Manager 中进行，以下简称 CompizConfig。
1、给立方体设置一个背景，进入 CompizConfig -> 桌面立方体 -> Appearance -> Skydome 勾选 “Background”，在 Background Images 中选择一个背景图片即可。
2、如果要让立方体增加一个倒影，勾选 CompizConfig -> Cube Reflection 即可。
Tips: 展示立方体默认快捷键是 Ctrl+Alt+鼠标左键拖动，Ctrl+Alt+方向键旋转立方体。
3、使用神灯效果，这个效果最早出现在 Mac 上。
勾选 CompizConfig -> Animations
Tips: 建议取消 CompizConfig -> 最小化效果，勾选 CompizConfig -> 渐弱窗口
4、火焰字
勾选 CompizConfig -> Paint fire on the screen 即可。
可以在 CompizConfig -> Paint fire on the screen -> Fire Particle Color 这里设置火焰的颜色，勾选 Randomly Colored Fire 会写出五彩斑斓的文字。
Tips: 默认快捷键是 shift+win+鼠标左键，按 shift+win+c 是清除效果。
5、桌面展示
勾选 CompizConfig -> Expo
Tips: 默认快捷键是 win+e，鼠标移动到屏幕左上角也可以。
6、雪花效果
勾选 CompizConfig -> Snow
再到这里下载雪花材质，在 Snow -> Textures 这里添加进去即可。
7、菜单透明问题
默认菜单是透明的，可以到 CompizConfig -> General Options -> Opacity Settings 这里进行设置。删除 Window opacities 里面的那行代码就可以了。
8、3D 层叠窗口效果
勾选 CompizConfig -> 3D Windows
9、给立方体中增加一些鱼儿
勾选 CompizConfig -> Cube Atlantis
按住 Ctrl+Alt+鼠标左键拖动桌面，就能看见了。
10、屏幕保护
勾选 CompizConfig -> Screen Saver
有两种效果，一种是 Rotating cube 旋转立方体，一种是 Flying windows 飞行的窗口。
11、3D 窗口切换效果
勾选 CompizConfig -> Shift Switcher
在 Switcher mode 中可以选择两种切换效果，一种类似 Vista 的 Flip3D 切换，一种类似 Mac 的 CoverFlow 切换。
Tips: 默认快捷键是：超级键+Tab
昨天无意中发现了一篇文档，解决了我很久以来的一些问题，于是花了半天的时间把它翻译了一下，可是翻译的却不好，如果您的E文还好的话，建议您直接看原文更好。
这是关于使用iptables来调优防火墙性能的一篇短文。
谢谢freenode 上#debian-zh中的pnt_。
原文地址： http://www.wallfire.org/misc/netfilter_conntrack_perf.txt
翻译：NetDC <fjdc AT 163 DOT com>
如果您有什么好的想法可以和我交流。
转载注明出处，谢谢。
Netfilter conntrack 性能调整，v0.6
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Herv?Eychenne <rv _AT_ wallfire _DOT_ org>
这篇文档说明了对netfilter conntrack（还有NAT）性能调整时你必须知道的一些事情。
本文档最新版可以在下面这个地址找到：
http://www.wallfire.org/misc/netfilter_conntrack_perf.txt
------------------------------------------------------------------------------
这里我们可以调整两个参数：
－允许的最大跟踪连接条目，在这篇文档中我们叫作CONNTRACK_MAX
－存储跟踪连接条目列表的哈西表的大小，在这篇文档中我们叫做HASHSIZE（下面是这个结构的描述）
CONNTRACK_MAX是在内核内存中netfilter可以同时处理的“任务”（连接跟踪条目）。
一个跟踪连接的条目是存储在一个链接起来的列表的一个节点上，每个列表都是一个哈西表的元素。因此每个哈西表的条目（也叫一个桶－bucket）包含了一个链接起来的跟踪连接条目。
要访问一个特定包的跟踪连接条目，内核必须：
－针对一个包中的已经定义的一些字符计算哈西值。这是一个不间断的计算。
这个哈西值就会被当作哈西表的索引来使用，而跟踪连接条目的列表就存储在这里。
－反复的查看链接列表中的跟踪连接条目以找到匹配的那一个。
这是一个耗资源的操作，依赖于列表的大小（也依赖于列表中被操作的跟踪连接条目的位置）。
哈西表包含了HASHSIZE大小的链接条目。当条目满的时候（总的跟踪连接条目数达到了CONNTRACK_MAX），理想状态下，每个列表（在最优化的条件下）将包含大约CONNTRACK_MAX/HASHSIZE的条目数。
不管你是否有连接，哈西表都将占用一个固定大小的非交换内核内存。但是最大连接跟踪条目会检测最多可以存储多少条目（globally into the
linked lists），也就是说他们最多可以占用多少的内核内存。
这篇文档将给你一些关于为了达到最好的netfilter conntracking/NAT系统性能，如何调优HASHSIZE和CONNTRACK_MAX值的提示。
CONNTRACK_MAX和HASHSIZE的默认值
============================================
一般来说，CONNTRACK_MAX和HASHSIZE都会设置在“合理”使用的值上，依据可使用的RAM的大小来计算这个值。
CONNTRACK_MAX的默认值
------------------------------
在i386架构上，CONNTRACK_MAX = RAMSIZE (以bytes记) / 16384 =
RAMSIZE (以MegaBytes记) * 64，
因此，一个32位的带512M内存的PC在默认情况下能够处理512*1024^2/16384 = 512*64 = 32768个并发的netfilter连接。
但是真正的公式是：
CONNTRACK_MAX = RAMSIZE (in bytes) / 16384 / (x / 32)
这里x是指针的bit数，（例如，32或者64bit）
请注意：
－默认的CONNTRACK_MAX值不会低于128
－对于带有超过1G内存的系统，CONNTRACK_MAX的默认值会被限制在65536（但是可以手工设置成更大的值）
HASHSIZE的默认值
-------------------------
通常，CONNTRACK_MAX = HASHSIZE * 8。这意味着每个链接的列表平均包含8个conntrack的条目（在优化的情况并且CONNTRACK_MAX达到的情况下），每个链接的列表就是一个哈西表条目（一个桶）。
在i386架构上，HASHSIZE = CONNTRACK_MAX / 8 =
RAMSIZE (以bytes记) / 131072 = RAMSIZE (以MegaBytes记) * 8。
举例来说，一个32位、带512M内存的PC可以存储512*1024^2/128/1024 =
512*8 = 4096 个桶（链接表）
但是真正的公式是：
HASHSIZE = CONNTRACK_MAX / 8 = RAMSIZE (以bytes记) / 131072 / (x / 32)
这里x是指针的bit数，（例如，32或者64bit）
请注意：
－默认HASHSIZE的值不会小于16
－对于带有超过1G内存的系统，HASHSIZE的默认值会被限制在8192（但是可以手工设置成更大的值）
读取CONNTRACK_MAX和HASHSIZE
==================================
现在通过/proc文件系统我们可以在运行时读取CONNTRACK_MAX的值。
在Linux kernel 2.4.23版本前，使用：
# cat /proc/sys/net/ipv4/ip_conntrack_max
在Linux kernel 2.4.23版本后，使用：
# cat /proc/sys/net/ipv4/netfilter/ip_conntrack_max
(旧的 /proc/sys/net/ipv4/ip_conntrack_max是不建议使用的!)
当前的HASHSIZE总是可以在syslog信息中找到（对任何一个内核版本），桶（也就是HASHSIZE）的数目是在ip_conntrack初始化的时候显示出来的。
对于linux内核2.4.24以后，当前的HASHSIZE值可以在运行时使用下面的命令读取：
# cat /proc/sys/net/ipv4/netfilter/ip_conntrack_buckets
修改CONNTRACK_MAX和HASHSIZE
====================================
默认的CONNTRACK_MAX和HASHSIZE的值都会因主机的不同而不同，但你可以在只做防火墙的高负载的系统上增加他们。
因此CONNTRACK_MAX和HASHSIZE值如果需要的话可以手工更改。
读取桶是一个连续性的操作（我们的兴趣在于得到一个哈西列表），请记得内核需要不停的遍历一个链接的列表去查找一个跟踪连接条目。因此一个链接列表（CONNTRACK_MAX/HASHSIZE的值在优化的状态下并且达到上限）的平均值不能设置太大。这个比值默认值是8（当值是自动计算的时候）。
在系统有足够的内存并且性能真的很重要的时候，你可以试着使平均值是一个跟踪连接条目配一个哈西桶，这意味着HASHSIZE = CONNTRACK_MAX。
设置CONNTRACK_MAX
---------------------
跟踪连接的条目是存储在链接的表中的，因此最大的跟踪链接条目（CONNTRACK_MAX）可以很容易的动态调整。
linux内核2.4.23之前，使用：
# echo $CONNTRACK_MAX > /proc/sys/net/ipv4/ip_conntrack_max
linux内核2.4.23之后，使用：
# echo $CONNTRACK_MAX > /proc/sys/net/ipv4/netfilter/ip_conntrack_max
这里$CONNTRACK_MAX是一个整数。
设置HASHSIZE
----------------
因为数学上的原因，哈西表占有固定的大小。因此HASHSIZE必须在哈西表被创建和开始填充之前就确定。
在linux内核2.4.21之前，必须使用素数作为哈西表的大小，而且要保证这个哈西表能够有效并通用。非素数的奇数或者其他的数值都是强烈不推荐使用的，因为这样哈西的分配不能达到最优化的状态。
从linux内核2.4.21（还有2.6内核）跟踪连接使用jenkins2b算法，这样就可以使用所有的数值，但是使用2^n次方运作的最有效。
如果netfilter的跟踪连接是被编译进内核中的，哈西表的大小就可以在编译的时候设置，或者（2.6内核之后）可以作为一个启动选项ip_conntrack.hashsize=$HASHSIZE。
如果netfilter的跟踪连接是编译成一个模块，哈西表的大小可以在加载模块的时候设置，使用下面的命令：
# modprobe ip_conntrack hashsize=$HASHSIZE
这里$HASHSIZE是一个整数。
一个理想的例子：只做防火墙的机器
------------------------------------
在理想的例子中，你有一台机器只做包过滤和NAT（也就是说，基本上没有用户空间的使用，至少不会有象代理这样会不断的耗费内存空间的东西......）
netfilter跟踪连接使用的内核内存大小是：
size_of_mem_used_by_conntrack (以bytes记) =
CONNTRACK_MAX * sizeof(struct ip_conntrack) +
HASHSIZE * sizeof(struct list_head)
－这里：sizeof(struct ip_conntrack)可以有很大的区别，依赖于机器的体系架构，内核版本和编译时间的配置。要想知道它的大小，可以查看ip_conntrack初始化时候kenel的日志信息。sizeof(struct ip_conntrack)在i386架构、2.6.5内核上大约是300bytes，但是在2.6.10的内核上，这个值可以在352至192bytes之间变化！
－sizeof(struct list_head) = 2 * size_of_a_pointer
在i386上，size_of_a_pointer是4bytes。
因此在i386，2.6.5内核上，size_of_mem_used_by_conntrack大约是CONNTRACK_MAX * 300 + HASHSIZE * 8 (bytes)。
如果我们使HASHSIZE = CONNTRACK_MAX（如果我们将大部分的内存用来做防火墙的工作，参见“修改CONNTRACK_MAX和HASHSIZE”部分），在i386架构、2.6.5内核上，size_of_mem_used_by_conntrack大概是CONNTRACK_MAX * 308 bytes。
现在我们假定你使用512M的内存拿来做一个只做防火墙的机器，并且使用128MB以外的内存来做跟踪连接，对于使用终端模式只做防火墙来说应该是足够的大的，例如：
你可以同时设置CONNTRACK_MAX和HASHSIZE大致如下：
(512 - 128) * 1024^2 / 308 =~ 1307315 (instead of 32768 for CONNTRACK_MAX,
and 4096 for HASHSIZE by default)。
对于linux2.4.21（和linux2.6），哈西算法最好使用“2的次方”大小（之前是使用素数）。
因此在这里我们可以将CONNTRACK_MAX和HASHSIZE设置成1048576（2^20）。
这样，你可以存储默认值32倍的跟踪连接条目，而且可以得到更好的跟踪连接性能。
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
最后更新：2005年1月20日
修正历史：
Revision history:
0.6 Hashsize parameter can be set at boot time with Linux 2.6. Thanks to
Tobias Diedrich for pointing this out.
0.5 Added further notice about the varying length of the conntrack structure.
0.4 Since Linux 2.4.21, hash algorithm is happy with all sizes, not only
prime ones. However, power of 2 is best.
0.3 Various small precisions.
0.2 Information about Linux kernel versions and corresponding /proc entries.
(/proc/sys/net/ipv4/netfilter/ip_conntrack_{max,buckets}).
0.1 Initial writing, largely based on my discussions with Harald Welte
(netfilter maintainer) on the netfilter-devel mailing-list. Many thanks
to him!
