MySQL Engine – InnoDB vs MyISAM

InnoDB does locking on the row level and runs queries as nonlocking consistent reads by default

How to check the engine of tables for a database

mysql> select engine from tables.information_schema where table_schema='<DB_NAME>';

 

Summary of Engines’ space utilised

select engine,sum(index_length+data_length)/1024/1024,count(engine) from information_schema.tables group by engine;

 

Check MyISAM tables

NOTE:

When it converts from MyISAM to InnoDB, you will see in process list ‘copy to tmp table’. This has nothing to do with tmp_table_size or tmpdir. What it does, it goes inside your /var/lib/mysql/<dbname>/ and creates temporary files called like #sqlXXXX.frm and #sqlXXXX.ibd. Keep an eye on the size of the .ibd file to monitoring the progression. It should be roughly big like the sum of <table>.MYI and <table>.MYD. +~20%

 

SELECT table_schema,table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql") and engine = 'MyISAM';

Change all to InnoDB
https://support.rackspace.com/how-to/mysql-engines-myisam-vs-innodb/
When?
https://docs.dev4press.com/tutorial/wordpress/choosing-mysql-database-engine-myisam-or-innodb/
Quick answer: Always EXCEPT If you want to use FULLTEXT or SPATIAL indexes for some tables: in this case there is no choice and you must use MyISAM.

NOTE: FULLTEXT index support for InnoDB tables requires MySQL 5.6.4 or higherMariaDB 10.0.5 or higher
Highly recommend upgrading to MariaDB 10.0 as this is the 5.6 MySQL equivalent. It does not alter the data structures as 5.6 does and can be downgraded much more easily. It contains all the features of 5.6 plus more as well as being a drop-in replacement for MySQL in RHEL7.
I would point you to these KB articles:
https://mariadb.com/kb/en/mariadb-vs-mysql-features/https://mariadb.com/kb/en/mariadb/mariadb-vs-mysql-compatibility/Repositories: https://downloads.mariadb.org/mariadb/repositories/#mirror=somerset

Check if there are FULLTEXT or SPATIAL indexes:

mysql> use information_schema;
mysql> SELECT DISTINCT CONCAT(STATISTICS.TABLE_SCHEMA, '.', STATISTICS.TABLE_NAME) AS fulltext_table, (TABLES.DATA_LENGTH + TABLES.INDEX_LENGTH)/1024/1024 AS size_M FROM STATISTICS INNER JOIN TABLES ON TABLES.TABLE_SCHEMA = STATISTICS.TABLE_SCHEMA AND TABLES.TABLE_NAME = STATISTICS.TABLE_NAME WHERE STATISTICS.INDEX_TYPE IN ("SPATIAL", "FULLTEXT") GROUP BY STATISTICS.TABLE_SCHEMA, STATISTICS.TABLE_NAME;

Convert ALL MyISAM tables to InnoDB (all databases):

2 steps + backup

# mysql --batch --skip-column-names --execute 'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," ENGINE='MyISAM';") from information_schema.TABLES where ENGINE = "MyISAM" AND TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql");' > rollback_all2Innodb.sql
# mysql --batch --skip-column-names --execute 'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," ENGINE='InnoDB';") from information_schema.TABLES where ENGINE = "MyISAM" AND TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql");' > all2Innodb.sql
# mysql < all2Innodb.sql

(one liner – CAREFUL!)

# mysql --batch --skip-column-names --execute 'select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," ENGINE='InnoDB';") from information_schema.TABLES where ENGINE = "MyISAM" AND TABLE_SCHEMA NOT IN ("information_schema", "performance_schema", "mysql");' | mysql

 

Convert MyISAM tables of a SPECIFIC database to InnoDB:

Backup and convert-file

# DB="<dbname>"
# mysql --batch --skip-column-names --execute "select concat('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' ENGINE = \'MyISAM\';') from information_schema.TABLES where TABLE_SCHEMA = '"$DB"' and ENGINE = 'MyISAM';" > rollback_$DB_all2Innodb.sql

# mysql --batch --skip-column-names --execute "select concat('alter table ',TABLE_SCHEMA,'.',TABLE_NAME,' ENGINE = \'InnoDB\';') from information_schema.TABLES where TABLE_SCHEMA = '"$DB"' and ENGINE = 'MyISAM';" > $DB_all2Innodb.sql

NOTE: When it converts from MyISAM to InnoDB, you will see in process list ‘copy to tmp table’. This has nothing to do with tmp_table_size or tmpdir. What it does, it goes inside your /var/lib/mysql/<dbname>/ and creates temporary files called like #sqlXXXX.frm and #sqlXXXX.ibd. Keep an eye on the size of the .ibd file to monitoring the progression. It should be roughly big like the sum of <table>.MYI and <table>.MYD. +~20%

MySQL temporary table to RAM

Best practise

