How to run a MySQL benchmarking using sysbench

How to run a MySQL benchmarking using sysbench

Installing sysbench

#yum install sysbench

The next step is to use the prepare statement with sysbench to generate a table in the specified database which will be used when performing tests.

root@# sysbench –test=oltp –oltp-table-size=250000 –mysql-db=database name –mysql-user=db user –mysql-password=db password –mysql-socket=/tmp/mysql.sock –db-driver=mysql –oltp-auto-inc=off prepare

sysbench 0.4.12: multi-threaded system evaluation benchmark

Creating table ‘sbtest’…
Creating 250000 records in table ‘sbtest’…

The above command has created a table called sbtest with 250000 rows of data which will be used for testing. The below commands show the the created table.

root@# sysbench –test=oltp –oltp-table-size=10000 –oltp-test-mode=complex –oltp-read-only=on –num-threads=1000 –max-time=300 –max-requests=5000 –mysql-db=database name –mysql-user=db user –mysql-password=db password –mysql-socket=/tmp/mysql.sock –db-driver=mysql run

sysbench 0.4.12: multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 1000

Doing OLTP test.
Running mixed OLTP test
Doing read-only test
Using Special distribution (12 iterations, 1 pct of values are returned in 75 pct cases)
Using “BEGIN” for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 5000
Threads started!
Done.

