Mysql Nested Query Tweak

Do not use mysql nested queries for performance. Here I will share a little database table and two queries and their response times:
#Experiment is done by Mustafa TURAN(http://vpslife.blogspot.com/)#
#Experiment purpose to see how a nested query affects MYSQL Performance #
#Experiment Hardware:#
#--RAM: 1.5GB#
#--CPU: AMD Athlon(tm) 64 X2 Dual Core Processor 4000#
#Experiment Software:#
#--MYSQL 5(MYISAM)#
#--Centos5.2#
#Notes: 1k=1000#
Experiment tables
Table #1: links

database table : links(l_id, l_source, l_status, l_kb)

total rows: 220k

Table #2: filelinks

database table : filelinks(f_id, l_id)

total rows: 1500k

Nested query

Query: SELECT l_id, l_source, l_status, l_kb FROM links WHERE links.l_id IN (SELECT l_id FROM filelinks WHERE filelinks.f_id = 1);

Response: Showing rows 0 - 0 (1 total, Query took 2.9740 sec)

Inner Join(same query with inner join method)

Query: SELECT distinct(l_id), l_source, l_status, l_kb FROM links INNER JOIN (SELECT l_id as xL_id FROM filelinks WHERE filelinks.f_id = 1) xLinks ON links.l_id =
xLinks.xL_id;

Response: Showing rows 0 - 0 (1 total, Query took 0.0003 sec)

This example make you to see how dangerous using nested queries. I hope this experiment will save your CPU usage by MYSQL!

My detailed /etc/my.cnf file:

#Mustafa Turan (http://vpslife.blogspot.com/)#

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
port = 3306
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 2M
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache_size = 128
query_cache_size= 64M
thread_concurrency = 4
thread_stack = 1M
max_connections = 250
skip-bdb
skip-innodb

[mysqldump]
quick
max_allowed_packet = 32M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 16M
write_buffer = 16M