Check ID/GID for MySQL

# id mysql
uid=27(mysql) gid=27(mysql) groups=27(mysql)

Create and mount

# mkdir -p /var/lib/mysqltempinram
# chown 27:27 /var/lib/mysqltempinram
# echo "tmpfs /var/lib/mysqltempinram tmpfs rw,gid=27,uid=27,size=512M,nr_inodes=10k,mode=0700 0 0" >> <a href="/etc/fstab">/etc/fstab</a>
# mount /var/lib/mysqltempinram
# df -Th /var/lib/mysqltempinram

Add this line under [mysqld] into my.cnf configuration file:

tmpdir = /var/lib/mysqltempinram

restart mysql

Check if all is applied:

# mysql
mysql> SHOW VARIABLES LIKE 'tmpdir';

Test

mysql> CREATE TEMPORARY TABLE IF NOT EXISTS mysql.table2 AS (SELECT * FROM mysql.user);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

 

Quick method – careful! This might make the server run out of memory!!!

Check what’s the RAM filesystem on the server

# mount | grep tmpfs
tmpfs on /dev/shm type tmpfs (rw,noexec,nosuid,nodev)

=> /dev/shm

Add this to my.cnf file:

[mysqld]
tmpdir = /dev/shm/

restart mysql

Check if all is applied:

# mysql
mysql> SHOW VARIABLES LIKE 'tmpdir';
+---------------+-------------+
| Variable_name | Value |
+---------------+-------------+
| tmpdir | /dev/shm/ |
+---------------+-------------+

 

Test:

mysql> CREATE TEMPORARY TABLE IF NOT EXISTS mysql.table2 AS (SELECT * FROM mysql.user);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

 

MySQL notes

MySQL backup – mysqldump
shell> mysqldump [options] db_name [tbl_name …] > db_name.sql
shell> mysqldump [options] –databases db_name … > multi_db.sql
shell> mysqldump [options] –all-databases > all_dbs.sql

Importing MySQL Table
To import the table run the following command from the command line:
shell> mysql -D dbname < tableName.sql

Check database space
SELECT table_schema “Data Base Name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB” FROM information_schema.TABLES GROUP BY table_schema ;

MySQL Uptime
a) mysql> SHOW GLOBAL STATUS;
b) # mysqladmin version | grep -i uptime

innodb_open_files
mysql> show global variables like “innodb_open_files”\G

Binary Logs
>> Enable:
> /etc/my.cnf
log-bin = /var/lib/mysql/bin-log

Enable the slow query log
slow-query-log = 1

Log queries that take longer than 2 seconds
long-query-time = 2

Set ‘max_connections’:
>> On the fly (GLOBAL variable. we can increase it on the fly without restarting mysqld service)
[Check] select @@global.max_connections;
[Set] set @@global.max_connections=300;
[Re-Check] select @@global.max_connections;
(or mysql> set global max_connections=250;)

>> CHANGE on /etc/my.cnf
max_connections = 50
max-connections = 50

set @@global.max_connections=default;

Set the query_cache_size to 16MB, query_cache_type to 1 and query_cache_limit to 1MB

mysql> set global query_cache_size=16*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql> set global query_cache_type=1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global query_cache_limit=1*1024*1024;
Query OK, 0 rows affected (0.00 sec)

Check variables
select @@global.max_connections;OR
show variables;show variables like ‘%max%’;

Disable InnoDB
[mysqld]
skip-innodb
default-storage-engine = myisam

Check if Query Cache is enabled:
SHOW VARIABLES LIKE ‘have_query_cache’;

Check Query Cache statistics:
show status like ‘Qcache%’;

MySQL’s maximum memory usage is dangerously high
>> (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + thread_stack + join_buffer_size) x max_connections
=> change max_connections

wait_timeout (global variable)
mysql> show processlist;
If there are too many queries, it might be a bug in the code (for example no “close connections”). In this case, it would be safer to setup a wait_timeout low, maybe 180 (seconds -> 3mins) to make sure the sleeping connections will get dropped at that time.

 

 

Apache not www to www redirects

not www to www

RewriteCond %{HTTP_HOST} !^www\.
RewriteRule (.*) http://www.%{HTTP_HOST}%{REQUEST_URI} [L,R=301]

Excluding ‘mysubdomain’

RewriteCond %{HTTP_HOST} !^(www|mysubdomain)\.
RewriteRule (.*) https://www.%{HTTP_HOST}%{REQUEST_URI} [L,R=301]

 

LVM – Add space

Add extra disk in a VG and expand existing LV

# df -Th /
Filesystem           Type   Size  Used Avail Use% Mounted on
/dev/mapper/vglocal00-root00
                     ext4    26G   23G  1.5G  95% /

# cfdisk /dev/sdc 
Disk has been changed.

# pvcreate /dev/sdc1 
  Physical volume "/dev/sdc1" successfully created

