Nix World

blog

Archive for the ‘Mysql Clusters’ category

How to scalable your application using nosql

May 10th, 2012

How to scalable your application

Most of the scalable application on web use MYSQL+memcached+varnish cache as the back end for their applications. Recently some of them shifted to NOSQL for one of the biggest reason ‘performance’. Certainly, NOSQL performs better than MYSQL for simple queries and primary key look-ups.

Most of the database queries of web applications are simple and related to primary key lookups. So, it seems to be healthy decision of migrating to NOSQL from MYSQL.

Yoshinori Matsunobu recently launched a MySQL plug-in ‘HandlerSocket’ which implements protocol for MySQL. The plug-in allows applications to communicate directly with MySQL storage engines, without the overhead associated with using SQL. Operations such as parsing and optimizing queries, as well as table handling operations (opening, locking, unlocking, closing) are also included in this plug-in.

As a result, HandlerSocket can provide much better performance for applications that using simple queries and primary key look-ups.

How it Works?

Whenever a query is fired on a table in MySQL, MySQL servers upper layer parses the query. Then it opens a table for which query is fired. After performing operations on data table is closed and results are returned, for every single query disk operation involved and MySQL Engine opens a new instance of table and closes it after performing operations.

Opening and closing of tables is very costly as it causes mutex contensions and hampers performance. The biggest difference here in Handlersocket is, it doesn’t open/close tables everytime. It reuses the open table connections.

Advantages

1. Supporting lots of query patterns
2. It can handle lots of concurrent connections
3. Extremely high performance
4. Smaller network packets
5. Running limited number of MySQL internal threads
6. Grouping client requests
7. It can reduce the number of fsync() calls and replication delay
8. No duplicate cache and data inconsistency
9. No need to modify/rebuild MySQL
10.Independent from storage engines

How to install HandlerSocket

1. Install latest version of percona Mysql
2. Enabling the Plugin, First, add the following lines to the [mysqld] section of your my.cnf file:

loose_handlersocket_port = 9998
# the port number to bind to for read requests
loose_handlersocket_port_wr = 9999
# the port number to bind to for write requests
loose_handlersocket_threads = 16
# the number of worker threads for read requests
loose_handlersocket_threads_wr = 1
# the number of worker threads for write requests
open_files_limit = 65535
# to allow handlersocket to accept many concurrent
# connections, make open_files_limit as large as
# possible.

3. Login to mysql as root, and execute the following query:
mysql> install plugin handlersocket soname ‘handlersocket.so’;

4. Testing the Plugin installation

If handlersocket.so was successfully installed, it will begin accepting connections on ports 9998 and 9999. Executing a SHOW PROCESSLIST command should show HandlerSocket worker threads:

for more details http://www.percona.com/doc/percona-server/5.5/performance/handlersocket.html

Thanks
Manoj

View Comments »

Posted in Mysql, Mysql Clusters

Tags: HandlerSocket How to scalable your application

How to examine and kill MySQL client processes

November 26th, 2010

A useful MySQL command, when it comes to keeping track of client-server transactions, is SHOW PROCESSLIST. It lists all the open connections to the server, together with a thread ID, information on the requesting host, user and database, the query string, and the current state of the connection. The command itself is simple to run from the MySQL client:

mysql> SHOW PROCESSLIST;

Note that since this command displays currently executing queries, it is only available to users with the SUPER privilege (such as the MySQL root user). A number of different states are possible for each connection —the MySQL manual lists them, together with explanations of each.

In addition to viewing processes, users with the SUPER privilege can also kill running threads—for example, threads which have “gone zombie” and are either not responding or tying up server resources—with the KILL command. To kill a thread completely, use the KILL command followed by the thread ID returned by SHOW PROCESSLIST:

mysql> KILL 27;

Query OK, 0 rows affected (0.05 sec)

To kill the query being executed by a thread but leave the connection active (yes, MySQL even allows such fine-grained control), use the KILL QUERY command instead, followed by the appropriate thread ID.

The best approach is to add mysql_close() at the end of every page.

You have also two options:
1) In ssh, mysql | kill <procid>, or
2) In my.cnf, wait_timeout = 600 (time is in seconds)

View Comments »

Posted in Mysql, Mysql Clusters

Tags: kill MySQL client processes kill sleep process/connections

Enable read only mode on a mysql-server

November 24th, 2010

Here are some useful options that can prevent writing to MySQL databases, while retaining the right reading.

The activation of these features may be particularly interesting in the case of a change of server. You can then export and import all data from one server to another, being sure to maintain data integrity. As for customers, they can only read data.

1. Method by changing a variable server

To do this, connect to the server with root user:

