MYSQL/MARIADB Tips

1,MySQL ignore the primary key conflict, avoid the repeated insertion of several ways.

2, set the MySQL connection timeout parameter can refer to: One, two or three.

3 . Check the current number of MySQL connections.

1), View status:

SHOW STATUS;

2), view the current number of connections:

SHOW STATUS WHERE variable_name= ' threads_connected ';

3), see which connections the current user has:

SHOW processlist;

4), view all user connections. (Root access required)

SHOW full processlist;

4 . Modify the maximum number of MySQL connections.

1), view:

/* View all Global variables */show global variables;/* View the maximum number of connections */show global VARIABLES WHERE variable_name= ' max_connections ';

2), Temporary modification:

SET GLOBAL max_connections=3600;

3), write the configuration file, under Windows MariaDB for example, in C:\Program files\mariadb 10.0\data\my.ini

[MYSQLD] Paragraph Add or modify max_connections=3600 .

5 . View the number of rows in a large table.

For tens of millions of large tables, directly with

SELECT COUNT (*) from TableName;

Even if a primary key or unique index is built, it is still slow. Can be viewed in the following two ways.

(1), see rows below results.

SHOW TABLE STATUS from Mydbnamewhere name= ' mytablename ';

(2), see the following results of the Table_rows column.

SELECT * from INFORMATION_SCHEMA. Tableswhere table_schema= ' mydbname ' and table_name= ' mytablename ';

The above two methods are accurate for the MyISAM storage engine, and for the InnoDB storage engine, the result is an approximate value, which may be a far worse. Walker's test of InnoDB is that the actual number of data bars is 31182219, and the above method results in a value of 38569811. Refer to the document show TABLE STATUS syntax for the description of rows.

Related reading: Python+mysql

Walker * * *

This article is from "Walker's Journal" blog, please be sure to keep this source http://walkerqt.blog.51cto.com/1310630/1705236

MYSQL/MARIADB Tips