Getting Started with MemSQL¶
First Steps¶
Getting MemSQL up and running is fast and easy. To unwrap MemSQL, run
$ tar -xzf memsqlbin_amd64.tar.gz
$ cd memsqlbin
The first thing you need to do is verify that your system is compatible with MemSQL, and then install any necessary dependencies. MemSQL comes packaged with a script that does this for you. Just run
$ ./check_system
and the script will guide you through any steps you need to take.
To start the server, just run
$ ./memsqld
You can then connect to the server with the standard mysql client:
$ mysql -u root -h 127.0.0.1 -P 3306 --prompt="memsql> "
And you’re all set! The client will indicate the “Server version” as “5.5.8 MemSQL source distribution.”
Warning
The string "localhost" will not work in place of the IP address 127.0.0.1 in the commands above, because the MySQL client defaults to connecting to localhost via Unix-domain sockets rather than via the specified TCP port. This also means the host must always be explicitly given on the command line, because otherwise it defaults to localhost.
Warning
There’s a good chance you’ve already got MySQL running on your system and occupying port 3306. If so, you’ll see an error like:
120501 3:04:15 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use
120501 3:04:15 [ERROR] Do you already have another mysqld server running on port: 3306 ?
120501 3:04:15 [ERROR] Aborting
The fastest way to get around this is to just add -P 3307 to the ./memsqld command when you start it up. You can then connect with mysql -u root -h 127.0.0.1 -P 3307 --prompt="memsql> ".
Warning
If you see an error like:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (user password: YES)
then you probably have a default password configured in your global my.cnf file. The client refers to this file for default parameters to use when connecting to the database, so if you have a line resembling:
password = XXXXXX
then the client will default to connecting to MemSQL with that password. MemSQL comes installed with a root user and no password, so to resolve this issue you have two simple workarounds:
- Remove this line from my.cnf
- Specify --password='' as an argument to the connection command: mysql -u root --password='' -h 127.0.0.1 -P 3306 --prompt="memsql> "
Note
Throughout this documentation we assume that MemSQL is running locally on port 3306. We indicate this with the switches -h 127.0.0.1 and -P 3306, respectively. If you run MemSQL on a different host/port combination, just replace these values as you follow along the examples.
Querying the Database¶
You can start running database commands just as you would in MySQL. Here are some simple queries to get you started:
> create database test;
> use test;
> create table T (id int primary key);
> insert into T values (1);
> insert into T values (2), (3);
> select * from T;
> update T set id = id + 3;
> select * from T;
Note
You’ll notice that the first time you try a new kind of query, it takes a while to run. Every subsequent execution, however, completes quickly. This is actually the core of why MemSQL is so fast. The technique is called code generation - every time MemSQL sees a new query skeleton (the query without numbers and strings), it generates and compiles C++ code and then loads the compiled query plan into the database. The next time it sees a query with the same skeleton, MemSQL runs the query as a tightly optimized sequence of native instructions. Run show plancache to see a list of compiled plans.
Stopping MemSQL¶
To stop the MemSQL database, you just need to send SIGTERM to the MemSQL process. The easiest way to do this is by running:
$ killall memsqld
MemSQL has disk-durability turned on by default. Once you restart the server, it will recover the data present before the shutdown by reading snapshot and log files.