mysql-h localhost-u root-p

and run the command:

mysql> set GLOBAL read_only = true;

The data in all databases are accessible only for reading. Note that root guard on the other hand always have the right to write.

Example of inserting data with a simple user:

mysql> INSERT INTO foo VALUES ( ‘tata2′);
ERROR 1290 (HY000): The MySQL server is running with the-read-only
option so it can not execute this statement

To disable this mode, you can then run the following command:

mysql> set GLOBAL read_only = false;

2. Method of positioning locks

There is also a second method is to put locks via:

mysql> FLUSH TABLES WITH READ LOCK;

The latter will then close all open tables and locks all the reading tables and bases.

At this time, applications that wish to make an entry will be queued until the unlock command:

mysql> UNLOCK TABLES;

It will be possible to see a list of requests waiting in the listing process as follows:

mysql> SHOW processlist;
| 5 | root | localhost | test | Query | 160 | Waiting for release of readlock | INSERT INTO foo VALUES ( ‘tata2′) |

Conclusion

The first method seems more adapted when transferring data from one MySQL server to another, while the second plutût it will be used in case of a backup cold

View Comments »

Posted in Mysql, Mysql Clusters

Tags: Enable read only mode on a mysql-server How to Enable read only mode on a mysql-server

How to check Memory and Index Usage in MySQL CLuster

November 22nd, 2010

How to check Memory and Index Usage in MySQL CLuster,on the management Node connect to ndb_mgm client utility

run /usr/bin/ndb_mgm

output
– NDB Cluster — Management Client –

run show command
ndb_mgm> show

