Script para optimizar tablas InnoDB en MySQL

Cuando utilizamos MySQL es común optimizar tablas con muchos registros con cierta periodicidad, esto para solventar problemas de fragmentación, entre otros. La verdad esta es una de las cosas del modelo de PostgreSQL que echo en falta, quizás no es tan «amigable» pero todo queda claro desde el inicio.

En PostgreSQL hay un proceso de aspiradora (vacuum) que va eliminando periódicamente registros inutilizados en tablas, su configuración, pan nuestro de cada día para un admin de BBDD que debe ajustarlo con frecuencia.

Bueno…. Volviendo a MySQL, si necesita optimizar tablas InnoDB, lo mejor que puede utilizar son ALTER nulos, estas son instrucciones DDL de tipo ALTER sin parámetros que permiten seguir trabajando con las BBDD, porque realiza copias temporales en disco. La cuestión es que esta herramienta «reconstruye» la tabla y elimina, entre otros, los problemas de fragmentación.

Aquí les dejo un script para optimizar de «un sólo golpe» varias tablas InnoDB:

#!/bin/bash
 
if [ $# -lt 2 ]; then
        echo "You must specify database host"
        echo "Eg. script.sh MY_DATABSE 192.168.10.1"
        exit
fi
 
db="$1"
host="$2"
user="root"
declare -a tables=(Table1 Table2 Table3)
 
stty -echo
read -p "Enter MySQL's Admin password: " password
stty echo
 
for table in ${tables[@]}; do
        echo $table &&
        time mysql -u $user --password=$password -h $host $db -e "ALTER TABLE $table ENGINE=INNODB"
done

Básicamente optimizamos las tablas especificadas (en un arreglo) e imprimimos el tiempo que toma cada instrucción (time).

Si tiene la certeza de que todas las tablas de una BD son InnoDB y quiere optimizarlas todas aún más rápido, puede hacerlo valiéndose del comando «show tables»…

#!/bin/bash
 
if [ $# -lt 2 ]; then
        echo "You must specify database host"
        echo "Eg. script.sh MY_DATABSE 192.168.10.1"
        exit
fi
 
db="$1"
host="$2"
user="root"
 
stty -echo
read -p "Enter MySQL's Admin password: " password
stty echo
 
mysql -u $user --password=$password -h $host --batch --skip-column-names $db -e "SHOW TABLES" |
while read table; do
        echo $table &&
        time mysql -u $user --password=$password -h $host $db -e "ALTER TABLE $table ENGINE=INNODB"
done

La única diferencia es que las tablas ya no son especificadas a través de un arreglo (que recomiendo para BBDD grandes, donde optimizar todas las tablas podría demorar toda la vida), sino que se toman directamente del comando «SHOW TABLES» para una BD especificada.

Script para optimizar las tablas fragmentadas en MySQL

Bueno otro buen script a la huchaca… esta escrito en bash aparte de ser utilisisimo porque chequea las tablas fragmentadas y tambien chequea todas las bbdds en busca de tablas MyISAM o INNODB y las optimiza.

#!/bin/bash

VERSION="0.7.2"
log="$PWD/mysql_error_log.txt"

echo "MySQL fragmentation finder (and fixer) v$VERSION, written by Phil Dufault ( http://www.dufault.info/ )"

showHelp() {
echo -e "\tThis script only repairs MyISAM and InnoDB tables"
echo -e "\t--help or -h\t\tthis menu"
echo -e "\t--user username\tspecify mysql username to use\n\t\t\tusing this flag means the script will ask for a password during runtime, unless you supply..."
echo -e "\t--password \"yourpassword\""
echo -e "\t--host hostname\tspecify mysql hostname to use, be it local (default) or remote"
}

#s parse arguments
while [[ $1 == -* ]]; do
case "$1" in
--help|-h) showHelp; exit 0;;
--user) mysqlUser="$2"; shift 2;;
--password) mysqlPass="$2"; shift 2;;
--host) mysqlHost="$2"; shift 2;;
--) shift; break;;
esac
done

