C # and Newsql to get started with--cockroachdb (probably the all-network starter under C #)

Read Catalogue

• What is COCKROACHDB?
• Environment deployment
• Actual combat
• Performance testing
• Conclusion

First, what is COCKROACHDB?

COCKROACHDB (https://www.cockroachlabs.com) is Google's high-profile open-source imitation of spanner , promising to provide a highly viable, consistent, scale-out SQL database . The main design goals are global consistency and reliability, as can be seen from the name of cockroaches (cockroach). The cockroach node is balanced and is designed with homogeneous deployment (only one binary package) and minimal configuration. The extension of the cockroachdb is very easy, as long as a single line of command, second-level.

II. deployment of the environment

The official documents were written in very detail, just as they were done. Deployment of the document (https://www.cockroachlabs.com/docs/stable/install-cockroachdb.html), the author on CentOS directly using binary method of deployment.

Start Cockroachdb after installation and start the Command Reference document (https://www.cockroachlabs.com/docs/stable/start-a-node.html).

　　

Cockroach Start----store=hello-1--host=192.168.118.21

　　

Explain this line of command: Start a cockroachdb node, the storage location is the directory under the installation of Hello-1, and use 192.168.118.21 for host, if necessary, you can also specify the port of the management site and the database external port, the corresponding parameter is-- Port = 26257 (default) 、--Http-port = 8080 (default value).

OK, after the launch can be entered in the browser in the host specified IP: port access to the management site, interface such as 1:

"Figure 1"

Third, actual combat

Create an account first, using the Cockroach user command before you actually call the C # code. It is important to note that if the IP and port are specified before the node is started, the same needs to be specified . Again, create a database and set permissions for the created account. The code is as follows:

Cockroach user set Testaccount--insecure--host=192.168.118.21--insecure-e ' CREATE DATABASE test '--host= 192.168.118.21--insecure-e ' GRANT all on DATABASE test to Testaccount '  --host=192.168.118.21

In fact, after installing COCKROACHDB, you will bring your own SQL Client, which can run all the SQL statements. Of course we don't discuss this here for the time being, interested partners can go to (https://www.cockroachlabs.com/docs/stable/use-the-built-in-sql-client.html).

Since COCKROACHDB supports PostgreSQL's protocol, we can use C # to compare the popular PostgreSQL drivers: Https://github.com/npgsql/npgsql. What we need to use is actually the DLL in which the Npgsql project was compiled. Okay, start creating a table and initializing 2 data:

            using(varconn =NewNpgsqlconnection ("server=192.168.118.21; port=26257; User Id=testaccount; Password=testaccount;database=test; timeout=0; Command timeout=0") {Conn.                Open (); Conn. ExecuteNonQuery ("CREATE TABLE IF not EXISTS accounts (id int PRIMARY KEY, balance int)"); using(varcmd =NewNpgsqlcommand ("INSERT into accounts (ID, balance) VALUES (1, +), (2, +)", conn)) {cmd.                ExecuteNonQuery (); }            }

You can see that the syntax supported by the statement that is being built is different from SQL Server, and you can directly follow the CREATE table to determine if the tables exist. OK, after execution you can click on the "DATABASES" tab in the upper left corner of the admin site to see the following database (test) and table (accounts), Figure 2:

"Figure 2"

OK, let's try to read the data:

            using(varconn =NewNpgsqlconnection ("server=192.168.118.21; port=26257; User Id=testaccount; Password=testaccount;database=test; timeout=0; Command timeout=0") {Conn.                Open (); using(varcmd =NewNpgsqlcommand ("SELECT ID, balance from accounts", conn)) {                    using(varReader =cmd. ExecuteReader (Behavior)) { while(reader. Read ()) {Console.WriteLine (@"Reader[id] is {0}", reader["ID"]); Console.WriteLine (@"Reader[balance] is {0}", reader["Balance"]); }                    }                }            }

One thing to be aware of here is that. Npgsql If a connection string is used to initialize the connection, its default connection mode is connection pooling mode. in this mode, once the created Npgsqlconnection is Dispose () once, subsequent instantiation of a new npgsqlconnection will also cause an error. If you want to use this kind of short link, you need to use Npgsqlconnectionstringbuilder to instantiate the connection, and specify Pooling = True , as follows.

            var New Npgsqlconnectionstringbuilder ("server=192.168.118.21; port=26257; User Id=testaccount; Password=testaccount;database=test; timeout=0; Command timeout=0")            {                true            }. ToString ();             using (varnew npgsqlconnection (connstring))

Iv. Performance Testing

The test client machine is a 4-core i5-4300u, and the machine where the database resides is the 8-core Xeon (R) E5630. In order to make a corresponding comparison, I installed SQL Server on LUnix in the machine where the COCKROACHDB was located, and also used the SQL SERVER2008 on Windows Server installed in the same configuration. See Figure 3 (click on the image for a larger view):

"Figure 3"

You can focus on the database Response Time column, where the time is pure database response + 2 network I/O time, reduce the database Access SDK impact on the results. However, it is clear from the thread time column that the cost of connection in Nqgsql is 2 orders of magnitude larger than the SqlConnection in the. NET Framework , so we need to consider encapsulating a layer of connection pooling when we use it.

The result of the test is thatCockroachdb has more than 3 times times the performance of SQL Server in long-connection mode, and other scenarios are completely useless , so if you want to use COCKROACHDB, do it around this pattern. In addition, SQL Server in your own Windows and LUnix are still a little different, using the default settings, performance a few percentage points, of course, the version of SQL Server may also have some impact, there is no further testing, the results are for reference only.

V. Conclusion

Then do the above series of work, also to cockroachdb some information to do a more in-depth understanding. It is mostly around the beginning, "scalable, geographically (global) replicated and compatible with the ACID characteristics of the transaction's distributed database", and does not apply to complex join or OLAP-type requirements Scenarios . But it is still very young, currently only 3 years old, from the heat of GitHub, its future development is a bright future, and Google's investment in the go language for the benefit of the COCKROACHDB is direct, so maintain continued attention.

In addition, if you do have cross-room multi-data center needs, and several computer room location is not far away, to NTP (if the COCKROACHDB based on the implementation of NTP HLC interested, you can go to this article: http://www.jianshu.com/p/ 8500882ab38c) The clock error control in a certain acceptable range, I think it is worth a try. After all, dynamic expansion and rebalance really is your finger moving seconds of things, operation and maintenance costs are very low.

Zachary_fan
Source: http://www.cnblogs.com/Zachary-Fan/p/cockroachdb_net_csharp.html

If you want to get a personal self-written article in time to push the message, Welcome to scan the following QR code ~.

C # and Newsql to get started with--cockroachdb (probably the all-network starter under C #)