You are here

MySQL Questions & Answers

Content

Search with special characters

Question: How can I search the following string in a text field: '%newline,tabluator,b)%'?

Answer:

CREATE TABLE spec(txt VARCHAR(255));

INSERT INTO spec values ('bla\tbla\nbla');
INSERT INTO spec values ('\n\tb)');
INSERT INTO spec values ('abc\n\tb)xyz');

SELECT * FROM spec;

SELECT * FROM spec WHERE txt LIKE '\n\tb)';
SELECT * FROM spec WHERE txt LIKE '%\n\tb)%';
SELECT * FROM spec WHERE txt REGEXP '^\n\tb)$';
SELECT * FROM spec WHERE txt REGEXP '\n\tb)';

Why is InnoDB disabled?

Question: After reconfiguring the my.conf InnoDB was disabled. Why?

Answer: This can happen when the InnoDB logfile size (innodb_log_file_size) was set to an new value which is not compatible with the old value. To avoid this problem shut-down MySQL properly (mysqladmin --user=root shutdown). Then backup and after remove the logfiles, configure the my.cnf with the new logfile size. Start MySQL again and check the error.log.

How to find MySQL system information?

Question: How can I find MySQL stytem information?

Answer:

Operating System

Linux:
$ uname -a
$ cat /etc/SuSE-release
$ cat /proc/version

MySQL libraries

$ ldconfig -p | grep -i mysql

MySQL client

$ mysql --version

MySQL server

mysql> STATUS;
mysql> SELECT VERSION();
mysql> SHOW VARIABLES LIKE 'version%';

$ mysqladmin version -p

MySQL Table versions

SELECT table_schema, table_name, engine, version, row_format
  FROM information_schema.tables
 WHERE table_type = 'BASE TABLE'
 ORDER BY table_schema, table_name
;

What is the difference between MySQL certified server and community server?

Question: What is the difference between MySQL certified server and community server?

Answer:

MySQL certified server MySQL community server
  • Chosen software by MySQL
  • Based on internal quality/feature completeness
  • Serves as base for Certified Server
  • New MySQL Forge helps get contributions!
  • Profits from community testing
  • Receives additional internal/external testing
  • Receives community and basic internal testing
  • Contains no untested/certified feature
  • May contain features for community testing
  • Infrequent major releases
  • Frequent releases (early and often)
  • Under active development
  • Tiered patch releases (every month to once/qtr)
  • Contains patches plus new features
  • Recommended to partners, ISV's, Enterprise deployment
  • Not recommended to partners, ISV's
  • Certified on most popular platforms
  • Offered on over two dozen platforms
  • Formal support through MySQL AB
  • Informal support via forums, etc.

MySQL monitoring

Question: How can I monitor MySQL related system information, status and activities?

Answer: MySQL monitoring solutions

MySQL backup

Question: How to do backup?

Answer:

# for both
cp /etc/my.cnf $BACKUP_DIR/my_$BACKUP_TIMESTAMP.cnf

# for MyISAM
BACKUP_TIMESTAMP=`date '+%Y-%m-%d_%H-%M-%S'`
BACKUP_DIR='/mybackupdir'
mysqldump --user=root --all-databases --flush-privileges --lock-all-tables --master-data=1 \
--flush-logs --triggers --routines --events --hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

# for InnoDB
BACKUP_TIMESTAMP=`date '+%Y-%m-%d_%H-%M-%S'`
BACKUP_DIR='/mybackupdir'
mysqldump --user=root --all-databases --flush-privileges --single-transaction --master-data=1 \
--flush-logs --triggers --routines --events --hex-blob > $BACKUP_DIR/full_dump_$BACKUP_TIMESTAMP.sql

*--flush-privileges works since 5.1.12