# pvs
  PV         VG        Fmt  Attr PSize  PFree  
  /dev/sda2  vglocal00 lvm2 a--u 29.75g 224.00m
  /dev/sdb1  vglocal01 lvm2 a--u  9.97g  64.00m
  /dev/sdc1            lvm2 ---- 10.00g  10.00g

# vgextend vglocal00 /dev/sdc1
  Volume group "vglocal00" successfully extended

# pvdisplay  /dev/sdc1 | grep Free
  Free PE               319

# lvextend --extents +319 -n /dev/vglocal00/root00 
  Size of logical volume vglocal00/root00 changed from 25.53 GiB (817 extents) to 35.50 GiB (1136 extents).
  Logical volume root00 successfully resized.

# resize2fs /dev/vglocal00/root00
resize2fs 1.41.12 (17-May-2010)
Filesystem at /dev/vglocal00/root00 is mounted on /; on-line resizing required
old desc_blocks = 2, new_desc_blocks = 3
Performing an on-line resize of /dev/vglocal00/root00 to 9306112 (4k) blocks.
The filesystem on /dev/vglocal00/root00 is now 9306112 blocks long.

# df -Th /
Filesystem           Type   Size  Used Avail Use% Mounted on
/dev/mapper/vglocal00-root00
                     ext4    35G   23G   11G  68% /

 

For a most updated article, check this one: http://blog.tian.it/lvm-how-to/

LVM – quick win 

pvscan
fdisk -l | grep Disk | egrep -v "mapper|identifier"

# Create a new primary partition - Linux Type LVM (8E)
cfdisk /dev/sdb

fdisk -l | grep LVM

pvcreate /dev/sdb1 && vgcreate vglocal01 /dev/sdb1 && lvcreate -n data1 -l 100%VG vglocal01

mkdir -p /mnt/data1
mkfs.ext4 /dev/mapper/vglocal01-data1 && mount /dev/mapper/vglocal01-data1 /mnt/data1


tail -1 /etc/mtab

tail -1 /etc/mtab >> /etc/fstab

 

LVM for dummies

You have your disk /dev/sdc

You need to cfdisk/fdisk it to set the flag “Linux LVM”, (flag 8E in cfdisk).

After that, you need to make this partition/device a physical volume (pvcreate /dev/sdc1) to make this device “usable” in a Virtual Group (VG).

The VG si basically a huge disk that can be partitioned in Logical Volumes (LVs).

Once is done, you need to extend the VG to include this new device (pv) => vgextend vglocal00 /dev/sdc1

Now the space is available to the VG vglocal00 and can be used to create/extend Logical Volumes (LV), which are some sort of “partitions” of the VG.

The LV is your “new device to format”.

DISK --> 8E flag --> PV ---> VG ---> LV1
			      |_____ LV2
			      |_____ LV3

 

Apache MaxClients and ServerLimit on Centos 7 and Ubuntu 14.04

In Apache 2.4 (which is in Centos 7 and Ubuntu 14.04 default) the mpm_worker MaxClients has been replaced with MaxRequestWorkers.

In Ubuntu 14.04 you can see the below in /etc/apache2/mods-enabled/mpm_prefork.conf

<IfModule mpm_prefork_module>
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxRequestWorkers 150
MaxConnectionsPerChild 500
ServerLimit 300
</IfModule>

In Centos 7 however there is NO configuration for this, which means it’s at the default value of 256 unless set by the user. This also means that StartServers is set by default to 3, MinSpareServers is set to 5, and MaxSpareServers is 10.
To configure Centos and set some limits, you need to edit this file /etc/httpd/conf.modules.d/00-mpm.conf appending the above content.

Always verify with apachectl -t or  httpd -t if all is ok before reloading/restarting Apache.

NOTE1: ServerLimit is not in the default configurations for either Centos 7 or Ubuntu 14.04 which means that if you set MaxRequestWorkers above 256, you must remember to add ServerLimit!

NOTE2: MaxConnectionsPerChild set to 0 on Ubuntu 14.04, and 0 is also the default in Centos 7. This means that on both the Apache processes will not expire. This is going to be bad for users who like to set their php memory_limit to 1G!

Find files based on date/time 

# ONE LINERS


> Modified in the last 12 hours (720 min)
find . -cmin -720 

> Modified in the last day
find . -mtime -1



# => ctime - for hacked/modified files 
# look for ctime instead, hacked scripts can't set that to what they want as opposed to mtime:

find -cmin -$n_minutes_ago
find -ctime -$n_days_ago
ls -lc   ## sorted by name
ls -ltc   ## sorted by time


>> File OLDER THAN xx days:
find . -type f -ctime +$n_days_ago

>> Find files RESTORED older that xx days and MOVE them
find . -type f -mtime +$n_days_ago | xargs -I '{}' mv {} /destination/path/