Connected to Management Server at: localhost:1186
Cluster Configuration
———————
[ndbd(NDB)]     2 node(s)
id=2    @xx.xxx.xx.xxx (mysql-5.1.47 ndb-7.1.8, Nodegroup: 0, Master)
id=3    @yy.yyy.yy.yyy (mysql-5.1.47 ndb-7.1.8, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1 @zz.zzz.zz.zzz (mysql-5.1.47 ndb-7.1.8)

[mysqld(API)] 1 node(s)
id=4 @bb.bb.bb.bb (mysql-5.1.47 ndb-7.1.8)

now this setup has 2 data nodes to check the Memory and Index Usage for node 2

ndb_mgm> 2 REPORT MemoryUsage;
Node 2: Data usage is 5%(3846 32K pages of total 65536)
Node 2: Index usage is 0%(229 8K pages of total 131104)

this will give you two things how much in percentage used and how much total memory we have allocated
Memory : 32K*65536 (32k data page size and 65536 no of pages
INdex : 8k*131104 (8K data page size and 131104 no of blocks)

View Comments »

Posted in Mysql Clusters

Tags: Memory and Index Usage MySQL Cluster

Upgrading to MariaDB from MySQL 5.0 (or older version)

November 19th, 2010

If you upgrade to MariaDB 5.1 from MySQL 5.1 you don’t have to do anything with your data or MySQL clients. Things should “just work”.

When upgrading between different major versions of MariaDB or MySQL you need to run the mysql_upgrade program to convert data that are incompatible between versions. This will also update your privilege tables in the mysql database to the latest format.

In almost all cases mysql_upgrade should be able to convert your tables, without you having to dump and restore your data.

After installing MariaDB, just do:

mysql_upgrade –verbose

If you want to run with a specific TCP/IP port do:

mysql_upgrade –host=127.0.0.1 –port=3308 –protocol=tcp

If you want to connect with a socket do:

mysql_upgrade –socket=127.0.0.1 –protocol=socket

To see other options, use –help.

“mysql_upgrade” reads the my.cnf sections [mysql_upgrade] and [client] for default values.

There are a variety of reasons tables need to be converted; they could be any of the following:

* The collation (sorting order) for an index column has changed
* A field type has changed storage format
o DECIMAL and VARCHAR changed format between MySQL 4.1 and MySQL 5.0
* An engine has a new storage format
o ARCHIVE changed storage format between 5.0 and 5.1
* The format for storing table names has changed
o In MySQL 5.1 table names are encoded so that the file names are identical on all computers. Old table names that contains forbidden file name characters will show up prefixed with #mysql50# in ‘SHOW TABLES’ until you convert them.

If you don’t convert the tables, one of the following things may happen:

* You will get warnings in the error log every time you access a table with an invalid (old) file name.
* When searching on key values you may not find all rows
* You will get an error “ERROR 1459 (HY000): Table upgrade required” when accessing the table.
* You may get crashes

“mysql_upgrade” works by calling mysqlcheck with different options and running the “mysql_fix_privileges” script. If you have trouble with “mysql_upgrade”, you can run these commands separately to get more information of what is going on.

Most of the things in the MySQL 5.1 manual section also applies to MariaDB.

The following differences exists between “mysql_upgrade” in MariaDB and MySQL (as of MariaDB 5.1.50):

* MariaDB will convert long table names properly.
* MariaDB will convert InnoDB tables (no need to do a dump/restore or ALTER TABLE).
* MariaDB will convert old archive tables to the new 5.1 format (note: new feature in testing).
* “mysql_upgrade –verbose” will run “mysqlcheck –verbose” so that you get more information of what is happening.

mysql_upgrade
Actions

* History
* Flag as Spam/Inappropriate

User
monty
Created
2 months, 2 weeks ago
Modified
2 months, 1 week ago
Type
article
Status
active

mysql_upgrade is a tool that checks and updates your tables to the latest version.
Usage:

mysql_upgrade [--force] [--user=# --password --host=hostname --port=# --socket=#
--protocol=tcp|socket|pipe|memory --verbose] OTHER_OPTIONS]

You should run mysql_upgrade when you upgrade from another major MySQL/MariaDB release, like from MySQL 5.0 to MariaDB 5.1.

The following groups are read from the my.cnf files: [mysql_upgrade] and [client].

The following options to handle option files may be given as the first argument:
–print-defaults    Print the program argument list and exit.
–no-defaults    Don’t read default options from any option file.
–defaults-file=#    Only read default options from the given file #.
–defaults-extra-file=#    Read this file after the global files are read.

Main arguments are:
-?, –help    Display this help message and exit.
-#, –debug[=name]    Output debug log.
–debug-check    Check memory and open file usage at exit.
-T, –debug-info    Print some debug info at exit.
-f, –force    Force execution of mysqlcheck even if mysql_upgrade has already been executed for the current version of MySQL.
-h, –host=name    Connect to host.
-p, –password[=name]    Password to use when connecting to server. If password is not given, it’s solicited on the tty.
-P, –port=name    Port number to use for connection or 0 for default to, in order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/services, built-in default (3306).
–protocol=name    The protocol to use for connection (tcp, socket, pipe, memory).
-s, –silent    Print less information.
-S, –socket=name    The socket file to use for connection.
-t, –tmpdir=name    Directory for temporary files.
-u, –user=name    User for login if not current user.
-v, –verbose    Display more output about the process.
–write-binlog    All commands including mysqlcheck are binlogged. Enabled by default ; Use –skip-write-binlog when commands should not be sent to replication slaves.

“mysql_upgrade” is mainly a framework to call mysqlcheck. mysql_upgrade works by doing the following operations:

# Find out path to datadir
echo “show show variables like ‘datadir’” | mysql
mysqlcheck –no-defaults –all-databases –fix-db-names –fix-table-names –write-binlog
mysqlcheck –check-upgrade –all-databases –auto-repair –write-binlog
mysql_fix_privilege_tables

The connect options given to mysql_upgrade are passed along to mysqlcheck and mysql.

The mysql_fix_privilege_tables script is not actually called; it’s included as part of mysql_upgrade.

If you have a problem with mysql_upgrade, try run it in very verbose mode:

mysql_upgrade –verbose –verbose other-options

The following differences exists between “mysql_upgrade” in MariaDB and MySQL (as of MariaDB 5.1.50):

* MariaDB will convert long table names properly.
* MariaDB will convert InnoDB tables (no need to do a dump/restore or ALTER TABLE).
* MariaDB will convert old archive tables to the new 5.1 format (note: new feature in testing).
* “mysql_upgrade –verbose” will run “mysqlcheck –verbose” so that you get more information of what is happening.
* More descriptive output.

View Comments »

Posted in Mysql, Mysql Clusters

Tags: Upgrading to MariaDB from MySQL 5.0

SQL node started, but can NOT be added into the cluster.

November 2nd, 2010

after doing the setup as mentioned on http://dev.mysql.com/tech-resources/articles/mysql-cluster-for-two-servers.html all nodes were started but on managenment console I was getting the nodes status as starting for long time on debugging figured out that all nodes were not able to talk to each other so removed the firewall on
the machines using

/etc/init.d/iptables save

/etc/init.d/iptables stop

after doing this cluster started to work.

View Comments »

Posted in Mysql Clusters

NDB node keep starting state for long time

November 2nd, 2010

after doing the setup as mentioned on http://dev.mysql.com/tech-resources/articles/mysql-cluster-for-two-servers.html all nodes were started but on managenment console I was getting the nodes status as starting for long time on debugging figured out that all nodes were not able to talk to each other so removed the firewall on the machines using