Memory (de)allocation,(de)fragmentation, and chkfrag!

Tweet (this blog post applies to MySQL Cluster 6.3 and MySQL Cluster 6.4/7.0)

A while ago I discussed Memory Allocation in MySQL Cluster. Here it comes again, but we also discuss fragmentation and a tool to help analyze if there is fragmentation.

The main problem I want to address here is that "I filled up 100% of my DataMemory, then deleted 40% and I cannot create a new table!!". Now this is not such a common problem, but can happen in systems with a lot of insert/deletes (especially on VAR* columns), and can be an issue if you need to create a new table. If the allocation patterns are always on the same set of tables, then this is less of an issue.

Allocation

1. If the table is empty and there is an insert - one page will be allocated from the page pool (DataMemory) and associated with that table.
   
2. Subsequent inserts will fill up the data page until it is full.
3. NEW: Attributes in a table are divided into a fixed size part, and a variable part (if the table has VAR* or even DYNAMIC attributes). The fixed size part is stored together with the record header and from there is a reference to the VAR*/DYNAMIC part. The VAR*/DYNAMIC part and the FIXED part are stored on different pages and are never mixed!
4. The allocator allocates one page at a time!
5. If there is a hole on an already allocated page, and this hole fits the data that should be inserted, then this hole will be used.
6. 5) means that space within a page that is associated with a table will be reused.
   

Deallocation

• From MySQL Cluster 6.3 there is per page de-allocation, so an empty page after a DELETE will release the pages to the DataMemory page pool.
  
• TRUNCATE/DROP TABLE will release the pages to the DataMemory page pool.

Fragmentation/Defragmentation

• The pages will be fragmented when you insert/delete record.
• OPTIMIZE TABLE can help to defragment pages within a table, and free (empty) pages will be put on the page pool (see Deallocation above).
  
• In MySQL Cluster - rolling restarts of the data nodes will not defragment the FIXED size pages, but VAR*/DYNAMIC pages will be defragmented.

But how do you know if you have fragmentation or not? A new tool, chkfrag (beta) can help you.

Back to the problem: "I filled up 100% of my DataMemory, then deleted 40% and I cannot create a new table!!". Why is that?

Pretened that we have a MySQL Cluster up and running. We have only four pages and all of them are being used (figure 1):


We cannot create a new table at this stage, because we don't have an free pages that can be used to store records.

Now we delete 40% of the records. This frees up space on the pages (shown in figure 2). We can still not create a new table, but if we can of course insert new records into t1 provided they fit. OPTIMIZE TABLE can help to reduce fragmentation.
In its current form and foreseeable future, OPTIMIZE TABLE, will only compact the VAR* pages. Fixed size will not be compacted. And frankly, it is not great on defragmentation either. A number of IFs and buts about record sizes etc mak
es it rather inefficient (in most cases). It would be good to have the option to run it more aggressive. Figure 3 shows what it could look like after OPTIMIZE TABLE.
Currently, the most efficient to defragmentation method is simply to perform a rolling restart (figure 4).. and chkfrag let's you know if you need to!
Example
Below follows an example on fragmentation and how chkfrag can help:

CREATE TABLE `t5` (
`id` varchar(32) CHARACTER SET utf8 NOT NULL,
`name` varchar(32) NOT NULL DEFAULT '',
`unit` int(11) DEFAULT NULL,
PRIMARY KEY (`id`,`name`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1

Then we use hugoLoad (a test program that isn't built by default in the src distribution of cluster) to load it with 1M records.

./hugoLoad -r 1000000 -d test t5

Checking fragmentation:
johan@homecomputer:~/dev/chkfrag$ src/chkfrag
Gathering information how much DataMemory is being used
Used DataMemory calculated: 338MB 10816 pages (32KB)
Used DataMemory reported: 324MB 10368 pages (32KB)
Total DataMemory reported: 512MB 16384 pages (32KB)
Percentage of VAR* in database: 42.01 percent
There is no fragmentation!

In this case there is a discrepancy between Calculated DataMemory and real data memory.
That is ok, but when multiplying the difference with the percentage of VAR* in the database, then the fragmenation is too small, i.e, less than 10MB. Btw, this value is taken out of the blue..so there is no science with that.

deleting 200000 random records and checking fragmentation again:

Gathering information how much DataMemory is being used
Used DataMemory calculated: 270MB 8640 pages (32KB)
Used DataMemory reported: 324MB 10368 pages (32KB)
Total DataMemory reported: 512MB 16384 pages (32KB)
Percentage of VAR* in database: 41.85 percent
Fragmentation of VAR* in database: 16.67 percent
Possibly lost memory (locked in by tables): 22.60MB

Recommendation:
1. Run OPTIMIZE TABLE on tables with a lot of VARCHAR/VARBINARY.
If you are not happy with the result of this then do step 2)!
2. Perform a rolling restart.

Running the OPTIMIZE TABLE:

mysql> optimize table t5;
+---------+----------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------+----------+----------+----------+
| test.t5 | optimize | status | OK |
+---------+----------+----------+----------+
1 row in set (3 min 42.44 sec)

Checking the fragmentation:

Gathering information how much DataMemory is being used
Used DataMemory calculated: 270MB 8640 pages (32KB)
Used DataMemory reported: 324MB 10368 pages (32KB)
Total DataMemory reported: 512MB 16384 pages (32KB)
Percentage of VAR* in database: 41.85 percent
Fragmentation of VAR* in database: 16.67 percent
Possibly lost memory (locked in by tables): 22.60MB

Recommendation:
1. Run OPTIMIZE TABLE on tables with a lot of VARCHAR/VARBINARY.
If you are not happy with the result of this then do step 2)!
2. Perform a rolling restart.


Hmm... OPTIMIZE TABLE does not work so great (i have however seen it reclaimin some pages sometimes..).
And after a rolling restart:

Gathering information how much DataMemory is being used
Used DataMemory calculated: 270MB 8640 pages (32KB)
Used DataMemory reported: 272MB 8704 pages (32KB)
Total DataMemory reported: 512MB 16384 pages (32KB)
Percentage of VAR* in database: 41.48 percent
Fragmentation of VAR* in database: 0.74 percent
Possibly lost memory (locked in by tables): 0.83MB

We have reclaimed 324MB-272MB=52MB!

Conclusion:
A dba should never have to perform a rolling restart in order to defragment (and arguably in no other case either). OPTIMIZE TABLE should be improved and perhaps have a "FULL" flag, indicating how thorough it should be.

bencher - a benchmarking utility for MySQL Cluster

Tweet bencher is a test program that allows you to benchmark requests on MySQL Cluster. I have used this utility a lot of customers, because it lets me:

• specify a simple query that I want to benchmark on the command line
  
• implement more complex use cases.
  
• implement NDBAPI requests

and I don't have to reinvent the wheel every time. It is all there: connectivity, multi-threading support, timers, and some basic statistics, and it compiles on most platforms. I just have to focus on the queries I want to optimize or benchmark.

The simple use case is to specify the SQL query you want to benchmark, the number of threads, and how many times. You can also customize this very easily to benchmark more elaborate SQL requsts, and NDBAPI requests.

bencher outputs per thread statistics and total throughput:

./src/bencher -s /tmp/mysql.sock.3306 -t 2 -l 10000 -q "select * from t1 limit 1"

------- Starting Benchmark ----------

Thread 1 - 638 qps (average qps measured after 5 secs)
Thread 0 - 631 qps (average qps measured after 5 secs)
Thread 1 - 680 qps (average qps measured after 10 secs)
Thread 0 - 679 qps (average qps measured after 10 secs)

------- Benchmark Finished ----------

Thread 0 - max: 83091 us, min 668 us, less than 5 ms: 9761 of 10000, avg: 1485 us, total time: 14949 ms, qps: 668.91
Thread 1 - max: 43743 us, min 578 us, less than 5 ms: 9770 of 10000, avg: 1475 us, total time: 14767 ms, qps: 677.16

Total throughput = 1346.08 qps
Average exec time per thread = 14.86 secs


You can also specify a "querytime-threshold", to see how many transaction have executed under a certain time (default is 5 ms). From the above you can see that for this particular query 9760/10000 requests finished within 5 ms.

cmon 0.13 released

Tweet The main fixes in cmon 0.13 are:

• filtering of clusterlog
• better graphs interface with filtering
  
• fixed bug in load average graphs. Now the loadavg graphs works
• added replication role, socket when adding a mysql server to be monitored from the web interface.
  
• corrected bug with how often mysql_variables are collected
• fixed problems with graphs
• added images directory
• revamped graphs - now producing files in images directory,
• ajax support for Storage graphs
• divided helpers into smaller files

Upgrade from cmon 0.12

• Stop cmon 0.12 (e.g. killall cmon from the command line)
• Execute this sql script on the cmon database.
• Start cmon 0.13
  
• If the above does not work, please report a bug. You can also try to execute drop database cmon and let cmon recreate it when it starts up,
  

You can download it at severalnines.com/cmon.

Thanks for all feedback and bug reports!

MySQL Cluster Sandbox - test cluster to cluster replication!

Tweet If you have max 15 minutes and want to try out MySQL Replication (geo redundancy) between two Clusters on your localhost (only tested on Linux) or on vmware then this Sandbox is for you.

Here is what you have to do:

1. Go to www.severalnines.com/sandbox
2. Create a master cluster:
   I have used the ports 1186 (for the management server) and 3306 and 3307 for the mysql servers).
   
