Mysql / Mariadb performance test

I have two different Linux servers, and wanted to test if one where faster than the other. I used mysqlslap together with the “time” command in a bash shell.

# time mysqlslap –user=root –password=cat /root/mysql –host=localhost concurrency=50 –iterations=100 –number-int-cols=5 –number-char-cols=20 –auto-generate-sql –verbose

In this way I could get out different values from the server which I could compare.

Average number of seconds to run all queries: 0.007 seconds
Minimum number of seconds to run all queries: 0.006 seconds
Maximum number of seconds to run all queries: 0.024 seconds
Number of clients running queries: 1
Average number of queries per client: 0

real 0m9.390s
user 0m0.085s
sys 0m0.165s



Idle in transaction – DELETE waiting

We have a Linux server (Redhat Enterprise 6) running a multi-site installation of Mediawiki. In total, we have today 120 unique wiki’s, each having its one Postgresql 9.1 database.

Suddenly the server stopped responding. In Apache webserver, the total number concurrent apache processes went up to Apache’s max-client setting in httpd.conf, and people couldn’t log in or see any wiki-pages.

In the apache log, we did not see anything special. But, when we started to check which processes were running during a full stop on the server with the Unix command ps, we could see entries like:

A search on Internet found that also others experienced this, and the solution to our problem we found here:



We implemented a fix to our problem by adding a bash script to /etc/crontab, running each fifth minute.

The script will first check if there are more then three (3) processes with the description “idle in transaction”.

If so, then the pkill command will stop (“kill”) the oldest one.

The script runs every fifth minute. Here is our entry in the /etc/crontab file.

[code lang=”bash”]
*/5 * * * * root /usr/share/scripts/fix-transactions/


Authenticate Postgresql client agains backend ldap

We have a Postgresql server open to many different people at our organization. In order to add a ldap authentication you can add these 3 lines to your current pg_hba.conf file:

where the first one will allow users that access the server from localhost (either from a shell or connections). The second line is allowing a computer from the following IP adresse: 129.177.XXX.YYY.

Finally the last line will use ldap backend for local connections. That means a Linux user that logged in with SSH, will still be validated agains the ldap server, regardless of local password.

Remember this is only authentication, so you have to add the username to Postgresql database anyway (hint: use createuser command).

If you want to allow a user to connect remotely over web, remember to use SSL in the webserver, HTTPS, so that the password is not transmittet un-encrypted.

Note also:  This is also only LDAP, not LDAPS connections. So data going from the Postgresql server to the ldap is not encrypted. In short: Keep the distance between the postgresql server and the LDAP server short, or try to implement LDAPS, which I did, where I failed, so far…

Open Journal System missing email logs

I upgraded our Open Journal Systems from 2.2.2 to 2.4.2 and afterwards we saw the email logs were gone. This was related to:

My solution was this:
Established a new Postgresql server in a virtual enviroment (vmware, test-machine), and import the old and the new (upgraded) database.

ojs_old: was the original 2.2.2 database
ojs_new: was the upgraded 2.4.2 one

In order to fix the assoc_id numbers, I used this bash script:

The output from this script is sent to STDOUT. I redirected it to another script, and ran the script on our Production server.