# prevent overwriting the commandline args with the ones in .my.cnf, and check that .my.cnf exists
if [[ ! $mysqlUser && -f "$HOME/.my.cnf" ]]; then
if grep "user=" "$HOME/.my.cnf" >/dev/null 2>&1; then
if grep "pass=" "$HOME/.my.cnf" >/dev/null 2>&1; then
mysqlUser=$(grep user= < "$HOME/.my.cnf" | awk -F\" '{print $2}'); mysqlPass=$(grep pass= < "$HOME/.my.cnf" | awk -F\" '{print $2}'); if grep "host=" "$HOME/.my.cnf" >/dev/null 2>&1; then
mysqlHost=$(grep host= < "$HOME/.my.cnf" | awk -F\" '{print $2}'); fi else echo "Found no pass line in your .my.cnf,, fix this or specify with --password" fi else echo "Found no user line in your .my.cnf, fix this or specify with --user" exit 1; fi fi # set localhost if no host is set anywhere else if [[ ! $mysqlHost ]]; then mysqlHost="127.0.0.1" fi # error out if [[ ! $mysqlUser ]]; then echo "Authentication information not found as arguments, nor in $HOME/.my.cnf" echo showHelp exit 1 fi if [[ ! $mysqlPass ]]; then echo -n "Enter your MySQL password: " read -s mysqlPass fi # Test connecting to the database: mysql -u"$mysqlUser" -p"$mysqlPass" -h"$mysqlHost" --skip-column-names --batch -e "show status" >/dev/null 2>&1
if [[ $? -gt 0 ]]; then
echo "An error occured, check $log for more information.";
exit 1;
fi

# Retrieve the listing of databases:
databases=( $(mysql -u"$mysqlUser" -p"$mysqlPass" -h"$mysqlHost" --skip-column-names --batch -e "show databases;" 2>"$log") );
if [[ $? -gt 0 ]]; then
echo "An error occured, check $log for more information."
exit 1;
fi

echo -e "Found ${#databases[@]} databases";
for i in ${databases[@]}; do
# get a list of all of the tables, grep for MyISAM or InnoDB, and then sort out the fragmented tables with awk
fragmented=( $(mysql -u"$mysqlUser" -p"$mysqlPass" -h"$mysqlHost" --skip-column-names --batch -e "SHOW TABLE STATUS FROM $i;" 2>"$log" | awk '{print $1,$2,$10}' | egrep "MyISAM|InnoDB" | awk '$3 > 0' | awk '{print $1}') );
if [[ $? -gt 0 ]]; then
echo "An error occured, check $log for more information."
exit 1;
fi
tput sc
echo -n "Checking $i ... ";
if [[ ${#fragmented[@]} -gt 0 ]]; then
if [[ ${#fragmented[@]} -gt 0 ]]; then
if [[ ${#fragmented[@]} -gt 1 ]]; then
echo "found ${#fragmented[@]} fragmented tables."
else
echo "found ${#fragmented[@]} fragmented table."
fi
fi
for table in ${fragmented[@]}; do
let fraggedTables=$fraggedTables+1;
echo -ne "\tOptimizing $table ... ";
mysql -u"$mysqlUser" -p"$mysqlPass" -h"$mysqlHost" -D "$i" --skip-column-names --batch -e "optimize table $table" 2>"$log" >/dev/null
if [[ $? -gt 0 ]]; then
echo "An error occured, check $log for more information."
exit 1;
fi
echo done
done
else
tput rc
tput el
fi
unset fragmented
done

# footer message
if [[ ! $fraggedTables -gt 0 ]]; then
echo "No tables were fragmented, so no optimizing was done.";
else
if [[ $fraggedTables -gt 1 ]]; then
echo "$fraggedTables tables were fragmented, and were optimized.";
else
echo "$fraggedTables table was fragmented, and was optimized.";
fi
fi

if [[ ! -s $log ]]; then
rm -f "$log"
fi

unset fraggedTables

Link | http://www.dufault.info/blog/a-script-to-optimize-fragmented-tables-in-mysql/

Instalar Memcached en CentOS 5.3

Memcached is a generic purpose distributed high performance memory object caching system to use in speeding up dynamic database driven websites by caching data and objects in memory to reduce the amount the database needs to be read.

Memcached was originally developed by Danga Interactive for LiveJournal but is now used by many popular and large community driven websites like Slashdot, Wikipedia, SourceForge, GameFAQs, Facebook, Digg, Fotolog, Kayak and like. It is being distributed under a permissive free software licence. Know more about who all are using memcached

Things to consider before Installing memcached.

  1. First, decide how much memory you want to give memcached to use for caching.
  2. Then decide if you want to run memcached on the default port (11211) or not.
  3. Next decide if you want memcached to listen to a specific IP address if you have multiple IP addresses on your server
  4. Finally decide, what user you want to run memcached as; typically, you want to run it using Apache user so that Apache processes can access memcache data

Installation Process

1. If you don’t have rpmforge installed, follow this step.

wget http://dag.wieers.com/rpm/packages/rpmforge-release/rpmforge-release-0.3.6-1.el5.rf.i386.rpm
rpm –install rpmforge-release-0.3.6-1.el5.rf.i386.rpm
yum install –enablerepo=rpmforge memcached

2. Start memcached.

memcached -d -m 512 -l 127.0.0.1 -p 11211 -u nobody

The “-m SIZE” is the flag for setting the memory requirements in MBs. Once this cache is filled memcache will just start to overwrite with newer content. Please experiment with this setting to find what works best for you.

3. Install PHP extension.

wget http://pecl.php.net/get/memcache-2.2.5.tgz

4. Extract tar file.

tar -xvf memcache-2.2.5.tgz

5. Open the directory.

cd memcache-2.2.5

6. Install the memchaced PHP extension.

phpize && ./configure –enable-memcache && make

7.  Copy the extension.

cp modules/memcache.so {PHP extension directory}

8. Edit your php.ini and add the following line.

extension=memcache.so

9. Last is restart your webserver.

10. If you check your server using a phpinfo page you should now see a MemCache section on the page. You can now fully use the MemCache functionality in your PHP.

memcached pre-requissites

yum -y install libevent libevent-devel

How to make memcached run automatically when you restart your server?. Add this line to rc.local

#!/bin/sh
echo “# Start memcached” >> /etc/rc.local
echo “/usr/local/bin/memcached -d -m 1024 -u httpd -l 127.0.0.1″ >> /etc/rc.local

How to have a multiple memcached server.

Create LocalSettings.php file and this line.

$wgMainCacheType = CACHE_MEMCACHED;
$wgParserCacheType = CACHE_MEMCACHED; # optional
$wgMessageCacheType = CACHE_MEMCACHED; # optional
$wgMemCachedServers = array( “127.0.0.1:11211″ );

$wgSessionsInMemcached = true; # optional

To use multiple servers (physically separate boxes or multiple caches on one machine on a large-memory x86 box), just add more items to the array. To increase the weight of a server (say, because it has twice the memory of the others and you want to spread usage evenly), make its entry a subarray:

$wgMemCachedServers = array(“127.0.0.1:11211″, # one gig on this box
array(“127.0.0.1:11211″, 2 ) # two gigs on the other box
);

Security Note:

Memcached has no security or authentication. Please ensure that your server is appropriately firewalled,
and that the port(s) used for memcached servers are not publicly accessible. Otherwise, anyone on the internet can put data into and read data from your cache.

ENJOY FRIENDS AND MAKE EASY¡¡¡¡¡¡¡¡¡¡¡

Reparar todas las tablas de todas las bases de datos de MySQL en Plesk

Para reparar todas las tablas de todas las bases de datos de MySQL en Plesk (Linux) tenemos que poner en una sola linea este pedazo churro:

for database in $(mysql --skip-column-names -uadmin -p`cat /etc/psa/.psa.shadow` -e
"show databases" ); do echo "optmizing tables from $database";
for table in $(mysql --skip-column-names -uadmin -p`cat /etc/psa/.psa.shadow` -e
"show tables" $database ); do echo "-> $table " ;
mysql -uadmin -p`cat /etc/psa/.psa.shadow` -e "OPTIMIZE TABLE $table" $database ;
done ; done ;

Cómo chequear, reparar u optimizar tablas o bases de datos MySQL con mysqlcheck

El cliente mysqlcheck comprueba y repara tablas MyISAM. También puede optimizar y analizar tablas.

mysqlcheck es similar a myisamchk, pero funciona de forma distinta. La principal diferencia operacional es que mysqlcheck debe usarse cuando el servidor mysqld está en ejecución, mientras que myisamchk debe usarse cuando no lo está. El beneficio de usar mysqlcheck es que no tiene que parar el servidor para comprobar o reparar las tablas.

mysqlcheck usa los comandos SQL CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, y OPTIMIZE TABLE de forma conveniente para los usuarios. Determina los comandos a usar en función de la operación que quiera realizar, luego envía los comandos al servidor para ejecutarlos.

Hay tres modos generales de invocar mysqlcheck:

shell> mysqlcheck [opciones] nombre_de_base_de_datos [tablas]
shell> mysqlcheck [opciones] –databases DB1 [DB2 DB3…]
shell> mysqlcheck [opciones] –all-databases

Si no nombra ninguna tabla o usa las opciones –databases o –all-databases, se comprueban todas las bases de datos.

mysqlcheck soporta las siguientes opciones:

Continuar leyendo «Cómo chequear, reparar u optimizar tablas o bases de datos MySQL con mysqlcheck»