3. Enter the email address and a set of scripts to install cluster will be sent.
4. Create a slave cluster:
   Make sure you use different ports for the slave cluster. I have used the ports 1187 (for the management server) and 3310 and 3311 for the mysql servers).
   
   

Master Cluster:

tar xvfz mysqlcluster-63-master.tar.gz
cd mysqlcluster-63-master
cd scripts
If you have the gnu tool chain (g++/gcc/make) you can build from source:
sh download-and-compile.sh
sh dist-src-build.sh
or if you prefer pre-built binaries (you must chose the "non-rpm" (i.e. the .tar.gz) and the mysql-...tar.gz must not be in /tmp !!):
sh dist-tgz mysql-cluster-gpl-6.3.17-linux-i686-glibc23.tar.gz
Then we have to setup the mysql servers and start the cluster:
sh bootstrap.sh
sh start-cluster-initial.sh
Connect the mysql client to the mysql server:
sh mysqlclient-1.sh
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost' IDENTIFIED BY 'password';
We don't want the GRANT to be replicated (and clear out old stuff in the binlogs that we might have):
RESET MASTER;
and once more (on both mysql servers since grants done locally on each mysql server)..
sh mysqlclient-2.sh
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost' IDENTIFIED BY 'password';
RESET MASTER;

Slave Cluster:

tar xvfz mysqlcluster-63-slave.tar.gz
cd mysqlcluster-63-slave
cd scripts
If you have the gnu tool chain (g++/gcc/make) you can build from source:
sh download-and-compile.sh
sh dist-src-build.sh
or if you prefer pre-built binaries (you must chose the "non-rpm" (i.e. the .tar.gz) and the mysql-...tar.gz must not be in /tmp !!):
sh dist-tgz mysql-cluster-gpl-6.3.17-linux-i686-glibc23.tar.gz
Then we have to setup the mysql servers and start the cluster:
sh bootstrap.sh
sh start-cluster-initial.sh
Connect the mysql client to the mysql server:
sh mysqlclient-1.sh
CHANGE MASTER TO master_host='localhost', master_port=3306, master_user='repl', master_password='password';
START SLAVE;
SHOW SLAVE STATUS\G

Create a table on the master:
sh mysqlclient-1.sh
use test
CREATE TABLE t1 (a INTEGER PRIMARY KEY) ENGINE=NDB;
INSERT INTO t1(a) VALUES (1);
Go to the slave:
sh mysqlclient-1.sh
use test
SELECT * FROM t1;

Read more on replication and learn how to do link failovers etc.

cmon 0.12 - with diskdata support and a lot of fixes

Tweet cmon 0.12 is now released and can be downloaded from www.severalnines.com/cmon.

Release notes:

• connection handling - cmon will now retry connects forever. cmon will also recover and automatically reconnect to cluster after a cluster failure or if the connection to the mysql server crashes where cmon logs information. This means you will have to terminate the cmon using e.g 'killall cmon' or /etc/init.d/cmon stop
• disk data - cmon now gathers information about the amount of table space used and this is presented in the web interface
  
• init.d scripts - fixed bugs in creating the init.d scripts during make install-extra
• fixed compiler warnings - now 0 compiler warnings on Linux systems
• mysql_statistics table - now explicitly using engine=myisam
• web interface - changed printout if cmon cannot connect to the cmon mysql server (suggests you to change $mysql_hostname in {wwwroot}/cmon/cmon_db_conf.inc.php
  
• web interface - changed "Memory" to "Storage" which now contains table space information
• web interface - print hostname instead of ipaddresses if possible
• cmon has been tested on SUSE 10 (SLES 10).
  
• documentation bug - wwwroot for SUSE is /srv/www/htdocs/

Upgrading from cmon 0.11

• cmon 0.12 will add a new table called 'diskdata'. This will be added automatically when you start cmon-0.12. If you prefer, you can download the sql script and create it manually.
• don't forget to do ./configure --with-wwwroot={documentroot of www server} if you want to easily install the www files for cmon using 'make install-extra'
• make install-extra will also install the init.d scripts.
  

External integration
cmon was successfully integrated at a customer who was using Solarwinds IpMonitor as the central monitor for all their IT services.
By using SQL to select the cluster state, node state, etc from the cmon database, and raise alarms and notifications when needed, the customer's MySQL Cluster solution could be monitored from one central point.

Todo

• brush up web interface (make it look nicer)
• add replication link monitoring
• add smtp/snmp notifications