Very nice 5 questions quiz about SQL performance
Often the server admin has little control over the applications which uses MySQL and it is hard to find the bottlenecks. This blog post can’t bring the peace in the world, or help NASA to finally land on the Mars. Instead those tasks, I’ll try to solve something else and present my own experiences with MySQL storage engines (at least for MyISAM and InnoDB as the most popular).
Continue reading MySQL Performance – Howto – part 1 (high performance tuning scripts)
If you need to update one table with information from another table, you can use next query.
UPDATE sourceTable s, destTable d SET d.something = s.something WHERE d.fieldId = s.id
One of the biggest mistakes you can make with MySQL is to leave its default configuration. It will work, but not as it should.
For example, default MySQL installation (5.0x) will keep all InnoDB data in one file – ibdata1. This file is usually located in /var/lib/mysql (at least on RH and Debian based distros) and after few months this file can became very big (in my case it was 20GB). This file has a initial size of 10Mb and it automatically extends but it can’t be reduced with DELETE, TRUNCATE or DROP. The file could reach the maximum size allowed by the filesystem if no limit is set in the my.cnf file (Debian/Ubuntu -/etc/mysql/my.cnf or RH based distros /etc/my.cnf). The best idea is to force MySQL to create an ibd file for each InnoDB table (add innodb_file_per_table in my.cnf and restart mysql). Unf. this won’t affect old tables which are already created and which are in use.
You can “FIX” this on several ways but no matter what option do you choose, BACKUP YOUR WHOLE MYSQL DATA DIRECTORY and stop services connected to your MySQL server (httpd, radius, postfix, dovecot, etc).
Keep in mind that converting InnoDB table to MyISAM will kill foreign keys so do not do it unless you know how to recreate your foreign keys.
The best option is to dump all your databases in one sql file.
# /usr/bin/mysqldump ––extended-INSERT ––all-DATABASES ––add-drop-DATABASE ––disable-KEYS ––flush-privileges ––quick ––routines ––triggers > backup.sql
Stop mysql server with
# service mysqld stop
# /etc/init.d/mysqld stop
Backup complete mysql data dir (/var/lib/mysql).
# cd /var/lib/ # mv mysql mysql_backup # mkdir mysql # chown mysql:mysql mysql
Then you should have something like
drwxr-xr-x 5 mysql mysql 4096 2010-12-30 13:38 mysql
Add innodb_file_per_table option in /etc/my.cnf file and save file
Re-initialize the database with the following commands
# su mysql $ mysql_install_db $ exit
Start mysql server with service mysqld start and get into mysql console with
# mysql -u root
Then exec next commands
SET FOREIGN_KEY_CHECKS=0; SOURCE backup.sql; SET FOREIGN_KEY_CHECKS=1;
Restart mysql server with service mysqld restart
That should be all.
In case that something goes wrong you still have mysql_backup dir which contains all you databases and files. Simple rename the new mysql dir to mysql_new and mysql_backup to mysql. Then restart mysql.
Keep in mind that this operation will kill all services who depends on mysql. So, be quick 🙂
Also, good idea is to execute command mysql_secure_installation which will “tight” your MySQL server.
If you’re using RH based distros, you’ll probably notice that their habbit is to keep the same software versions in one release. For example, if you need PHP on CentOS 5.x, # yum install php will install PHP 5.1.6 (Latest PHP version available on http://php.net is 5.3.x). If you need MySQL, you can count on MySQL 5.0.xx branch and any other wishes will force you to use independent repos (like http://www.jasonlitka.com/yum-repository/). Another option is to use “do-it-yourself” method.
In this post I’ll write about manual upgrade steps from rpm archives. Please keep in mind that this procedure works for me and please do not send me the private messages. I can’t help you on that way. Only options is to post your comments here and I will try to solve your problem(s).
First thing you need to do is to see what mysql packages do you have installed
# rpm -qa | grep -i ^mysql
You will get something like
mysql-connector-odbc-3.51.12-2.2 mysql-5.0.77-4.el5_5.3 MySQL-python-1.2.1-1 mysql-server-5.0.77-4.el5_5.3 mysql-devel-5.0.77-4.el5_5.3 mysql-bench-5.0.77-4.el5_5.3
Then backup all your databases, save them on the safe location, protect with alarms, guards, poison dogs, cobras, ninjas, etc…
Download rpms from http://www.mysql.com/downloads/mysql/ (MySQL-client-community-5.1.50-1.rhel5.i386.rpm, MySQL-devel-community-5.1.50-1.rhel5.i386.rpm, MySQL-server-community-5.1.50-1.rhel5.i386.rpm, MySQL-shared-community-5.1.50-1.rhel5.i386.rpm)
Note: if you have 32-bit OS, download i386 rpms, if you have 64bit download x86_64 rpms)
Stop mysql server with
# service mysqld stop
Then remove mysql rpms but with –nodeps option (in case you use yum remove mysql, you will need to reinstall a lot of apps because they will be deleted too)
# rpm -e --nodeps mysql
Repeat the same with other mysql packages (devel, bench, client,…). Then you need to install downloaded rpms with rpm -i mysql… and do it.
It is possible to receive the errors like
ls: /var/lib/mysql/*.err: No such file or directory ls: /var/lib/mysql/*.err: No such file or directory ERROR: 1136 Column count doesn't match value count at row 1 100910 10:24:00 [ERROR] Aborting 100910 10:24:00 [Note] /usr/sbin/mysqld: Shutdown complete Installation of system tables failed! Examine the logs in /var/lib/mysql for more information.
This will be fixed later…
Now you need to add .my.cnf file to your root dir so you can execute mysql_upgrade command (note that . before m means that file is hidden). Add next lines inside this file (and don’t forget to replace rootpass with your real mysql root pass)
[client] user=root password=rootpass
(chmod .my.cnf to 600 for security reasons)
Then exec next command
# service mysql start
This will produce the similar output
Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck with default connection arguments Running 'mysqlcheck with default connection arguments blabla.table1 OK blabla.table2 OK blabla.table3 OK mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log Error : You can't use locks with log tables. status : OK mysql.help_category error : Table upgrade required. Please do "REPAIR TABLE `help_category`" or dump/reload to fix it! mysql.help_keyword error : Table upgrade required. Please do "REPAIR TABLE `help_keyword`" or dump/reload to fix it! mysql.help_relation OK database2.cache error : Table upgrade required. Please do "REPAIR TABLE `cache`" or dump/reload to fix it! database2.contacts OK database2.identities OK database2.messages error : Table upgrade required. Please do "REPAIR TABLE `messages`" or dump/reload to fix it! database2.session error : Table upgrade required. Please do "REPAIR TABLE `session`" or dump/reload to fix it! database2.users error : Table upgrade required. Please do "REPAIR TABLE `users`" or dump/reload to fix it! Repairing tables mysql.help_category OK mysql.help_keyword OK mysql.help_topic OK mysql.proc OK mysql.time_zone_name OK database2.cache note : The storage engine for the table doesn't support repair database2.messages note : The storage engine for the table doesn't support repair database2.session note : The storage engine for the table doesn't support repair database2.users note : The storage engine for the table doesn't support repair Running 'mysql_fix_privilege_tables'... OK
As you can see, the database database2 can’t be repaired and you should drop all tables inside this database and import your backup. After this, you can check is everything ok with mysql_upgrade –force
Once again, restart mysql with service mysql restart and check logs. Test is everything ok, try ti create a new database, optimize your installation, eat something…
I had a lot of problems with this upgrade. I had to upgrade PHP to 5.3.x, I had to recompile postfix with MySQL support, I had to download and recompile Dovecot because Dovecot from CentOS repos is compiled with mysql 5.0 branch. Some versions of RoundCube doesn’t work with php 5.3.x so you should download latest, etc etc… It can be done but please be careful with this. Who knows which nuclear reactor will explode after this 😛
MySQL Workbench is a cross-platform, visual database design tool developed by MySQL. It is the highly anticipated successor application of the DBDesigner4 project. MySQL Workbench will be available as a native GUI tool on Window, Linux and OS X.
MySQL Workbench 5.2 RC provides:
1. Data Modeling
2. Query (upgrade from MySQL Query Browser)
3. Admin (upgrade from MySQL Administrator)
Please get your copy from this page
By default, MySQL Server will be installed with root superuser without any password. You can connect to MySQL server as root without requiring password or by keying in blank password. However, if you have set the password for root and forget or unable to recall the password, then you will need to reset the root password for MySQL.
Step 1 is to stop MySQL service with
# /etc/init.d/mysql stop
# service mysqld stop
Step 2 is to start MySQL w/o root password
# mysqld_safe --skip-grant-tables &
Step 3 is to connect with mysql client
# mysql -u root
Step 4 is to finally change old root password with
mysql> use mysql; mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root'; mysql> flush privileges; mysql> quit
Step 5 is to stop mysql server and restart it without –skip-grant-tables parameter
# /etc/init.d/mysql stop
Step 6 is to start mysql server and test it with
# /etc/init.d/mysql start # mysql -u root -p
After you install your new server, it is very good idea to improve the security of your MySQL installation with
With mysql_secure_installation wizard you can set a password for root account, you can remove root accounts that are accessible from outside the local host, you can remove anonymous-user accounts and you can remove the test database, which by default can be accessed by anonymous users. The script will prompt you to determine which actions to perform.
If you want to test server performance, you can think about SysBench. SysBench is a modular, cross-platform and multi-threaded benchmark tool for evaluating OS parameters that are important for a system running a database under intensive load. The idea of this benchmark suite is to quickly get an impression about system performance without setting up complex database benchmarks or even without installing a database at all.
Current features allow to test the following system parameters:
* file I/O performance
* scheduler performance
* memory allocation and transfer speed
* POSIX threads implementation performance
* database server performance (OLTP benchmark)
(Primarily written for MySQL server benchmarking, SysBench will be further extended to support multiple database backends, distributed benchmarks and third-party plug-in modules)
I couldn’t find CentOS RPM so here are few tips how to install it manually.
Download Sysbench (current version is 0.4.12)
# wget http://garr.dl.sourceforge.net/sourceforge/sysbench/sysbench-0.4.12.tar.gz
Then unpack it and install with
# tar -xvzf sysbench-0.4.12.tar.gz # cd sysbench-0.4.12 # libtoolize --force --copy # ./autogen.sh # ./configure # make # make install
To test CPU performance you can try
# sysbench --test=cpu --cpu-max-prime=20000 run
For MySQL test, you’ll need to prepare database for testing with
# sysbench --test=oltp --mysql-table-engine=innodb --oltp-table-size=500000 --mysql-user=test_database --mysql-password=test_database_password --mysql-socket=/var/lib/mysql/mysql.sock prepare
(replace test_database with valid username and test_database_password with valid password)
This command will create sample table inside test_database and it will have 500 000 rows (InnoDB engine).
sysbench 0.4.12: multi-threaded system evaluation benchmark No DB drivers specified, using mysql Creating table 'test-database'... Creating 500000 records in table 'test-database'...
Now to start read test
# sysbench --num-threads=16 --max-requests=100000 --test=oltp --oltp-table-size=500000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-read-only --mysql-user=test_database --mysql-password=test_database_password run
For read-write test you can try
# sysbench --num-threads=16 --max-requests=10000 --test=oltp --oltp-table-size=500000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-test-mode=complex --mysql-user=test_database --mysql-password=test_database_password run
More info about specific parameters can be found in official docs (http://sysbench.sourceforge.net/docs/)
In this post, I will present few basic commands for MySQL administration. You’ll see how to create a database, create user, assign a database for specific user, etc. First I suppose that you have root password set and that you know it… (in next posts I will present few steps to set this password or to recover it…)
Log in to mysql console:
[root@hydra mysql]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 248 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql>
Here you can see server version (in this case 5.0.45). With next command you can see all databases
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | birds | | mysql | | private | | bdd1 | | test | +--------------------+ 6 rows in set (0.05 sec) mysql>
If you want to create a new database, enter next command (remember that all SQL commands must be terminated with ; )
mysql> create database db1; Query OK, 1 row affected (0.00 sec)
Now we need to assign a user to newly created database. Also, we need to grant him all privileges for this database but restrict access and operations on another databases (except test database)
mysql> CREATE USER 'db1'@'localhost' IDENTIFIED BY 'db1pass'; Query OK, 0 rows affected (0.02 sec) mysql> GRANT ALL PRIVILEGES ON db1.* TO 'db1'@'localhost' IDENTIFIED BY 'db1pass'; Query OK, 0 rows affected (0.01 sec) mysql> quit; Bye
To add readonly account you can set next privileges
GRANT SELECT ON db1.* TO 'db1'@'%';
If you replace “localhost” with “%”, the user will be able to connect from remote host.
After this you can repeat step from the beginning and test new user account. In next posts I will present phpMyadmin