new database with user in mysql console

this will create a new database on the server with the new user having all rights granted on that db.


CREATE USER 'uname'@'localhost' IDENTIFIED  BY 'pass';

GRANT  USAGE ON *.* TO 'uname'@'localhost' IDENTIFIED  BY 'pass' WITH  MAX_QUERIES_PER_HOUR 0  MAX_CONNECTIONS_PER_HOUR  0  MAX_UPDATES_PER_HOUR  0  MAX_USER_CONNECTIONS  0 ;

CREATE  DATABASE  IF  NOT  EXISTS 'uname' ;

GRANT  ALL  PRIVILEGES  ON 'uname'.*  TO 'uname'@'localhost';

if there´s a problem when creating the database,  do not use the ‘ ‘ signs around the db name.

just to avoid phpmyadmin for this little task ;) i didn´t like to look that up everytime.

mysql replace http://typo3/http://

remember when the links out of the rte looked like http://typo3/http://yourlink.tld/ ? well, as i still prod at links looking like this, i’d rather post the relieving sql statement here. it’s simple but handy. the replace function just makes backend work even faster.

UPDATE tt_content SET bodytext = REPLACE(bodytext, 'http://typo3/http://', 'http://');

web server stress testing

a nice tool to go frenzy on a webserver is siege. it simulates an editable amount of concurrent users that pick on the web server for a variable time. having found this tool, stress testing some TYPO3 instances on apache(s) i rule was the inevidable next step.

now first of all this is not my daily business, but it’s tending to become to, so if you have corrections, suggestions and comments, your lines are appreciated below. the post you read is more like a small tutorial for those who want to know how performant the own webserver (i.e. apache) does. i like siege because of the easy settings, but i also run the better known apache benchmark when it comes down to a single page.

preparing the battle

get all urls from the system, it’s willing to accept and put them in a text file line by line. my TYPO3 (4.3.0a3) runs realurl, so the trick is easy.

  • make sure, all the pages have been visited (# wget -r does the job)
  • ask mysql or phpmyadmin to provide following data
    SELECT spurl FROM `tx_realurl_urldecodecache` WHERE 1=1 LIMIT 0 , 4500

    sure, one could narrow the links down by using WHERE rootpage_id = [domain1_id] but as the whole server is to be tested, give me what i can get.

  • grab the result into a file and prefix every line with the base url defined in config.baseUrl.
    the lines should like like clickable http requests. exactly that is siege going to do.
    “http://baseurl.com/spurls/from/decodecache/” <- the way i did it
  • if you’re into MySQL profiling, a tool like jet profiler can make your day. innoDB ftw! ;)
  • clear all caches in TYPO3 (and read stuff written in nc_staticfile manual:)

Clear cache for all domains in tree

Here you can decide what to do when clearing the frontend cache. By default all static html files will be deleted. Usually this is fine. Most installations of TYPO3 serve a single domain. If multiple domains are served from the same TYPO3 tree you might want to leave the cache for the other domains intact. If you uncheck the ‘clearCacheForAllDomains’ checkbox, only the html files are removed that are in the same domain as which you are logged into the backend.

  • after the siege battle you will start thinking about using a static file cache… if you haven’t already
  • machine A is going to battle machine B, so B would serve http://baseurl.com/ and A has siege installed and a file with the lines built above, named realurls.txt.
  • now you run a command like
    me@machine1 [/home/me] siege -c 60 -t 5M -i -b -f realurls.txt > /dev/null

    -c 60 # makes siege simulate 60 concurrent web users, i think that’s a number
    -t 5M # the battle will last around 600 seconds
    -b # benchmarking mode, leave no delay between requests -> make the box steam!
    -i -f realurls.txt # internet activity mode, the links in realurls.txt are randomly clicked, rather than sequentially
    > /dev/null # the output is going to the built-in black hole, better than flaming my console. you may prefer logging to a file.csv which contains info on the single requests (useful for finding the slow pages)

  • hit and see
    ** SIEGE 2.69
    ** Preparing 60 concurrent users for battle.
    The server is now under siege…

i was prepared for stuff but that hit me down (hopefully your first values are better)… after a while results are shown.

benchmark results

Response time: 4.15 secs
Transaction rate: 12.06 trans/sec
Throughput: 0.28 MB/sec

