Often when a web-application seems slow it’s not (or not only) the parse time, but also or mayorly database speed. Most scripting-languages don’t act “intelligent” in parsing as it would be best for user experience, but go through the code line by line (whom would expect it otherwise?). So if you have a website containing some slow MySQL-Queries it seems as if all the page just takes a eternity to load. One step would be to put all the page loading into some seperate code-piece and load it later with ajax-technology. This gives a great boost in user-experienced speed, because the most parts of the website load and are already usable before the database content pops in. But otherwise often not the database is the problem, but bad written querys. But how to find them? Luckily mysql already provides us with a feature called slow-query-log which can be activated easily through my.cnf. So just look for this:
vi /etc/mysql/my.cnf
........
#log_slow_queries = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
...........
uncomment these lines and you will have a nice log showing which queries take longer than 2 seconds which is a quite good value. Of course you can set it to what you want. It’s the time in seconds it takes mysql to qualify a query as slow. A plus is the option “log-queries-not-using-indexes” which does exactly this. It’s good to find queries which do a full table scan, but would (perhaps) be more performant if they would use an index. Actually using an index not everytime is better. You have to test this practically. In some cases it is faster to do the full table scan (and have some of the result already in buffers). So just try-and-error. In some cases it gives a huge performance boost. Read this to get more information:
http://dev.mysql.com/doc/refman/5.1-maria/en/mysql-indexes.html
So what to do if you logged some queries which take long? Try to find why the queries are slow. Sometimes there are tables used in a SELECT which data isn’t needed at all at this point (often happens through copy&paste by developers) in other cases perhaps the information would be better retrieved once and stored in memory to be used again. See memcache in this case:
http://php.net/manual/en/book.memcache.php
Perhaps also the data isn’t important for displaying the inital page and can be loaded with ajax, as mentioned above.
A simple Online-Slow-Query Logger
Often in a production environment the slowquery-Log is disabled. Now there’s a problem, you want to enable it, but you can’t restart the database. That’s why I wrote a little php-script which connects to the database and shows slow-queries.
Command:
The only argument is the slow-query-threshold in seconds.
php slow-query-logger.php 2
<?php
if( $argc != 2) die("Wrong number of arguments, just give number of seconds");
$time = $argv[1];
$db = @new mysqli('localhost', 'root', 'somepw', 'somedb');
if (mysqli_connect_errno()) {
die ('could not connect to database: '.mysqli_connect_error().'('.mysqli_connect_errno().')');
}
while(1)
{
$sql = "SELECT * FROM information_schema.PROCESSLIST WHERE time >= ".$time." AND info IS NOT NULL";
$result = $db->query($sql);
if (!$result) {
die ('query error: '.$db->error);
}
while($data=$result->fetch_assoc())
{
echo implode(" ", $data)."\n";
}
sleep(1);
}
$db->close();
?>
October 14th,2009
Misc |
No Comments
Credits
This very good article is written by Marcus Redivo.
http://www.eclectica.ca/howto/openbsd-software-raid-howto.php
I added a warning to stress one point (see red font) which took me 5 hours to find the error, the rest is the orginal tutorial, have fun.
Installing OpenBSD 64Bit on a Software Raid 1 (Mirroring)
This document describes how to set up RAID mirroring on OpenBSD with the RAIDframe driver built into the kernel.
Overview
This procedure assumes OpenBSD 4.5, the amd64 architecture (should work with any other architecture too), and two 320GB sata disks; wd0 and wd1. The steps involved are:
* Install OpenBSD on wd0
* Compile a kernel that supports RAID
* Reboot into RAID-enabled kernel
* Configure RAID partitions on wd1 as half of a broken mirror
* Copy all files onto the RAID partitions
* Reboot into broken mirror
* Reallocate wd0, hot-add it, and reconstruct the broken mirror
* Reboot into complete RAID environment
OpenBSD will not boot from a RAIDframe RAID set at present, so wd0a and wd1a will be set aside as partitions to boot from. Kernel RAID autoconfiguration will prepare the RAID set at boot time, and mount the RAID partitions as per /etc/fstab.
Disk Preparation
I like to start with completely clean disks. If they have been used before, I wipe them before doing anything else, using dd(1). Boot from the OpenBSD distribution CD to get a shell, and execute:
dd if=/dev/zero of=/dev/rwd0c bs=1024000 &
dd if=/dev/zero of=/dev/rwd1c bs=1024000 &
This will take a while. To keep busy, you can set up the Master Boot Record and the BSD disklabel on each disk.
wd0 will get a temporary installation of OpenBSD, but we will only keep the first partition when we are done. We will use this installation to configure and compile a kernel with RAID support. The following sizes will work:
Part Size FS Type Purpose
---- ----- ------- -------------------------------
a: 512M 4.2BSD / (becomes boot partition)
c: ----- unused Entire drive
d: 1024M 4.2BSD /usr
e: 512M 4.2BSD /tmp
f: 1024M 4.2BSD /var
g: 512M 4.2BSD /home
On wd1, we need two partitions; a boot partition, and a partition to contain the RAID set:
Part Size FS Type Purpose
---- ----- ------- -------------------------------
a: 512M 4.2BSD Boot partition
c: ----- unused Entire drive
d: * RAID Everything except boot kernel
It’s important to set the FS Type to RAID for the wd1d Partition, otherwise the System won’t boot !
If later during boot you get the following error, check the FS Type with “disklabel wd1″ and set it to RAID, if this not the case
Kernelized RAIDframe activated
softraid0 at root
root on wd1a swap on wd1b dump on wd1b
warning: /dev/console does not exist
init: not found
panic: no init
Stopped at Debugger + 0x5 .... and so on
Use the following commands to implement this:
fdisk -i wd0
disklabel -E wd0
...
fdisk -i wd1
disklabel -E wd1
...
Reboot from media, and follow the normal installation procedure to install OpenBSD onto wd0. If you followed the steps above, all partitions will already be set up; simply quit disklabel(8) and move on to assigning partitions to their mount points. If you started here, use the instructions above while in disklabel.
Make a RAID-Capable Kernel
To build a kernel, we need the source code, and a configuration. Source code is on the CD:
mount /dev/cd0a /mnt
cd /usr/src
tar xzpf /mnt/src.tar.gz
cd sys/arch/i386/conf
If you don’t have the cd available you can get it also per FTP
for name enter “anonymous”, for password just press ENTER
cd /usr/src
ftp mirror.roothell.org
Connected to mirror.roothell.org.
220 blog.roothell.org FTP server ready.
Name (mirror.roothell.org:user123): anonymous
331 Guest login ok, send your email address as password.
Password:
230 Guest login ok, access restrictions apply.
Remote system type is UNIX.
Using binary mode to transfer files.
ftp> get /pub/OpenBSD/4.5/src.tar.gz ./src.tar.gz
ftp> get /pub/OpenBSD/4.5/sys.tar.gz ./sys.tar.gz
tar xzvvf src.tar.gz
tar xzvvf sys.tar.gz
The configuration is the same as GENERIC, with two additions. Create a file called GENERIC.RAID in /usr/src/sys/arch/amd64/conf with the following contents:
---Begin---
#
# GENERIC.RAID - Add kernelized RAIDframe driver
#
include "arch/amd64/conf/GENERIC"
option RAID_AUTOCONFIG # Automatically configure RAID at boot
pseudo-device raid 4 # RAIDframe disk driver
----End----
The following steps will build and install the new kernel:
config GENERIC.RAID
cd ../compile/GENERIC.RAID
make depend && make
mv /bsd /bsd.original
cp bsd /bsd
chmod 644 /bsd
Now reboot into the RAID-enabled kernel.
Second Disk Setup – Make A Broken Mirror
To prepare the second disk, we need to accomplish the following:
* Make it bootable
* Create a RAID configuration
* Apply the configuration to the disk
* Create a disklabel for the RAID device
* Create filesystems in the RAID device partitions
* Make the RAID device autoconfigurable
* Copy our installation onto the new partitions
* Update fstab to refer to the RAID device
* Reboot into the RAID environment
Make the second disk (wd1) bootable:
newfs /dev/rwd1a
mount /dev/wd1a /mnt
cp /bsd /mnt/bsd
cp /usr/mdec/boot /mnt/boot
/usr/mdec/installboot -v /mnt/boot /usr/mdec/biosboot wd1
Create /etc/raid0.conf, which should look like this:
START array
# rows (must be 1), columns, spare disks
1 2 0
START disks
/dev/wd2d
/dev/wd1d
START layout
# sectPerSU SUsPerParityUnit SUsPerReconUnit RAID_level
128 1 1 1
START queue
# queue mode, outstanding request count
fifo 100
Notice that under disks, a (non-existent) wd2 is specified. When the RAID set is created, this serves as a placeholder, and will appear as failed. Once we are ready, we will hot-add wd0 as a spare and reconstruct the mirror on it. Once that is done, this configuration file will be updated to reflect the actual disk assignments.
Now that you have this configuration file, implement it with:
raidctl -C /etc/raid0.conf raid0
raidctl -I 20050900 raid0
raidctl -iv raid0
The argument to -I is a unique identifier that permits RAIDframe to determine which disks belong to which RAID sets; I used the year and month, plus ’00′ to indicate the first RAID set.
It is time to define the partitions on the RAID device. For my purposes, I used the following:
Part Size FS Type Purpose
---- ----- ------- -------------------------------
a: 512M RAID /
b: 1024M swap swap
c: ----- unused Entire drive
d: 5G RAID /usr
e: 5G RAID /tmp
f: 42G RAID /var
g: * RAID /home
Again, we use disklabel(8) to put this into effect. Once that is done, create filesystems in the partitions that need them, and make the RAID set auto-configurable as a root filesystem:
disklabel -E raid0
...
newfs /dev/rraid0a
newfs /dev/rraid0d
newfs /dev/rraid0e
newfs /dev/rraid0f
newfs /dev/rraid0g
raidctl -A root raid0
Copy the data from the initial installation over to the RAID partitions, making certain to preserve permissions:
mount /dev/raid0a /mnt
(cd /; tar -Xcpf - .) | (cd /mnt; tar -xpf -)
tar: Ustar cannot archive a socket ./dev/log
umount /mnt
mount /dev/raid0d /mnt
(cd /usr; tar -cpf - .) | (cd /mnt; tar -xpf -)
umount /mnt
mount /dev/raid0f /mnt
(cd /var; tar -cpf - .) | (cd /mnt; tar -xpf -)
tar: Ustar cannot archive a socket ./cron/tabs/.sock
tar: Ustar cannot archive a socket ./empty/dev/log
umount /mnt
mount /dev/raid0g /mnt
(cd /home; tar -cpf - .) | (cd /mnt; tar -xpf -)
umount /mnt
(You can ignore the errors; the sockets will be recreated.)
Update /etc/fstab on the broken mirror to point to the raid0 partitions instead of the old wd0 partitions, and reboot:
mount /dev/raid0a /mnt
sed 's/wd0/raid0/g' /mnt/etc/fstab > /mnt/etc/fstab.tmp
mv /mnt/etc/fstab.tmp /mnt/etc/fstab
umount /mnt
reboot
boot> boot wd1a:/bsd
Complete the Mirror Pair
The last step is to integrate the first disk into the mirror pair. We need to update the wd0 disklabel to match wd1, which is easily done by copying:
disklabel wd1 >disklabel.wd1
disklabel -R wd0 disklabel.wd1
There is no need to put a disklabel into the RAID partition, as this will be taken care of when the mirror is reconstructed.
To reconstruct the array, hot add wd0 as a new spare, and then start reconstruction. When reconstructions is complete, rebuild the parity.
Note: Reconstruction and parity rewrite can take a long time. Do not reboot while this is in progress, or you may lose everything and need to start over.
raidctl -a /dev/wd0d raid0
raidctl -vF component0 raid0
raidctl -vP raid0
One last step remains; the RAID configuration file must be updated to remove the bogus wd2 entry and replace it with an entry for wd0:
sed 's/wd2/wd0/g' /etc/raid0.conf > /etc/raid0.conf.tmp
mv /etc/raid0.conf.tmp /etc/raid0.conf
Reboot, and check the RAID status:
reboot
...
raidctl -s raid0
raid0 Components:
/dev/wd0d: optimal
/dev/wd1d: optimal
No spares.
Parity status: clean
Reconstruction is 100% complete.
Parity Re-write is 100% complete.
Copyback is 100% complete.
September 27th,2009
Misc |
4 Comments
Until now when I wrote some scripts I always copied them directly to the Servers .I always thought: “It would be really cool if this were a Debian Package”. I played with the thougth of creating my own packages but it seemed to be very complicated (reading the Debian Standard Howto). Lately I discovered, that the Debian Howto covers a lot more than I actually needed and to create a simple binary package you actually need almost only your scripts. So let’s start.
Package-Folder-Structure
Every Debian Package has to contain at least a directory named “DEBIAN” and this has to contain the files “control”, “postinst” and “prerm”.
“postinst” and “prerm” are scripts which are executed “post installation” or “pre removal”. So you can figure out what they’re for 
. The control-file contains information about the package e.g. the name, version, etc.
Folder-Structure
mypackage/
|-- DEBIAN
| |-- control
| |-- postinst
| `-- prerm
Basic control file
A basic control-file could look like this:
Package: mydebianpackage
Version: 0.1
Priority: optional
Architecture: all
Essential: no
Depends: perl
Maintainer: Jonas Garcia Koehel
Provides: mydebianpackage
Description: Some scripts used to do something
detailed description of the fields: http://www.debian.org/doc/debian-policy/ch-controlfields.html
Basic postinst and prerm scripts
They do nothing but have to exist to build the package
Create the folder structure including own scripts
Lets say you want your scripts “script1.pl”, “script2.pl”, and “script3.pl” in the folder /usr/bin and you want “myscripts.conf” in /etc, so you create the following structure inside a build-directory (which can be named freely).
mydebianpackage/
|-- DEBIAN
| |-- control
| |-- postinst
| `-- prerm
|-- etc
| |-- myscripts.conf
|
`-- usr
|-- bin
|-- script1.pl
|-- script2.pl
`-- script3.pl
That’s all
Speaking the magic spell
And Abrakadabra:
dpkg -b ./mydebianpackage/ mydebianpackage_0.1.deb
Ready is your own debian package
Install it on a system
To install it just copy it to a server and type:
dpkg -i mydebianpackage_0.1.deb
to remove it again
apt-get remove mydebianpackage
July 8th,2009
Linux |
1 Comment
Intro
Who isn’t impressed by all these real cool computers in the sciene fictions movies which can be controlled by voice commands, and which talk back. So am I and I thought about how cool it would be if this would be possible today. Totally aware that this is way out of my skill range I thought about an easier solution to make the computer actually talk to me. Remembering an article about chatterbots I searched a bit the net and found AIML ( http://en.wikipedia.org/wiki/AIML ) the “Artificial Intelligence Markup Language”. It’s a xml dialect for creating chatterbot databases. And luckily some programmers already put together some tools. I decided to use “PyAIML” because it seems to be really easy to use. But I can still hear no talking, you think? Just wait a bit, here it comes: espeak. It’s a speech synthesizer which reads out text you pass to it. So let’s get started!
Getting the tools
espeak
I have ubuntu linux and used apt-get to install some tools, it surely works almost the same on other systems with their package systems.
apt-get install espeak espeak-data
PyAIML
Download it here and extract to a directory
http://pyaiml.sourceforge.net/
Some Basic AIML files
Just download and extract as well. I started with the “Standard” AIML file.
http://aitools.org/Free_AIML_sets
A little chatterbot
The PyAIML Page gives pretty clear instructions how to create your own chatbot. Here is mine:
#!/usr/bin/python
import aiml
import commands
k = aiml.Kernel()
# load the aiml file
k.learn("firsttry.aiml")
# set a constant
k.setBotPredicate("name", "Chatty")
while True:
input = raw_input("> ")
response = k.respond(input)
# print out on the shell
print response
# and as speech
print commands.getoutput("/usr/bin/espeak -v en+f4 -p 99 -s 160 \"" + response + "\"")and here the firsttry.aiml
<?xml version="1.0" encoding="utf-8"?>
<aiml version="1.0.1" xmlns="http://alicebot.org/2001/AIML-1.0.1"
xmlns:html="http://www.w3.org/1999/xhtml"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://alicebot.org/2001/AIML-1.0.1 http://aitools.org/aiml/schema/AIML.xsd">
<category>
<pattern>*</pattern>
<template>Hello, I'm glad to see you</template>
</category>
<category>
<pattern>WHAT IS YOUR NAME</pattern>
<template>My name is <bot name="name"></bot></template>
</category>
</aiml>Invoking the bot
Now you just have to start the program
And it already gives some answers.
Loading jgk.aiml... done (0.02 seconds)
> Hello
Hello, I'm glad to see you
Of course you have to add additional patterns to really let the bot talk a bit. The example files (look in the upper section of this tutorial) are a good start.
Disclaimer: This are my first trys with the Soap Interface. If you’ve got better information just send it to me or post a comment, I will gladly update this documentation. I just wrote this article because there is almost no information about the OTRS Soap-Interface on the web. Perhaps it helps someone.
At first it is necessary to set the Soap user and password:
- - Login into your existing OTRS Installation as Admin
- - in the Menu under “Admin”->”Sysconfig” set Group to “Framework” and click “show”
- - In the list of subgroups click “Core::SOAP”
- - Check the boxes for SOAP::User: and SOAP::Password: and enter the values you like
- - click update
Now you can use the OTRS Functions over SOAP.
At my test installation the Setting of the username and password over the admin-interface didn’t work at all. I had to enter it manually in the rpc.pl, but normally I think it should work.
You find a documentation of the OTRS-Modules/Functions you can use here:
http://dev.otrs.org/
The invocation of a Module/Function works like follows:
$RPC->Dispatch($User, $Pw, 'Module','Function', Parameter);
A good start is the rpc-example.pl, which you find in the folder scripts of your OTRS-Install. I modified the example a bit to create a ticket. No guarantee that this doesn’t crash your database, so don’t try on a live system.
scripts/rpc-example.pl
use SOAP::Lite('autodispatch', proxy => 'http://localhost/otrs/rpc.pl');
my $User = 'exampleuser';
my $Pw = 'examplepass';
my $RPC = Core->new();
# create a new ticket number
my $TicketNumber = $RPC->Dispatch($User, $Pw, 'TicketObject','TicketCreateNumber');
print "RPC: New Ticketnumer created: ".$TicketNumber."\n";
my $TicketId = $RPC->Dispatch($User, $Pw, 'TicketObject', 'TicketCreate',
TN => $TicketNumber,
Title => 'Test Ticket',
Queue => 'Raw', # or QueueID => 123,
Lock => 'unlock',
PriorityID => 2, # or PriorityID => 2,
State => 'open', # or StateID => 5,
CustomerUser => 'customer at example.com',
OwnerID => 1, # new owner
ResponsibleID => 1, # new responsible
UserID => 1,
);
print "RPC: New Ticket id is: $TicketId\n";
my $ArticleID =$RPC->Dispatch($User, $Pw, 'TicketObject', 'ArticleSend',
TicketID => $TicketId,
ArticleType => 'email-external', # email-external|email-internal|phone|fax|...
SenderType => 'agent', # agent|system|customer
From => 'root', # not required but useful
To => 'customer', # not required but useful
Cc => '', # not required but useful
ReplyTo => '', # not required
Subject => 'Test Ticket', # required
Body => "Test Body", # required
MessageID => '', # not required but useful
Charset => 'ISO-8859-15',
HistoryType => 'EmailCustomer', # EmailCustomer|Move|AddNote|PriorityUpdate|WebRequestCustomer|...
HistoryComment => 'Some free text!',
UserID => 1,
NoAgentNotify => 0, # if you don't want to send agent notifications
Type => 'text/plain',
Loop => 0, # auto reject|auto follow up|auto follow up|auto remove
);Of course this works in PHP too. The task i wanted to do is send an e-mail to a user and simultaneously create a ticket in otrs. This is the ticket creation process in otrs:
- create a new ticketnumber
- create a new ticket with that number
- attach a message (called article) to the ticket, which at once is sent out per mail
Of course the last step can vary. if you don’t want to send an e-mail for example.
# WARNING: This could destroy your OTRS-Database, please use only for testing and as a starting point for your own developments
$user = "testuser";
$pass = "testpass";
$url = "http://localhost/otrs/rpc.pl" # replace with your own otrs url
# initialising soap client
$soapclient = new SoapClient(null, array('location' => $url,
'uri' => "Core",
'trace' => 1,
'login' => $user,
'password' => $pass,
'style' => SOAP_RPC,
'use' => SOAP_ENCODED));
# creating a ticket number
$ticketnr = $soapclient->__soapCall("Dispatch", array($user, $pass, "TicketObject", "TicketCreateNumber"));
#php changes long numbers to 123E+10 notation to prevent this screwing up our ticketnumbers we convert notation
#it into normal plain numbers. but only if not string, because sometimes you have a string addition like XY123 on your
#ticket numbers
if(! is_string($ticketnr) ) $ticketnr = number_format($ticketnr,0, '.', '');
# create the new ticket
$title = "some test title";
$queue = "support";
$customerid = "1234567";
$customeruser = "John Doe";
$userid = 3; # your OTRS-Userid, varies of course in every installation
$ticketid = $soapclient->__soapCall("Dispatch", array($user, $pass, "TicketObject", "TicketCreate",
"TN", $ticketnr,
"Title", "[ Ticket#: ".$ticketnr." ] ".$title,
"Queue", $queue,
"Lock" , "unlock",
"PriorityID", 5,
"State" , "closed successful",
"CustomerId", $customerid,
"CustomerUser", $customeruser,
"OwnerID" , $userid,
"UserID", $userid
));
#create the new e-mail message, which is send out and attached to the ticket automatically
$from = "support@example.com";
$to = "janedoe@example.com";
$message = "pay your bills";
$articleid = $soapclient->__soapCall("Dispatch", array($user, $pass, "TicketObject", "ArticleSend",
"TicketID" , $ticketid,
"ArticleType" , "email-external",
"SenderType" , "agent",
"From" , $from,
"To" , $to,
"ReplyTo" , "",
"Subject" , "[ Ticket#: ".$ticketnr." ] ".$subject,
"Body" , $message,
"ContentType" , "text/plain; charset=utf-8",
"Charset" , "utf-8",
"HistoryType" , "EmailCustomer",
"HistoryComment" , "generated by OTRSInterface-Class",
"UserID" , $userid,
"NoAgentNotify" , 0,
"MimeType" , "text/plain",
"Loop" , 0
));Of course this is just a very basic example. You can really use any OTRS-Class over the SOAP-Interface, just read the API. I realized, that some of the Objects are not created correctly in rpc.pl. So just add the additonal ones you need:
use Kernel::System::Group;
use Kernel::System::Queue; # this one I added by myself
use Kernel::System::Ticket;
# ... some more code
$CommonObject{GroupObject} = Kernel::System::Group->new(%CommonObject);
$CommonObject{QueueObject} = Kernel::System::Queue->new(%CommonObject); # you have to add this too
$CommonObject{TicketObject} = Kernel::System::Ticket->new(%CommonObject);If you have still some questions, just ask.
