Pedro's blog: db2, mysql, php, linux and performance

From the Giovani Collazo we get a git quick reference tip on how to use git everyday:

Everyday I use git on a local repository were I am working alone, under this scene there is no problem with overwritten others changes or using outdated code but, what if I am working with a repository were other coders are working too?

Here it comes the recipe:

• Checkout the master branch and pull of the most recent changes:
  

  $git checkout master
  $git pull

• Create a new branch to work on the new changes
  

  $git branch my-new-cool-branch
  $git checkout my-new-cool-branch

• Do your changes and afterwards you add and commit them
  

  $git status
  $git add .
  $git commit -a -m ‘Bug fix #1782 and new method getFullDescription’

• Now is when the magic comes, we are going to rebase with the master branch and then merge it
  

  $git checkout master
  $git pull
  $git checkout my-new-cool-branch
  $git rebase master

  If there is any conflict it will appear now! solve it in your branch and try again.

• The last step is to checkout with the master and merge our branch
  

  $git checkout master
  $git merge my-new-cool-branch

• Right now everything is fine so we can send our changes to github
  

  $git push

The source of the recipe is in spanish here:  http://elweb.co/como-uso-git-todos-los-dias/

Do you want to learn more about git? take a look to the entire Pro Git book, written by Scott Chacon and Ben Straub http://git-scm.com/book

The Respo wordpress theme is a cool theme for wordpress. It is free and it has a clean and simple design. After installing it you will find that the pictures of the posts in the blog ‘loop page’ are not working. To fix it I was researching a little in google and I found out a way to make it work for me:

1. Update the file wp-content/themes/respo/js/timthumb.php with the last version of the timthumb.php in the repository.
2. Comment the line 825:
   

   // $docRoot = @$_SERVER[‘DOCUMENT_ROOT’];

3. Check that every post has assigned is Featured Image.

After doing the 3 steps, the pictures are working in the blog ‘loop’s page’ and the problem with the respo timthumb.php script is fix!

Hello since April!
I am a little bit busy in the last months with some personal projects, bla bla bla…
Anyway, I had to improve the mysqldump command because the admin was getting some mails saying:
MySQL Error : MySQL server has gone away

The old command was:
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
The new command is:
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS --single-transaction --quick --lock-tables=false $db | $GZIP -9 > $FILE

The source of the tip is http://stackoverflow.com of course…

Yesterday we had a surprise, something happen, take a look:

This is the IO Service time graph – by day, and you can see the difference around nine in the morning between yellow and orange color lines (sdb write and read  stats) and the blue and green color lines (sda drive write and read stats). The hard disk working as /dev/sdb failed around twenty past nine and we found out at seven pm.

The mdadm command said something about “faulty spare”, the output was (command: mdadm –misc –detail /dev/md1):

dev/md2:
Version : 0.90
Creation Time : Thu Dec 16 11:16:09 2010
Raid Level : raid1
Array Size : 1454122944 (1386.76 GiB 1489.02 GB)
Used Dev Size : 1454122944 (1386.76 GiB 1489.02 GB)
Raid Devices : 2
Total Devices : 2
Preferred Minor : 2
Persistence : Superblock is persistent
Update Time : Thu Apr 19 20:13:58 2012
State : clean, degraded
Active Devices : 1
Working Devices : 1
Failed Devices : 1
Spare Devices : 0
UUID : 36c6fe3e:6fbcc6a0:a4d2adc2:26fd5302
Events : 0.108754
Number   Major   Minor   RaidDevice State
0       8        2        0      active sync   /dev/sda2
1       0        0        1      removed
2       8       18        –      faulty spare /dev/sdb2

So, we opened a ticket to the hosting team and they take like 10 minutes to change the disk, awesome support! then, copying partitions and rebuilding raid1 array:

#sfdisk -d /dev/sda | sfdisk -f /dev/sdb

#mdadm /dev/md1 –manage –add /dev/sdb1
mdadm: added /dev/sdb1
# mdadm /dev/md2 –manage –add /dev/sdb2
mdadm: added /dev/sdb2
# mdadm –misc –detail /dev/md1 | grep sdb
1       8       17        1      active sync   /dev/sdb1
# mdadm –misc –detail /dev/md2 | grep sdb
2       8       18        1      spare rebuilding   /dev/sdb2

Syncing:
# cat /proc/mdstat
Personalities : [linear] [raid0] [raid1] [raid10] [raid6] [raid5] [raid4] [multipath] [faulty]
md1 : active raid1 sdb1[1] sda1[0]
10485696 blocks [2/2] [UU]
md2 : active raid1 sdb2[2] sda2[0]
1454122944 blocks [2/1] [U_]
[>………………..]  recovery =  0.1% (2138624/1454122944) finish=3913.2min speed=6183K/sec

And then swap!
# cat /proc/swaps
Filename                                Type            Size    Used    Priority
/dev/sda3                               partition       526236  0       -1
# mkswap /dev/sdb3
Setting up swapspace version 1, size = 538865 kB
# swapon -a
# cat /proc/swaps
Filename                                Type            Size    Used    Priority
/dev/sda3                               partition       526236  0       -1
/dev/sdb3                               partition       526236  0       -2
[root@ns24862 ~]# free
total       used       free     shared    buffers     cached
Mem:      12318872   12069548     249324          0     507552    9107340
-/+ buffers/cache:    2454656    9864216
Swap:      1052472          0    1052472

After some long time, everything working again like a charm!

After the CMS migration I commented about on the article MySQL Tuning Query Cache And Thread Cache, google had a couple of naps, the last one looked CRAZY on the stats! take a look yourself:

Does anybody need me to draw a circle around the difference? 🙂

Here I collected some useful scripts for helping you tuning MySQL:

• MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability. MySQLTuner
• MySQL Performance Tuning Primer Script. This script takes information from “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…”
  to produce sane recomendations for tuning server variables. MySQL Performance Tuning Primer Script
• A Defragmentation Script to Optimize Fragmentated Tables in MySQL, finds your fragmented tables…and defragments them. A Defragmentation Script to Optimize Fragmentated Tables in MySQL

The first two will help you checking the MySQL most important performance values and giving you advice on how to modify them. The last one is really useful for finding every fragmented table and defragment it automatically.

Thanks to the authors!

Hello,

since we did the migration of a website from the php-nuke+phpbb to drupal+vbulletin, the cpu load of the server was getting around 20-30% more busy. I had to take a look to the mysql performance because it was using around the 20% of the CPU. First of all I activated the query cache on the /etc/my.cnf file:

query-cache-type = 1

And set the cache size to 20MB:

query-cache-size = 20M

After rebooting the MySQL service, the busy percentage decreased a bit. You can learn more about the query-cache on the MySQL Query Cache official website.

mysql> SHOW VARIABLES LIKE ‘have_query_cache’;
+——————+——-+
| Variable_name    | Value |
+——————+——-+
| have_query_cache | YES   |
+——————+——-+
1 row in set (0.00 sec)

The next step I took was to activate the thread cache:

mysql> SET GLOBAL thread_cache_size=40;
mysql> SELECT @@global.thread_cache_size;
+—————————-+
| @@global.thread_cache_size |
+—————————-+
| 40 |
+—————————-+
1 row in set (0.00 sec)

And include it on the /etc/my.cnf :

thread_cache_size=40

Since then, the MySQL server was caching every thread and every query and the cpu usage performance got reduced around 15%. While I was setting the new values I saw this one:

wait_timeout=600

And I reduce it to:

wait_timeout=10

Because there is no need for holding the connections 10 minutes, 10 seconds are more than enough. By now everything is working fine and you can see the load saving on the graph:

mysql cache optimization

Everything was working properly until one of the users add a summary artwork greater than the cast size in the xquery select.
Here the select i am using:

select A.ARCHIVO_ID,
F.ARCHIVO_FORMATO_DESCRIPCION_ES,
X.NOMBRE#,
X.RESUMEN#,
X.AUTOR#
from ARCHIVO A,
ARCHIVO_FORMATO F,
ARCHIVO_LOCALIZACION L,
ARCHIVO_TECNICA T,
ARCHIVO_CATEGORIA C,
ASIGNATURA_ARCHIVO AA,
ARCHIVO_BIBLIOGRAFIA B ,
xmltable(
‘$c/obra’ passing A.ARCHIVO_CARACTERISTICAS as “c” columns
NOMBRE# VARCHAR(100) path ‘nombre’,AUTOR# VARCHAR(100) path ‘autor’,
RESUMEN# VARCHAR(1000) path ‘resumen’
) as X
where A.ARCHIVO_ESTADO BETWEEN 1 AND 2 AND
A.ARCHIVO_PRIVADO = ‘N’ AND
…

The describe table:

db2 => describe table archivo

Data type Column
Column name schema Data type name Length Scale Nulls
——————————- ——— ——————- ———- —– ——
ARCHIVO_ID SYSIBM INTEGER 4 0 No
ARCHIVO_FORMATO SYSIBM INTEGER 4 0 No
ARCHIVO_TIPOLOGIA SYSIBM INTEGER 4 0 No
ARCHIVO_LOCALIZACION SYSIBM INTEGER 4 0 No
ARCHIVO_ESTADO SYSIBM INTEGER 4 0 No
ARCHIVO_VISITAS SYSIBM INTEGER 4 0 No
ARCHIVO_USUARIO_CREACION SYSIBM INTEGER 4 0 No
ARCHIVO_IP_CREACION SYSIBM VARCHAR 16 0 No
ARCHIVO_FECHA_CREACION SYSIBM TIMESTAMP 10 6 No
ARCHIVO_USUARIO_MODIFICACION SYSIBM INTEGER 4 0 Yes
ARCHIVO_IP_MODIFICACION SYSIBM VARCHAR 15 0 Yes
ARCHIVO_FECHA_MODIFICACION SYSIBM TIMESTAMP 10 6 Yes
ARCHIVO_PRIVADO SYSIBM VARCHAR 1 0 No
ARCHIVO_NOTA SYSIBM DOUBLE 8 0 Yes
ARCHIVO_CARACTERISTICAS SYSIBM XML 0 0 No
ARCHIVO_SOURCE SYSIBM BLOB 262144000 0 Yes

A XML file example:

<?xml version=”1.0″ encoding=”iso-8859-15″?>
<obra tipo=”Imagen”>
<nombre>Sin t&amp;iacute;tulo</nombre>
<autor>Vicente Ort&amp;iacute; Mateu</autor>
<periodo>Siglo XXI</periodo>
<archivo>42.jpg</archivo>
<resumen>escultura en m&amp;amp;aacute;rmol crema Valencia y madera, colecci&amp;amp;oacute;n particular</resumen>
<mime_type></mime_type>
<alto>2352</alto>
<ancho>2208</ancho>
<descriptores>
<descriptor></descriptor>
</descriptores>
</obra>

And here the error:

SQL16061N The value “El presente artículo constituye un inv…” cannot
be constructed as, or cast (using an implicit or explicit cast) to the data
type “VARCHAR_1000”. Error QName=err:FORG0001. SQLSTATE=10608

I had to change the cast from RESUMEN# VARCHAR(1000) to RESUMEN# VARCHAR(2000) because the select was failing and I was getting 0 results…

I just update the php of one of the servers I’m handling on and I got a surprise… every f*cking ereg() or eregi() function was displaying a pretty sentence:

Function ereg() is deprecated in…

How to disable this annoying warnings?
Fast solution:go to the php.ini file and add an ~E_DEPRECATED on the error_reporting var like:

error_reporting = E_ALL & ~E_NOTICE & ~E_DEPRECATED

Best solution:use preg_match() instead

Enjoy!

Something is going on DB2…

db2 “list tablespaces”

Tablespace ID = 2
Name = USERSPACE1
Type = Database managed space
Contents = All permanent data. Large table space.
State = 0x0020
Detailed explanation:
Backup pending

Solving it:

db2 backup database YOURDB online to /theBackupDir/