*--flush-logs has a bug (#61854) in MySQL 5.5 which fixed in 5.5.21.

*--triggers seems to be enabled by default now. It's unclear to me since when.

*While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

Caution: If you have a mixed environment (MyISAM AND InnoDB) it becomes a little bit more complicated!

Question: Is LVM snapshot a feasible way to take MySQL/InnoDB backups?

Answer: It depends! If you stop MySQL it should work well. If you have MyISAM tables only, then FLUSH TABLES WITH READ LOCK should guarantee a consistent backup. With InnoDB a LVM snapshot should work as well because it is the same situation as in a sever crash. It becomes a problem when the InnoDB log files are located on a different disk than the data files. FLUSH TABLES WITH READ LOCK is in this situation not sufficient for InnoDB because InnoDB still may write some data in the background which can corrupt your LVM snapshot over 2 devices.

There were some cases reported where MySQL/InnoDB refused to recover from a LVM snapshot backup with core dump:

InnoDB: Progress in percents: 0 1 2 3 mysqld got signal 11;

Innobase never approved LVM snapshots as valid way of taking backups! If these are technical or marketing reasons I do not know.

If you choose LVM snapshot as a backup method we recommend to do a restore-test of the backup and restart the database to see if it recovers successfully. Then you are sure your backup is a valid one.

Literature

  1. [ 1 ] MySQL Backups using LVM Snapshots
  2. [ 2 ] MySQL Server Backup, Restoration, And Disaster Recovery Planning Presentation
  3. [ 3 ] Using LVM for MySQL Backup and Replication Setup
  4. [ 4 ] Logical volume management
  5. [ 5 ] Backup of MySQL Databases on Logical Volumes

Corrupt MyISAM table

Question: How does a corrupt MyISAM table look like?

Answer: InnoDB tables should not get corrupted at all. MyISAM tables can get courrupted after system failure. You should NEVER run corrupted MyISAM tables. I can even get worse! Do always a check after a crash. How you can find if a table got corrupted and how you can repare it again is shown below:

mysql> CHECK TABLE test;
+-----------+-------+----------+-------------------------------------------------------+
| Table     | Op    | Msg_type | Msg_text                                              |
+-----------+-------+----------+-------------------------------------------------------+
| test.test | check | warning  | 1 client is using or hasn't closed the table properly |
| test.test | check | error    | Key in wrong position at page 3072                    |
| test.test | check | error    | Corrupt                                               |
+-----------+-------+----------+-------------------------------------------------------+
3 rows in set (0.05 sec)

mysql> SELECT COUNT(*) FROM test;
ERROR 145 (HY000): Table './test/test' is marked as crashed and should be repaired

mysql> REPAIR TABLE test;
+-----------+--------+----------+--------------------------------------------------+
| Table     | Op     | Msg_type | Msg_text                                         |
+-----------+--------+----------+--------------------------------------------------+
| test.test | repair | warning  | Number of rows changed from 11000000 to 10000000 |
| test.test | repair | status   | OK                                               |
+-----------+--------+----------+--------------------------------------------------+
2 rows in set (29 min 17.80 sec)

061004  9:43:50 [ERROR] /usr/local/bin/mysqld: Table './test/test' is marked as crashed and
                should be repaired
061004 10:13:24 [Note] Found 10000000 of 11000000 rows when repairing './test/test'

How to compile MySQL

Question: How do I compile MySQL on platforms were no binaries are provided?

Answer: Recently we wanted to run MySQL on a 64bit PPC Linux platform. We compiled it as follows:

CC="gcc" CFLAGS="-O3 -mpowerpc -m64 -mcpu=powerpc" CXX="gcc" \
CXXFLAGS="-O3 -m64 -mpowerpc -mcpu=powerpc" \
./configure --prefix=/app/mysql/5.0.37

make

make install

If you never compiled something on your Linux machine before maybe some necessary tools are missing:

  • gmake
  • autoconf
  • automake
  • libtool
  • m4
  • bison

Further very often there are some header files of standard libraries not installed:

  • libreadline-dev
  • libncurses5-dev

If you install all those it should work...

MySQL Documentation: Installing MySQL from a Standard Source Distribution

Test restore procedure

Question: Why should I regularly test my restore procedure?

Answer: You should test your restore procedure on a regular base to make sure it actually works, when you really need it.

See the following real life examples MySQL users were experiencing: When I backup the database with the command:
shell> mysqldump --user=root -p --hex-blob --max_allowed_packet=128M -x -t test > test_dump.sql

everything works fine. But when I try to restore the database I get the following error:
mysql --user=root -p --max_allowed_packet=128M test < test_dump.sql
ERROR 1153 (08S01) at line 87: Got a packet bigger than 'max_allowed_packet' bytes

In this case you possibly would have found the problem already before you have to do the real emergency restore.

Reset a MySQL user password

Question: How do I reset a Password for a MySQL user?

Answer: For a regular MySQL or MariaDB user you can reset the Password with the SET PASSWORD command:

mysql> SET PASSWORD FOR 'app_owner'@'%.mysite.com' = PASSWORD('secret');

Consider, that the user in MySQL always consist of a username AND a domain name.

Literature

[1] SET PASSWORD Syntax

Reset the MySQL root user password

Question: How do I reset the MySQL root user password?

Answer: To reset the MySQL or MariaDB root user password you have 2 possibilities:

  1. Restart the mysqld with an init-file where you reset the root password.
  2. Restart the mysqld with the skip-grant-tables option and then reset the root password.

Possibility one: Restart the mysqld with the init-file parameter:

  1. Create a file with the reset commands in a location where nobody else than the MySQL user has access to:
    -- reset_root_user_password.sql
    UPDATE mysql.user SET password = PASSWORD('secret') WHERE user = 'root';
    FLUSH PRIVILEGES;
  2. Hook this reset command file into your my.cnf
    # my.cnf
    [mysqld]
    init-file = /home/mysql/secret/reset_root_user_password.sql
  3. Stop or kill mysqld
    shell> kill `cat <datadir>/<host_name>.pid`
  4. Verify that mysqld was stopped properly:
    shell> pgrep mysqld
  5. Start mysqld
    shell> /etc/init.d/mysql start
    Now you should be capable to use the new root user password.
  6. Remove the init-file parameter again from the my.cnf and delete the reset_root_user_password.sql script.

This methode requires only one database restart.

Possibility two: Restart the mysqld with the skip-grant-tables parameter:

  1. Add the skip-grant-tables parameter to your my.cnf:
    # my.cnf
    [mysqld]
    skip-grant-tables = 1
  2. Stop or kill mysqld
    shell> kill `cat <datadir>/host_name.pid`
  3. Verify that mysqld was stopped properly:
    shell> pgrep mysqld
  4. Start mysqld
    shell> /etc/init.d/mysql start
  5. Now you can login without any password:
    shell> mysql --user=root
  6. Reset the root user password:
    mysql> UPDATE mysql.user SET password = PASSWORD('secret') WHERE user = 'root';
    mysql> FLUSH PRIVILEGES;
  7. Remove the skip-grant-tables parameter from the my.cnf.
  8. Restart the mysqld again to protect its security.
    shell> /etc/init.d/mysql restart

This methtode requires for security reasons two database restarts.

You can use both methods with mysqld command options as well.

Literature

[1] How to Reset the Root Password [2] MySQL Server Command Option init-file [3] MySQL Server Comand Option skip-grant-tables

How to enable the InnoDB plug-in

Question: How do I enable the InnoDB plug-in?

Answer: You can enable the InnoDB plug-in in 3 different ways:

  • In the MySQL Client with the INSTALL PLUGIN command.
  • When starting the mysqld with command line parameters.
  • In the MySQL configuration file:
# my.cnf
[mysqld]
ignore_builtin_innodb
plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;\
innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;\
innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

Pleased make sure that the plugin-load parameter is a one-liner.

You get the following possibilities:
DistributionVersionTypeMakerVersion
MySQL5.0built-in onlyInnobase?
MariaDB5.1built-in onlyPercona1.0
MySQL5.1built-in and plug-inInnobase? / 1.0
MariaDB5.2built-in onlyPercona1.0
MySQL5.5built-in onlyInnobase1.1

Storage Engines shipped with MariaDB / MySQL

Question: What storage engines do I get with MySQL and MariaDB?

Answer: Depending on the release and the distribution you are using you get different Storage Engines. The details you can find in the following matrix:

mysql> SHOW ENGINES;

MySQL

Engine 5.0 5.1 5.5
ARCHIVE YES YES YES
BerkeleyDBNO
BLACKHOLE YES YES YES
CSV YES YES YES
EXAMPLE NO NO
FEDERATED YES NO NO
InnoDB YES YES DEFAULT
ISAM NO
MEMORY YES YES YES
MRG_MYISAMYES YES YES
MyISAM DEFAULT DEFAULTYES
ndbclusterDISABLEDNO

Remarks:
  • From MySQL 5.0 to 5.1 BerkleyDB (BDB) and ISAM Storage Engines were removed and Federated Storage Engine was disabled.
  • In 5.5 it looks like the EXAMPLE and the FEDEREATED Storage Engines are not included any more.
  • With 5.5 InnoDB is the new default Storage Engine.
  • The NDB Storage Engine is not support with MySQL 5.5 yet.

MariaDB

Engine 5.1 5.2 5.3
ARCHIVE YES YES YES
Aria YES YES
BLACKHOLE YES YES YES
CSV YES YES YES
EXAMPLE YES YES YES
FEDERATED YES YES YES
InnoDB YES YES
MARIA YES
MEMORY YES YES YES
MRG_MYISAMYES YES YES
MyISAM DEFAULTDEFAULTDEFAULT
OQGRAPH YES
PBXT YES YES YES
SPHINX YES YES

Remarks:
  • In MariaDB 5.1 the FEDERATED Storage Engine is integraded in the form of the FederatedX Storage Engine.
  • InnoDB was not taken into MariaDB 5.1. PBXT acts as a substitue for InnoDB.
  • In MariaDB 5.2 the Maria Storage Engine was renamed into Aria and InnoDB comes back in form of XtraDB.
  • The new OQGraph Storage Engine was added with MariaDB 5.2 as well.
  • OQGraph did not build with MariaDB 5.3.0 any more but it is still included in the source code. So I assume this is a bug of this early alpha release.
For more information about the different Storage Engines available see also MySQL Pluggable Storage Engines

Compiling MySQL Cluster ndb-test fails

Question: When I want to compile flexAsynch I get some odd compiling errors.

Answer: We had a similar problem, when we added one include directive the problem disappeared.

When we compiled MySQL Cluster 7.1.9a as follows:

./configure --with-plugins=max --with-ndb-test ; make -j 4

we got the following error message:

g++ -DHAVE_CONFIG_H -DNDEBUG   -I. -I../../../../include -I../../../../storage/ndb/test/include -I. \
-I../../../../include -I../../../../storage/ndb/include -I../../../../include -I../../../../mysys \
-I../../../../storage/ndb/include -I../../../../storage/ndb/include/util \
-I../../../../storage/ndb/include/portlib -I../../../../storage/ndb/include/logger \
-I../../../../storage/ndb/include/mgmapi -I. -I../../../../include \
-I../../../../storage/ndb/include -I../../../../include -I../../../../storage/ndb/include \
-I../../../../storage/ndb/include/ndbapi -I../../../../storage/ndb/include/util \
-I../../../../storage/ndb/include/portlib -I../../../../storage/ndb/test/include \
-I../../../../storage/ndb/include/mgmapi -I../../../../storage/ndb/include/kernel \
-I../../../../storage/ndb/src/ndbapi -I../../../../storage/ndb/include/debugger \
-I../../../../ndb/src/mgmapi -I../../../../ndb/src/mgmsrv -I../../../../ndb/include/mgmcommon \
-DDEFAULT_PREFIX="\"/usr/local\"" -O3 -fno-implicit-templates -fno-exceptions -fno-rtti -MT atrt-db.o \
-MD -MP -MF .deps/atrt-db.Tpo -c -o atrt-db.o `test -f 'db.cpp' || echo './'`db.cpp
main.cpp: In function ‘bool parse_args(int, char**)’:
main.cpp:560: error: ‘lstat’ was not declared in this scope
main.cpp:741: error: ‘lstat’ was not declared in this scope
main.cpp:749: error: ‘S_ISREG’ was not declared in this scope

560   if (argc > 1 && lstat(argv[argc-1], &sbuf) == 0)
741     if (lstat(tmp.c_str(), &sbuf) != 0)
749     if (!S_ISREG(sbuf.st_mode))

This only happens with the --with-ndb-test directive but NOT without! So we assume, that there must be something wrong in the sources, which does not show up on the developers machines...
By just adding the following line to all failing parts (there were 2 or 3 of them):

#include <sys/stat.h>

it worked out for us.

NDB information schema does not show up

Question: When I start my MySQL 7.1 Cluster the NDB information schema does not show up. What can I do?

Answer: This happens in some cases. The reason is not known to us. Seems something like a bug.
When you look at the log MySQL seems to be aware of the NDB information schema:

110125  9:00:24 [Note] NDB: Creating mysql.ndb_schema
110125  9:00:24 [Note] NDB: Flushing mysql.ndb_schema
110125  9:00:24 [Note] NDB Binlog: CREATE TABLE Event: REPL$mysql/ndb_schema
110125  9:00:24 [Note] NDB Binlog: logging ./mysql/ndb_schema (UPDATED,USE_WRITE)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+

As mentioned in Bug #54552 ndbinfo missing although activated after online upgrade a mysql_upgrade will help to solve the problem:

shell> mysql_upgrade

mysql> SHOW PLUGINS;
+------------+----------+----------------+---------+---------+
| Name       | Status   | Type           | Library | License |
+------------+----------+----------------+---------+---------+
| ndbcluster | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
| ndbinfo    | ACTIVE   | STORAGE ENGINE | NULL    | GPL     |
+------------+----------+----------------+---------+---------+

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| ndbinfo            |
| test               |
+--------------------+

Hyper Threading (HT) enabled?

Question: How can I find if Hyper Threading (HT) is enable on my machine?

Answer: /proc/cpuinfo will tell you if Hyper Threading (HT) is enabled on your machine or not:

# cat /proc/cpuinfo | egrep 'siblings|cpu cores' | sort | uniq
cpu cores       : 2
siblings        : 4

If the values of cpu cores and siblings are equal, then hyper threading is DISABLED otherwise ENABLED.
On some Linux distributions (RedHat, CentOS) you can even set Hyper Threading online: Is hyper-threading enabled on a Linux system?

How to make a patch for MariaDB?

Question: I have found a bug for MariaDB and have fixed it. How do I make a patch for it?

Answer: I was told by the MariaDB developers that they would like to have it like this:

diff -up orig.cc new.cc

Where does the InnoDB AUTO-INC waiting come from?

Question: With SHOW INNODB STATUS we have seen under high load a lot of transactions waiting on AUTO_INC. Where does it come from?

Answer: This is a bug in MySQL. It looks as follows:

------- TRX HAS BEEN WAITING 10 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test/test` trx id 0 447488123 lock mode AUTO-INC waiting

This bug will be fixed in newer MySQL 5.1 releases (>= 5.1.47) but not in MySQL 5.0.

For more details refer to:

  1. InnoDB auto-inc scalability fixed
  2. Bug #16979: AUTO_INC lock in InnoDB works a table level lock

My character encoding seems to be wrong. How can I fix it?

Question: It looks like my data are somehow wrong in my MySQL database. I get some strange characters. How can I fix those?

Answer: This happens when you use the wrong encoding to fill in your data in the database. How this happens and how to fix it again you can find in the following lines:

mysql> CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql>\! locale

# Wrong encoding!!!
mysql> SET NAMES latin1;

mysql> INSERT INTO test VALUES (NULL, 'äöü', NULL);

# Data seems to be correct but are not:
mysql> SELECT data, HEX(data) FROM test;
+--------+--------------+
| data   | HEX(data)    |
+--------+--------------+
| äöü    | C3A4C3B6C3BC |
+--------+--------------+

# Set right enconding:
mysql> SET NAMES utf8;

# Wrong umlaut encoding for latin1 column:
mysql> SELECT data, HEX(data) FROM test;
+--------------+--------------+
| data         | hex(data)    |
+--------------+--------------+
| äöü       | C3A4C3B6C3BC |
+--------------+--------------+

# Fix encoding
mysql> ALTER TABLE test MODIFY data VARBINARY(64);
mysql> ALTER TABLE test MODIFY data VARCHAR(64) CHARACTER SET utf8;

mysql> SHOW CREATE TABLE test\G
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

# Now the data are displayed correctly and encoding matches the CHARACTERSET defintion of the column
mysql> SELECT data, HEX(data) FROM test;
+--------+--------------+
| data   | hex(data)    |
+--------+--------------+
| äöü    | C3A4C3B6C3BC |
+--------+--------------+

# Convert encoding now to latin1
mysql> ALTER TABLE test MODIFY data VARCHAR(64) CHARACTER SET latin1;

# Now the data are displayed correctly and encoding matches the CHARACTERSET defintion of the column
mysql> SELECT data, HEX(data) FROM test;
+--------+-----------+
| data   | hex(data) |
+--------+-----------+
| äöü    | E4F6FC    |
+--------+-----------+

mysql> SHOW CREATE TABLE test\G
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=PBXT DEFAULT CHARSET=latin1

If the table was created with utf8 character set the procedure is as follows:

ALTER TABLE test MODIFY data VARCHAR(64) CHARACTER SET latin1;
ALTER TABLE test MODIFY data VARBINARY(64);
ALTER TABLE test MODIFY data VARCHAR(64);

See also Convert latin1 to UTF-8 in MySQL

Solution: We have written a small application which repairs this issues. If you need our help please get in contact with us.

I think my Slave is not consistent to its Master anymore. How can I check this?

Question: I think my Slave is not consistent to its Master anymore. How can I check this?

Answer: The best way to do it is using the Maatkit-Tools.
In the Maatkit-toolbox there is mk-table-checksum and mk-table-sync. How to use it you can find as follows:

Check

On Master:

mk-table-checksum --create-replicate-table --empty-replicate-table --replicate=test.checksum \
  u=root,h=127.0.0.1 \
  --tables=test.test

On Slave:

mk-table-checksum --replicate=test.checksum \
  --replicate-check=1 \
  u=root,h=127.0.0.1 \
  --tables=test.test

Sync

On Slave:

mk-table-sync --sync-to-master  --print \
  h=127.0.0.1,u=root,D=test,t=test

Then run the queries on the master...

My MySQL Server is swapping from time to time. This gives hick-ups in MySQL. How can I avoid this?

Question: My MySQL Server is swapping from time to time. This gives hick-ups in MySQL. How can I avoid this?

Answer: First of all you have to make sure, that MySQL does not over-allocate memory:

# free
             total       used       free     shared    buffers     cached
Mem:      16431968    5736652   10695316          0     127876    2449008
-/+ buffers/cache:    3159768   13272200
Swap:     19802108          0   19802108

# ps aux | grep -e 'mysqld ' -e 'VSZ' | cut -b-120
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
mysql     2020  1.8  6.1 3166896 1010840 ?     Sl   08:06  11:09 /home/mysql/product/mysql-5.6.2/bin/mysqld

If you checked this and it looks OK you can check the swappiness of your system:

# cat /proc/sys/vm/swappiness
60

# sysctl -a
# sysctl vm.swappiness

If the value is bigger than 0 you can set it either on the fly or make it permanent:

# sysctl -n vm.swappiness=0

or

#
# /etc/sysctl.conf
#
vm.swappiness=0

# sysctl -p /etc/sysctl.conf

Which I/O scheduler?

Question: How can I find which I/O scheduler my device is using?

Answer: The default I/O scheduler (cfq) may not be optimal for database workloads. If you are I/O limited, try an other I/O scheduler. To find which I/O scheduler currently is used the following command might help:

cat /sys/block/<device>/queue/scheduler

noop and deadline often give better results than cfq or anticipatory for InnoDB workloads.

Mail problems

Question: How can I find why my mail is not sent?

Answer: To find this the best is to simulate this manually step by step. The following sequence of commands might help:

telnet localhost smtp
EHLO www.fromdual.com
MAIL FROM: contact@fromdual.com
RCPT TO: oli.sennhauser@fromdual.com
DATA
Subject:Title

bla bla bla
.
QUIT

Here you should see step by step why it is not working.

Comments

Hi everybody Please rectify my doubts. In my database i have three fields as id,medicinename,quantity. In medicine name i have many different names.Many names comes twice or thrice.Now i need to fetch the data from database for particular medicine name only(this medicine name comes nearly 10 times)say for example if the medicinename is "A" it comes in id 1,4,9,12,25.Now i want to fetch the data from id 1 only.Whent the quantity comes to "0" in id "1",i want to fetch the datas from next id.Also when the quantity comes to "0" the row shold be deleted automatically.Please help me to do this.
vishnurajcomment

Hi vishnuraj Simple examples are always good to explain or to try to understand. So I created the following table: In my database I have 3 fields as id,medicinename,quantity.
In medicinename I have many different names. Many names comes twice or thrice.
+------+----------------------+---------+
| id   | medicinename         | quantiy |
+------+----------------------+---------+
|    1 | Trifolium arvense L. |      25 |
|    2 | Agave americana      |     120 |
|    3 | Hypericum perforatum |      12 |
|    4 | Trifolium arvense L. |      35 |
|    5 | Agave americana      |     120 |
|    6 | Trifolium arvense L. |     120 |
+------+----------------------+---------+
Now I need to fetch the data from database for particular medicine name only (this medicine name comes nearly 10 times) say for example if the medicinename is "A" it comes in id 1,4,9,12,25.
SELECT * FROM medicine WHERE medicinename = 'Trifolium arvense L.';
+------+----------------------+---------+
| id   | medicinename         | quantiy |
+------+----------------------+---------+
|    1 | Trifolium arvense L. |      25 |
|    4 | Trifolium arvense L. |      35 |
|    6 | Trifolium arvense L. |     120 |
+------+----------------------+---------+
So far so good. Up to here I could follow you but then I loose you: Now I want to fetch the data from id 1 only. When the quantity comes to "0" in id "1", I want to fetch the data from next id. Also when the quantity comes to "0" the row should be deleted automatically. I think there is some missing business logic information in your explanation. What is meant with "when the quantity comes to 0"? Further "I want to fetch the data from next id" and "the row should deleted automatically" sounds like you mix data with business logic. Could you please explain more in detail, what you want to achieve? Regards, Oli
olicomment