[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Copy Remote Database to Local Database

Remote mysqldump

Run this one line command as a user:

mysqldump --opt --compress --user=REMOTE_DB_USERNAME --password=REMOTE_DB_PASS --host=REMOTE_DB_HOSTNAME REMOTE_DB_NAME mysql --user=LOCAL_DB_USERNAME --password=LOCAL_DB_PASSWORD --host=LOCAL_DB_HOSTNAME -D LOCAL_DB_NAME -C LOCAL_DB_NAME

Notes: You have to enable remote mysql on remote computer! (To do this you have to add ip address of local computer to remote computer's db remote access ip list)

How to reset root password on MySql?

Pass the root mode on terminal.

1. To stop mysql:

   # /etc/init.d/mysql stop

2. Access without privalages to mysql
   

   # mysqld_safe --skip-grant-tables &

3. Pass to the mysql
   

   # mysql -u root

   
   

   # use mysql;

   
   

   # mysql -u root

   
   

   # mysql>
   INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv, Index_priv, Alter_priv, Show_db_priv, Super_priv, Create_tmp_table_priv, Lock_tables_priv, Execute_priv, Repl_slave_priv, Repl_client_priv, Create_view_priv, Show_view_priv, Create_routine_priv, Alter_routine_priv, Create_user_priv, ssl_type, ssl_cipher, x509_issuer, x509_subject, max_questions, max_updates, max_connections, max_user_connections) VALUES
   ('localhost', 'root', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0);
   

4. To start mysql:

   # /etc/init.d/mysql start

Now, new mysql password is blank! You can user myadmin password command to specify another password for mysql.

How to fix mysql thread_stack overrun?

Today I faced a problem about running my stored procedure code and I got the error message :

#1436: Thread stack overrun: 4136 bytes used of a 131072 byte stack, and 131072 bytes needed. Use 'mysqld -O thread_stack=#' to specify a bigger stack.

I tried the command mysqld -O thread_stack=# but it did not worked. Then I entered my.cnf file which is at /etc/my.cnf I found the value of thread_stack which is not 4136 bytes, its value is 128K(131072 byte).
I thought that the value was true but I changed it to 512K. Here is my configuration:

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 128
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 512K

Now, it is working well with 40000 row datas.

Low Memory Mysql / Apache configurations

Configurations will be good for 64Mb - 256Mb RAM

If you would like to convert your InnoDB tables to MyISAM, you can use the shell script to automatically convert InnoDB tables to MyISAM.

#!/bin/bash

MYSQLCMD=mysql

for db in `echo show databases | $MYSQLCMD | grep -v Database`; do
     for table in `echo show tables | $MYSQLCMD $db | grep -v Tables_in_`; do
             TABLE_TYPE=`echo show create table $table | $MYSQLCMD $db | sed -e's/.*ENGINE=\([[:alnum:]\]\+\)[[:space:]].*/\1/'|grep -v 'Create Table'`
             if [ $TABLE_TYPE = "InnoDB" ] ; then
                     mysqldump $db $table > $db.$table.sql
                     echo "ALTER TABLE $table ENGINE = MyISAM" | $MYSQLCMD $db
             fi
     done
done

MySQL 4

Place the below configuration into /etc/my.cnf and restart your mysql server to begin using the new configuration.

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 128K

# For low memory, Berkeley DB should not be used so keep skip-bdb uncommented unless required
skip-bdb

# For low memory, InnoDB should not be used so keep skip-innodb uncommented unless required
skip-innodb

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 16M
#innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 5M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50


[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

Apache

Make your sure httpd.conf (/etc/httpd/conf/httpd.conf) is not configured to start too many servers, or have to many spare server sitting around. Reference the example below

StartServers 1
MinSpareServers 1
MaxSpareServers 5
ServerLimit 50
MaxClients 50
MaxRequestsPerChild 5000

Also, make sure to adjust KeepAliveTimeout (to say, 2 or 3).

The default configuration file for apache also frequently loads every module it can. This is an especially big deal with the prefork mpm, as each apache instance will eat up geometrically more memory when unneeded modules are enabled. Comment out any modules that aren't needed to save yourself some more memory.

Apache 2.2.X Note: The configuration files are likely under /usr/local/apache2/conf. Additionally, uncomment the following line in httpd.conf

# Include conf/extra/httpd-mpm.conf

Then you can edit this file with the above tip. One last tip is to comment out the features you don't currently use (e.g. webdav).

Also, make sure Apache is setup to use the right multi-Processing Module for your setup.

On Ubuntu mpm-prefork package to install:

apt-get install apache2-mpm-prefork

This is essential to stay under the memory limit. This reduced current apache footprint from around 225MB (one thread) to about 12MB per thread.

Source: http://wiki.vpslink.com/Low_memory_MySQL_/_Apache_configurations

Mysql Cheat Sheet

Selecting a database:

# mysql USE database;

Listing databases:

# mysql SHOW DATABASES;

Listing tables in a db:

# mysql SHOW TABLES;

Describing the format of a table:

# mysql DESCRIBE table;

Creating a database:

# mysql CREATE DATABASE db_name;

Creating a table:

# mysql CREATE TABLE table_name (field1_name TYPE(SIZE), field2_name TYPE(SIZE));

# mysql CREATE TABLE pet (name VARCHAR(20), sex CHAR(1), birth DATE);

Load tab-delimited data into a table:

# mysql LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table_name;

(Use \n for NULL)

Inserting one row at a time:

# mysql INSERT INTO table_name VALUES ('MyName', 'MyOwner', '2002-08-31');

(Use NULL for NULL)

Retrieving information (general):

# mysql SELECT from_columns FROM table WHERE conditions;

All values: SELECT * FROM table;
Some values: SELECT * FROM table WHERE rec_name = "value";
Multiple critera: SELECT * FROM TABLE WHERE rec1 = "value1" AND rec2 = "value2";

Reloading a new data set into existing table:

# mysql SET AUTOCOMMIT=1;

# used for quick recreation of table

# mysql DELETE FROM pet;

# mysql LOAD DATA LOCAL INFILE "infile.txt" INTO TABLE table;

Fixing all records with a certain value:

# mysql UPDATE table SET column_name = "new_value" WHERE record_name = "value";

Selecting specific columns:

# mysql SELECT column_name FROM table;

Retrieving unique output records:

# mysql SELECT DISTINCT column_name FROM table;

Sorting:

# mysql SELECT col1, col2 FROM table ORDER BY col2;

Backwards: SELECT col1, col2 FROM table ORDER BY col2 DESC;

Date calculations:

# mysql SELECT CURRENT_DATE, (YEAR(CURRENT_DATE)-YEAR(date_col)) AS time_diff [FROM table];

MONTH(some_date) extracts the month value and DAYOFMONTH() extracts day.

Pattern Matching:

# mysql SELECT * FROM table WHERE rec LIKE "blah%";

(% is wildcard - arbitrary # of chars)
Find 5-char values: SELECT * FROM table WHERE rec like "_____";
(_ is any single character)

Extended Regular Exblockquotession Matching:

# mysql SELECT * FROM table WHERE rec RLIKE "^b$";

(. for char, [...] for char class, * for 0 or more instances
^ for beginning, {n} for repeat n times, and $ for end)
(RLIKE or REGEXP)
To force case-sensitivity, use "REGEXP BINARY"

Counting Rows:

# mysql SELECT COUNT(*) FROM table;

Grouping with Counting:

# mysql SELECT owner, COUNT(*) FROM table GROUP BY owner;

(GROUP BY groups together all records for each 'owner')

Selecting from multiple tables:

(Example)

# mysql SELECT pet.name, comment FROM pet, event WHERE pet.name = event.name;

(You can join a table to itself to compare by using 'AS')

Currently selected database:

# mysql SELECT DATABASE();

Maximum value:

# mysql SELECT MAX(col_name) AS label FROM table;

Auto-incrementing rows:

# mysql CREATE TABLE table (number INT NOT NULL AUTO_INCREMENT, name CHAR(10) NOT NULL);

# mysql INSERT INTO table (name) VALUES ("tom"),("dick"),("harry");

Adding a column to an already-created table:

# mysql ALTER TABLE tbl ADD COLUMN [column_create syntax] AFTER col_name;

Removing a column:

# mysql ALTER TABLE tbl DROP COLUMN col;

(Full ALTER TABLE syntax available at mysql.com.)

Batch mode (feeding in a script):

# mysql -u user -p <> mysql source batch_file;

Backing up a database with mysqldump:

# mysqldump --opt -u username -p database > database_backup.sql

(Use '# mysqldump --opt --all-databases > all_backup.sql' to backup everything.)
(More info at mysql's docs.)