OLTP test statistics:
queries performed:
read: 70056
write: 0
other: 10008
total: 80064
transactions: 5004 (1128.50 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 70056 (15799.05 per sec.)
other operations: 10008 (2257.01 per sec.)

Test execution summary:
total time: 4.4342s
total number of events: 5004
total time taken by event execution: 4294.8422
per-request statistics:
min: 34.05ms
avg: 858.28ms
max: 2907.31ms
approx. 95 percentile: 2281.78ms

Threads fairness:
events (avg/stddev): 5.0040/0.73
execution time (avg/stddev): 4.2948/0.07
=-=-=-=

Finally, you can drop the database that have created while doing test.

How to Increase max_connections in MySQL without restarting MySQL service.

Posted on 2+05:30 by Renjith Raju

How to Increase max_connections in MySQL without restarting MySQL service.

If mysql connection reaches to it maximum (max) limit then you can see errors like “too many connections”. You can increase the connection limit without restart mysql.

As we know my.cnf is default configuration file for mysqld service and by default it is located in /etc directory unless and until you have changed it.

To find out how many max_connections are allowed currently on your mysql server use following command from mysql prompt.

mysql> select @@max_connections;
+——————-+
| @@max_connections |
+——————-+
| 500 |
+——————-+

max_connections is a GLOBAL variable. we can increase it on the fly without restarting mysqld service.

Use following command to increase max connection.

mysql> set global max_connections = 600;
Query OK, 0 rows affected (0.02 sec)

If you check again you will see that limit of max_connections is increased.

mysql> select @@max_connections;
+——————-+
| @@max_connections |
+——————-+
| 600 |
+——————-+

Note: It is important that you edit your /etc/my.cnf to add max_connections = 600 otherwise when you restart mysqld service in future, It will complain again after it hits the old max_connections limit.

How to migrate MySQL to MariaDB + cPanel

Posted on 0+05:30 by Renjith Raju

How to migrate MySQL to MariaDB + cPanel

With Oracle’s fairly recent acquisition of MySQL, a lot of people are looking to move away from MySQL in fear of Oracle changing the licensing, which could force you to change database back ends. MariaDB was initially forked in January of 2009. We can make this transition quickly, but not without some downtime, as we can’t have both databases working on the same files simultaneously.

If you are running the same major version of MySQL as MariaDB. Currently this means you must be running MySQL 5.5 and intend on moving to MariaDB 5.5.

Next, you need to take all the databases backup. These commands dump every SQL database you have to a single file. Make sure you do this on a partition big enough to hold your data.

# mysqldump –all-databases –routines –triggers > /home/alldata-`date +%F`.sql

First and foremost, shut down the MySQL service.

# service stop mysql

We are now done with MySQL. Use your package manager to remove it. Do not worry about associated libraries, as MariaDB is a drop in replacement. It should remain compatible at the API layer.

# /scripts/update_local_rpm_versions –edit target_settings.MySQL50 uninstalled
# /scripts/update_local_rpm_versions –edit target_settings.MySQL51 uninstalled
# /scripts/update_local_rpm_versions –edit target_settings.MySQL55 uninstalled
# /scripts/update_local_rpm_versions –edit target_settings.MySQL56 uninstalled

Remove the existing MySQL RPMs from your server. This will leave a clean slate for the MariaDB installation.

# /scripts/check_cpanel_rpms –fix –targets=MySQL50,MySQL51,MySQL55,MySQL56

Next, add the MariaDB repositories, and install it.

vi /etc/yum.repos.d/MariaDB.repo
———

# MariaDB 5.5 CentOS repository list – created 2013-06-23 21:13 UTC
# http://mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

——–

Edit /etc/yum.conf file and remove php* and mysql* from the exclude line. The exclude line in your /etc/yum.conf file may appear similar to the following example:

exclude=bind-chroot courier* dovecot* exim* filesystem httpd* mod_ssl* mydns* mysql* nsd* php* proftpd* pure-ftpd* ruby* spamassassin* squirrelmail*

Installing the new MariaDB packages.

# yum install MariaDB-server MariaDB-client MariaDB-devel php-mysql

# /etc/init.d/mysql start
# mysql_upgrade
# /etc/init.d/mysql restart

Rebuild EasyApache’s PHP to ensure that all PHP modules remain intact

# /scripts/easyapache –build

If you need to Switching back to MySQL

First you need to removing mariaDB package.

# yum remove MariaDB*

Install the MySQL RPM targets

# /scripts/update_local_rpm_versions –edit target_settings.MySQL50 installed
# /scripts/update_local_rpm_versions –edit target_settings.MySQL51 installed
# /scripts/update_local_rpm_versions –edit target_settings.MySQL55 installed
# /scripts/update_local_rpm_versions –edit target_settings.MySQL56 installed

Installing MySQL package

# /scripts/check_cpanel_rpms –fix –targets=MySQL50,MySQL51,MySQL55,MySQL56

Restoring the MySQL databases

# mysql < /home/db_dump/alldb.sql backup in (/home/alldb-`date +%F`.sql)

# /etc/init.d/mysql stop
# mv /var/lib/mysql /var/lib/mysql_mariadb-`date +%F`
# cp -p -r /var/lib/mysql_mysql_date /var/lib/mysql
# /etc/init.d/mysql start
# mysql_upgrade

After that check the MySQL logs also revert the my.cnf file.

How to repair a table in MYSQL

Posted on 2+05:30 by Renjith Raju

How to repair a table in MYSQL

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql>

1045 Cannot log in to the MySQL server

Posted on 6+05:30 by Renjith Raju

1045 Cannot log in to the MySQL server

Today I have faced an error while accessing phpMyAdmin from cPanel.

Logs:-
PHP Fatal error: Call to undefined function PMA_generate_common_url() in /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/plugins/auth/AuthenticationCpanel.class.php on line 611
PHP Fatal error: Call to undefined function PMA_generate_common_hidden_inputs() in /usr/local/cpanel/base/3rdparty/phpMyAdmin/libraries/plugins/auth/AuthenticationCpanel.class.php on line 213

Fix : reset the cPanel password from WHM (click the option Allow MySQL password change)

If the issue is still not resolved check the “skip-name-resolve” parameter in my.cnf file. You need to comment it and restart the mysql service.

[root~]# grep skip-name-resolve /etc/my.cnf
#skip-name-resolve

Issue is fixed 🙂

ERROR 1018 (HY000): Can’t read dir of ‘./dbname/’ (errno: 13)

Posted on 0+05:30 by Renjith Raju

When I access the database. I got the following error

ERROR 1018 (HY000): Can’t read dir of ‘./dbname/’ (errno: 13)
140301 12:35:35 [ERROR] /usr/sbin/mysqld: Can’t find file:

mysql> use dbname;
mysql> show tables;
ERROR 1018 (HY000): Can’t read dir of ‘./dbname/’ (errno: 13)

Solution :-

Check the directory permission for /var/lib/mysql/dbname

Use the command as follows:

cd /var/lib/mysql/
ls -l /var/lib/mysql/dbname
chown mysql:mysql dbname/ -R

Replace dbname with actual database directory name. Now you can connect to your mysql server:

mysql -u user -p dbname -e ‘show tables;’

Warning: mysql_connect() [function.mysql-connect] Host is blocked

Posted on 3+05:30 by Renjith Raju

Warning: mysql_connect() [function.mysql-connect]: Host ‘ is blocked because of many connection errors; unblock with ‘mysqladmin flush-hosts

Solution :-

Check the MySQL connection and increase the max connection limit in my.cnf file.

mysql>

Restart the MySQL service, then check the logs.
==============>

MySQL DB size showing zero Mb in cPanel

Posted on 2+05:30 by Renjith Raju

MySQL DB size showing zero Mb in cPanel

Sometimes we can see the mysql database size as zero in cPanel

Solution:-

vi /var/cpanel/cpanel.config
search for
disk_usage_include_sqldbs=0
and then make change to

disk_usage_include_sqldbs=1
If the parameter is not present, add it. Save the file and execute the following command:

/scripts/update_db_cache

This may take few minutes to get fixed if you have a large number of users with databases, but once done, you should be able see the database disk usage show up accurately in cPanel.

MySQL database not showing in cPanel

Posted on 4+05:30 by Renjith Raju

MySQL database not showing in cPanel

If database is not showing in cpanel, make sure database exists in the server. If it exists, make sure permission/ownership of database is correct.

Then check whether database details are listed in /var/cpanel/databases/username.yaml

If it is not listed in it, run following command to fix it.

/usr/local/cpanel/bin/setupdbmap

Check cpanel and database should be listed in it.

If database size is zero while listing, run the following command to fix it.

/scripts/update_db_cache

If it’s not works

/scripts/upcp –force

/scripts/checkperlmodules –force

/usr/local/cpanel/bin/setupdbmap

Table ‘./mysql/user’ is marked as crashed and should be repaired

Posted on 6+05:30 by Renjith Raju

How to make mysql service online from crash!

mysqld started

[ERROR] /usr/sbin/mysqld: Table ‘./mysql/user’ is marked as crashed and should be repaired
[ERROR] Fatal error: Can’t open and lock privilege tables: Table ‘./mysql/user’ is marked as crashed and should be repaired mysqld ended

Which means DB: /var/lib/mysql/mysql table: “user” has been crashed.

To resolve this issue, please perform the commands below:

]# service mysql start –skip-grant-tables

now with mysql started, you can repair the mysql/user table

]# mysqlcheck -r mysql user

After that,

]# service mysql stop

]# service mysql start

mysql> use mysql;

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

mysql>

#service mysql start

Linux System Administration

Category Archives: MySQL

How to run a MySQL benchmarking using sysbench

How to Increase max_connections in MySQL without restarting MySQL service.

How to migrate MySQL to MariaDB + cPanel

How to repair a table in MYSQL

1045 Cannot log in to the MySQL server

ERROR 1018 (HY000): Can’t read dir of ‘./dbname/’ (errno: 13)

Warning: mysql_connect() [function.mysql-connect] Host is blocked

MySQL DB size showing zero Mb in cPanel

MySQL database not showing in cPanel

Table ‘./mysql/user’ is marked as crashed and should be repaired

Rate this:

Share this:

Rate this:

Share this:

Rate this:

Share this:

Rate this:

Share this:

Rate this:

Share this:

Rate this:

Share this:

Rate this:

Share this:

Rate this:

Share this:

Rate this:

Share this:

Rate this:

Share this: