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

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

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!

Iconize is a cool CSS layout with a set of icons to iconize each kind of link inside your webpage. As I decided to use them in one of my personal projects I found out a little trouble with it because it didn’t work on the first try. Thinking on how http works I tried to find if the directory icons/ had some visibility from the internet and what i discovered? it worked but… wasn’t the icons folder of Iconize. WTF! was the icons folder of Apache!! so… working on Ubuntu:

grep -R icons /etc/apache2/*

And there it goes:

/etc/apache2/mods-enabled/alias.conf:#Alias /icons/ “/usr/share/apache2/icons/”

I added a comment on the line like:

#Alias /icons/ “/usr/share/apache2/icons/

And rebooted Apache:

/etc/init.d/apache2 restart

Problem solved, everything working.

Thank you pooliestudios.com people! Iconize is a great work!

After configuring a subversion server for web projects you should take care about the .svn directories and files, ¿how may I secure them from the Internet savage? I found an easy way to do it with the .htaccess file:

RewriteEngine on
RewriteRule .*\.svn/.* – [F]

Don’t forget to enable mod_rewrite, and if you are already using some rule like

RewriteRule ^.*$

be sure of writing first the new rule:

RewriteRule .*\.svn/.* – [F]

RewriteRule ^.*$

Source: http://forum.webfaction.com/viewtopic.php?id=1069

Sometimes a high level of security in a website can be painful. In my case, I lost some phpMyAdmin functionalities because the use of mod_security. The /var/log/httpd/modsec_debug.log said:

[15/Mar/2010:20:31:42 +0100] [thesite.com/sid#80082db8][rid#80643ac0][/phpMyAdmin/import.php][2] Warning. Pattern match “(?:\b(?:(?:s(?:elect\b(?:.{1,100}?\b(?:(?:length|count|top)\b.{1,100}?\bfrom|from\b.{1,100}?\bwhere)|.*?\b(?:d(?:ump\b.*\bfrom|ata_type)|(?:to_(?:numbe|cha)|inst)r))|p_(?:(?:addextendedpro|sqlexe)c|(?:oacreat|prepar)e|execute(?:sql)?|makewebtask)|ql_(? …” at ARGS:prev_sql_query. [file “/etc/httpd/modsecurity.d/modsecurity_crs_40_generic_attacks.conf”] [line “66”] [id “950001“] [msg “SQL Injection Attack“] [data “select * from `users`“] [severity “CRITICAL”] [tag “WEB_ATTACK/SQL_INJECTION”]

[15/Mar/2010:20:31:42 +0100] [thesite.com/sid#80082db8][rid#80643ac0][/phpMyAdmin/import.php][2] Warning. Pattern match “(?:\b(?:(?:s(?:elect\b(?:.{1,100}?\b(?:(?:length|count|top)\b.{1,100}?\bfrom|from\b.{1,100}?\bwhere)|.*?\b(?:d(?:ump\b.*\bfrom|ata_type)|(?:to_(?:numbe|cha)|inst)r))|p_(?:(?:addextendedpro|sqlexe)c|(?:oacreat|prepar)e|execute(?:sql)?|makewebtask)|ql_(? …” at ARGS:sql_query. [file “/etc/httpd/modsecurity.d/modsecurity_crs_40_generic_attacks.conf”] [line “66”] [id “950001“] [msg “SQL Injection Attack“] [data “select * from `users` where“] [severity “CRITICAL”] [tag “WEB_ATTACK/SQL_INJECTION”]

[15/Mar/2010:20:31:42 +0100] [thesite.com/sid#80082db8][rid#80643ac0][/phpMyAdmin/import.php][1] Access denied with code 400 (phase 2). Pattern match “\%(?!$|\W|[0-9a-fA-F]{2}|u[0-9a-fA-F]{4})” at ARGS:sql_query. [file “/etc/httpd/conf/modsecurity/modsecurity_crs_20_protocol_violations.conf”] [line “64”] [id “950107“] [msg “URL Encoding Abuse Attack Attempt”] [severity “WARNING”]

So, I had to add some local rules to the file /etc/httpd/modsecurity.d/modsecurity_localrules.conf

<LocationMatch “/phpMyAdmin/import.php”>
SecRuleRemoveById 950107
SecRuleRemoveById 950001
</LocationMatch>

I restarted the apache and everything is working!

Updated (16/03/2010):

I still had some troubles, so, I decided to disable mod_security totally on phpMyAdmin directory with the line:

SecRule REQUEST_URI ^/phpMyAdmin phase:1,allow,ctl:ruleEngine=off

In the file /etc/httpd/modsecurity.d/modsecurity_localrules.conf

Cheers!

Some tips and tricks.

Exporting data from DB2 to files in linux (I like EXPORT but you also can use UNLOAD or a simple SELECT and the redirection >):

$ db2 EXPORT TO “/tmp/unload.txt” OF del MODIFIED BY COLDEL, CHARDEL* SELECT * FROM MYTABLE

This command will export the table MYTABLE to the file /tmp/unload.txt formatting the fields like *COL1*,*COL    2*,*COL NUM 3*,…,*COL N*

In MacOSX formatting the files from UTF-8 format to ISO-8859-1 format (because the special spanish chars áéíóúñ…):

$ iconv -f UTF-8 -t ISO-8859-1 unload.txt > unload_8859.txt