wtf! the machine got spikes in the load like 25! this is not what i expected. i was aiming for about a mean response time of shorter than a second and, #whatdoiknow, about a hundred successfull transactions per second?

now it’s definitely time to optimize some settings. right now (two days later), the same box drives like

Response time: 0.13 secs
Transaction rate: 507.40 trans/sec
Throughput: 2.78 MB/sec

load below 4. not bad, hu? most tweaks (apache, mysql, TYPO3)  i did to achieve this will be written in one of the next posts, as i’m not finished yet ^ #botmustwork

(you see result of iteration 1 and i think 8. the siege settings haven’t changed, of course cache revalidation was disabled)

conclusion

if you’ve got a web site up and running, you’d better be looking on performance in the real world. is the page digg-proof and does it stand a large amount of requests over time, even when cache was cleared? siege can help in doing a forecast.

after the site gone public, you will try to avoid becoming host of dead ends.

0.27 MB/sec

piwik shows General error: 126

last week there was everything working fine. no changes made. today, stuff is not running fine. piwik in version 0.4.1 does not show useful content anymore. if you ever get to see all your widgets showing the General error 126″ you can start over with mysqlrepair.

General error: 126 Incorrect key file for table ‘/tmp/#sql_6d3c23.MYI’; try to repair it

SQLSTATE[HY000]

Yes sir, we’re going to repair the database totally. To do so, we fire up

mysqlrepair -B piwik -force -p

which will show a lot of OK rows. option -A is for all databases, so if your mysql server is in production and you’ve got a bunch of dbs on it hot, you propably consider using -B option to point to the single piwik database. I had to retry this with different options but now the things looks okay.

so today’s view is fixed, but since the problem existed for around three days, two days of data are corrupted. so i open config/global.ini.php and set the debugsters to 1.

[Debug]
; the archiving process will always be triggered, even if the archive has already been computed
always_archive_data_period = 1;
always_archive_data_day = 1;

; if set to 1, all the SQL queries will be recorded by the profiler
enable_sql_profiler = 1;

after having reprocessed the complete archive, which does take quite a while, there is still a report of an incorrect key file. But now with nice SQL profiling., d’oh.

<?xml version="1.0" encoding="utf-8" ?>
<result>
<error message="SQLSTATE[HY000]: General error: 126 Incorrect key file for table '/tmp/#sql_694e23.MYI'; try to repair it" />
</result>

solution

the problem was caused by a /tmp partition defined too small (16mb on a guest). now as i raised the tmpfs size the system works normal again. in vserver landscapes you can search a while before finding the corresponding fstab in /etc/vservers/yourvserver/fstab on the host. All this can be found in the vserver faq section.

Thanks to @schommr for the tip.

Best of it all, your data won’t be gone if you have a problem like this. It just can’t be processed to show correctly. My concerns about several days loss of data were flushed away when piwik’s archive.sh ran through in about twenty minutes. go piwik!

search and replace mysql

simple to do if you know how it’s done. and instead of searching the web, i’ll stick to my own note from now on :)

UPDATE tablename SET fieldname = REPLACE(fieldname,'searchpattern','replacement');

quite useful for batch changes i.e. paths to images that have changed… ;)

it is of importance to not use ” on tablename or the fieldname.

to narrow it down to a special page (like in Backend jobs for TYPO3)

UPDATE tt_content SET bodytext = REPLACE(bodytext, 'http://bigbabou.org', 'https://bigbabou.org') WHERE pid=2501;

that will change every link from http:// to https:// in any content element on page 2501. you could break it down even further to only change CType=’HTML’ (speaking, content elements of type HTML).
easy to apply but nice to have it to copy from…

sphinx on debian lenny

sphinx is a fast and open source full-text search engine for SQL databases, or simply SQL Phrase Index. To test it, I just started installing it on a server running debian lenny.

You’ll need to have gcc and make installed on this computer, as well as the package g++, which isn’t found in apt-cache search or aptitude. But it just will be installed when using

# apt-get install g++

Another simple thing for debian must be changed when following the sphinx installation manual. As I want to connect to a MySQL database, I must provide the mysql developer package, obviously mysql-devel. But on debian lenny its name is libmysql15client-dev.

After having installed these packages

# ./configure
# make
# make install

should just successfully run through.