mysql

MySQL (/maɪ ˌɛskjuːˈɛl/ "My S-Q-L", officially, but also called /maɪ ˈsiːkwəl/ "My Sequel") is (as of March 2014) the world's second most widely used open-source relational database management system (RDBMS). [read more at http://en.wikipedia.org/wiki/MySQL]

  • Speeding Up Joomla! a little bit

    joomla_cms

    Since my server is still suffering, Ive decide today to take some actions.

    user: changes can be done on shared hosting with limited user rights.
    root. changes require a full access to the server (root access with secure shell ssh)

    I have currently per months, 160.000 visitors and 2 Millions Hits.or per day 8000 visitors and 24000 pages view.
    Server has only 1GB RAM.

    UPDATE: I found one/THE reason why my host is slowing down...SPAMMERS!

    click read more

    user: Joomla! settings
    • I switch enable gzip compression OFF. Since this is meant to reduce the usage of bandwidth, not the load on my  server. The server has to encode all the files in order to send them, which only puts additional load on your page.
    • I switch Joomla!! statistics off, as AWSTATS is providing a much better job.
    user: MySQL maintenance

    I Optimize (repair, refresh statistics) MySQL tables through MySQL admin, but It can be done through PLESK
    user: Tune Joomla!! cache

    I Increase Joomla! cache lifetime from 900seconds to 24 hours as it better reflect the way I update my site (daily)
    user: keep pages small

    • Reduce size of banner, using GIMP so they are using a web palette, most of them were shrink from 40kb to 7kb
    • I remove all un-needed space from the main templates file (index.php), an action which will help first users visitors only.
    • Attention, it is a never ending task...
    user: Hunting software bugs
    By switching the site to debug mode, I notice some nasty queries (select count(*) from), all created by my statistics module ( Content Statistics on right side), In fact Joomla!! module do not inherit from Joomla!! cache automatically. I fix value in this module since I do not want to program cache support in it right now.
    root One morecache

    I decide to install a PHP accelerator: PHPA from  http://www.php-accelerator.co.uk/
    " The ionCube PHP Accelerator is an easily installed PHP Zend engine extension that provides a PHP cache, and is capable of delivering a substantial acceleration of PHP scripts without requiring any script changes, loss of dynamic content, or other application compromises."

    Install is straightforward: just copy library into /usr/local/lib/php_accelerator_1.3.3r2.so
    and add these lines to /etc/php.ini

    ; PHP Accelerator extension
    zend_extension="/usr/local/lib/php_accelerator_1.3.3r2.so"
    phpa = on
    phpa.c0_size = 64
    phpa.cache_dir = /tmp
    phpa.c0_logging = on


    ;The shm_stats_check_period is the minimum interval between checks of the
    ;cache for expired scripts. The first server request after the interval has
    ;elapsed will trigger a scan of the cache for expired scripts, and remove
    ;any entries that it finds.
    phpa.c0_stats_check_period = 5m

    ;The shm_ttl value is the value used to set the
    ;time-to-expiry value when a script is accessed. Put another way, the shm_ttl
    ;value is the period after which an unaccessed script expires.
    phpa.c0_ttl = 12h

    ;phpa.ignore_files = ""
    ;phpa.ignore_dirs = ""

    I use that tool: HTTP Viewer to check if my page now contains a headerX-Accelerated-By:·PHPA/1.3.3r2

    Reduce surface of attack: I found my components that were not used by Joomla! (very old code and uused components . So go through all directories with FTp/SCP and remove any un-needed code....


    Review table data directly in the database...
    This is how I found 27 000 spams in my gallery (Zoom gallery)
    solution:
    • I remove all entries
    • Disallow comments operations (in Zoom gallery admin panel),
    but spammers were still able to insert comments, so I edit the file components/com_zoom/lib/image.class.php
    //add because of spammers
    header("HTTP/1.0 403 Forbidden");
    //$database->setQuery("INSERT INTO #__zoom_comments (imgid,cmtname,cmtcontent,cmtdate) VALUES ('".mysql_escape_str

    Note: I  recommend You to use also mod_evasive and mod_security  (root access needed), see aprevious article on my site



    Some links, where I borrow some ideas:


    http://www.primakoala.com/tutorials/guides/speeding_up_joomla.html
    http://forum.joomla.org/index.php/topic,50278.0.html
    http://forum.joomla.org/index.php/topic,54175.0.html
  • 4 ways to automate MAMBO database backup...

    For "Joe six pack" user to advance users... 

    1. You only want to use Mambo admin panel-EASIEST
    2. Your provider has given You a plesk panel -EASY
    3. Your provider only give You a Telnet or ssh access to the server -ADVANCE USER
    4. You want more! - VERY ADVANCE USER

    I am using the method 4, which isn't more difficult and a lot better, open the script and set the variable according to the internal documentation. Upload the file to the server (not in httpdocs under plesk), chmod the file to 700 (rwx- - - - - -) and define a daily crontab. This script is making daily, weekly, monthy backup and send me a mail with the result and a report...

     1. You only want to use Mambo admin panel-EASIEST

    Install a Mambo component: Site backup from bigAPE

    The Component provides a basic site backup feature set. The following features are currently offered:

    • Ability to backup the entire Mambo file and database system to a compressed file.
    • Ability to select which folders to include and exclude from the backup
    • Ability to download & manage archives of the Mambo file system
    • Ability to generate, download & manage archives of the Mambo mySQL database
    • Archives files are unique to the Mambo installation based on creation time and secret key
    • Ability to email the generated archive file to a specified account (BETA)
    • Backup excludes existing backup sets to conserve space
    • Backwardly compatible with 4.5.1

    Note: this plugin is only backing up your DB and files when You want it. It is a lot BETTER to use a periodical backup strategy

    2. Your provider has given You a plesk panel-EASY

    We will use crontab:

    The crontab command, found in Unix and Unix-like operating systems, is used to schedule commands to be executed periodically. It reads a series of commands from standard input and collects them into a file known also known as a "crontab" which is later read and whose instructions are carried out.

    Go in the crontab section of plesk. (I have use here Plesk 7.5.2)

    Values that are allowed to be use:

    • Minute - 0-59 or *
    • Hour - 0-23 or *
    • Day of the Month - 1-31 or *
    • Month - 1-12 or *
    • Day of the Week - 0-6 (0 is Sunday) or *

    We need at least 3 tasks

    1. A task which extract all data from the database (here starting at 12:00)

    MinuteHourDay of the MonthMonthDay of the WeekCommand
    00***/usr/local/mysql/bin/mysqldump -uXXXX -pYYYY -q -hHOST TABLE > /home/httpd/vhosts/waltercedric.com/private/dbbackups/mambobackup.txt

    -uXXXX replace XXXX with your Mambo username for mysql
    -pYYYY replace YYYY with your Mambo password for mysql
    -hHOSTis yourdomain.com or .net or whatever tld your domain has
    TABLE is the Mambo table You want to backup
    /home/httpd/vhosts/waltercedric.com/private/dbbackups/mambobackup.txt is a file in a directory outside /httpdocs chmod to 700 (or rwx------_)

    2. A task which create a zip of the resulting file (here starting at 12:05)

    MinuteHourDay of the MonthMonthDay of the WeekCommand
    50***gzip -9 -f /home/httpd/vhosts/waltercedric.com/private/dbbackups/mambobackup.txt

    Notes

    1. You can also combine task 2 and 3 in one line and without temporary file (mambobackup.txt) by defining task:
      /usr/local/mysql/bin/mysqldump
      -uXXXX -pYYYY -q -hHOST TABLE | gzip -9 > /home/httpd/vhosts/waltercedric.com/private/dbbackups/mambobackup.txt.gz
    2. File mambobackup.txt is overwritten at each execution of the task -> may be not a goo idea :-(

    3. A task which send an email of the result (here starting at 12:10)

    MinuteHourDay of the MonthMonthDay of the WeekCommand
    100***MAILTO=This email address is being protected from spambots. You need JavaScript enabled to view it. /home/httpd/vhosts/waltercedric.com/private/dbbackups/mambobackup.txt.gz

    This email address is being protected from spambots. You need JavaScript enabled to view it. is the mail recipient

     

    3. Your provider only give You a Telnet or ssh access to the server -ADVANCE USER

    Follow the guide of TVB on Mambers forums, below is

    A copy of this text...All credits to author
    Cronjobbing your mambo (or any mysql db for that matter)--a short tutorial
    This is a script that was put together by another Futurequest site owner that I have changed very slightly for my own purposes

    If you comfortable backing up via command line, then hopefully this will help you. I suspect if you are backing up this way, you already know this stuff.

    If you don't know how to backup via command line or a cron job, it's real easy to follow the instructions (I hope they are---I tried to make them easy).

    Please note this is for a *nix setup and your setup or file structure on your server may be different. I am unable to offer support for it as I only know it works for me, and hopefully it will for you as well. Use at your own risk and enjoyment.

    I'm posting it because I am always seeing a lot of questions on backing up.

    *********************
    Step 1. Using your file manager or your ftp client, create a directory called dbbackups above the www level (for example: /big/dom/xyourdomain/dbbackups) Chmod it to 700.

    .
    Step 2. using notepad or your favorite text editor create a text file with the following contents:

    Code:

    #!/bin/bash /usr/local/mysql/bin/mysqldump -uxyourdomain -pyourpassword -q -hmysql.yourdomain.tld xyourdomain >/big/dom/xyourdomain/dbbackups/mambobackup.txt



    after the -u is your username for mysql
    after the -p is your password for mysql
    after the -hmysql is yourdomain.com or .net or whatever tld your domain has

    Following that is the name of the database you are using to run mambo. If you need help remembering which database you used to run mambo, check your config.php file in your mambo or check with phpMyadmin.
    After the /big/dom/x insert your actual domain name (no tld this time)

    Name this text file mambobackup.sh and install it at the root ( /big/dom/xyourdomain/mambobackup.sh ) & Chomod it 700.

    Step 3. Telnet into your account with your favorite SSH client (I like Putty) using your ftp username and password. On the command line enter /big/dom/xyourdomain/mambobackup.sh and hit enter. Put your actual domain in place of yourdomain in the example.

    Step 4. From your file manager or your ftp client, check the contents of the folder at /big/dom/xyourdomain/dbbackups/ and inside it you should find a file named mambobackup.txt which is a copy of your mysql database used to run your mambo.

    I also use vbulletin and have the same exact script installed substituting "forum" for "mambo" wherever it occurs. Both backup to the dbbackups directory


    Automating your backup:

    Important-if you have a crontab job already running, these instructions will overwrite it. Chances are, if you have one running, and want to make backups automated, you will want to add this to your existing crontab job and then reinstall it.

    Step 1. Using your favorite text editor create a text file with the following contents.

    MAILTO=This email address is being protected from spambots. You need JavaScript enabled to view it.
    5 10 * * * /big/dom/xyourdomain/backupscript.sh


    make sure that after .sh you hit enter at least once to create a line feed. Put your actual domain name and tld in place of yourdomain.tld and put your actual domain in place of xyourdomain. Name this file cronjob.txt and install it above the www, ( /big/dom/xyourdomain/cronjob.txt )Chomod it 700

    Using your favorite telnet client, telnet into your account using your ftp username and password. On the command line enter

    crontab /big/dom/xyourdomain/cronjob.txt

    and hit enter. At 10:05 am server time, each day, a new file will overwrite your old one and you will have a daily, fresh backup made automatically for you at /big/dom/xyourdomain/forumbackup/backup.txt You can of course adjust the timing of the backup, by changing the first two entries of cronjob.txt In the example, the 5 is minutes and the 10 is hours. If you wanted the backup to be done at 11:00 PM each day, you would put 0 23 as the first two numbers. However, as it is shown will work fine.

    One consideration in making backups is server storage space or disk usage.

    Automating your backup and saving disk space by gzipping your backup file

    With 1 more file, and a change in the cronjob.txt file, it's easy to create an automated backup file in in a zipped format (which cuts the space used by about 25%. This is what you will need to add:

    1. Create a text file called zip.sh

    Inside this file, put in the following code, replacing xyourdomain with your actual information.

    Code:

    gzip -9 -f /big/dom/xyourdomain/dbbackups/mambobackup.txt



    (I have a second line of code here doing the same for our vbulletin forums, substitituting forumbackup.txt for mambobackup.txt)

    Install this file above the www ( /big/dom/xyourdomain/zip.sh ) and chomod it 700. This script will make mambobackup.txt into mambobackup.txt.gz

    2. You will now need to modify the cronjob.txt file as follows replacing yourdomain.tld and xyourdomain with your actual information.

    MAILTO=This email address is being protected from spambots. You need JavaScript enabled to view it.
    5 10 * * * /big/dom/xyourdomain/backupscript.sh
    10 10 * * * /big/dom/xyourdomain/zip.sh

    after the last .sh be sure to hit enter at least once to insert a line feed. Install the new file in place of the old one above the www ( /big/dom/xyourdomain/cronjob.txt)

    Telnet into your account from CNC using your ftp username and password and on the command line put

    crontab /big/dom/xyourdomain/cronjob.txt

    and hit enter. Replace yourdomain with your actual domain information.
    What will happen is that at 10:05 AM each day, a file called backup.txt will be created in the forumbackup folder. At 10:10 AM each day, that file will be zipped up and made into backup.txt.gz and then gzip automatically deletes backup.txt. This series of events will happen automatically each day until you cancel the crontab job.

    You may change the timing of this by changing the first two numbers on the 2nd and 3rd lines in the cronjob.txt file to suit yourself. IE to run this at 1:05 PM and finish it at 1:10 PM the crontab.txt file would be

    MAILTO=This email address is being protected from spambots. You need JavaScript enabled to view it.
    5 13 * * * /big/dom/xyourdomain/backupscript.sh
    10 13 * * * /big/dom/xyourdomain/zip.sh

    Depending upon your site's busy times, it is best to schedule a backup during slower times of useage. When the backup is being made by the mambobackup.sh script, the site or forum is locked and on a larger site, this could last for 20-30 seconds while myslq dumps the data. You won't lose any data or posts, but your site may appear to be sluggish during this time.

    Betsy

     

    4. You want more! - VERY ADVANCE USER

    You can use the open source project:AutoMySQLBackup

    A script to take daily, weekly and monthly backups of your MySQL databases using mysqldump. Features - Backup mutiple databases - Single backup file or to a seperate file for each DB - Compress backup files - Backup remote servers - E-mail logs -

    • Backup mutiple MySQL databases with one script. (Now able to backup ALL databases on a server easily. no longer need to specify each database seperately)
    • Backup all databases to a single backup file or to a seperate directory and file for each database.
    • Automatically compress the backup files to save disk space using either gzip or bzip2 compression.
    • Can backup remote MySQL servers to a central server.
    • Runs automatically using cron or can be run manually.
    • Can e-mail the backup log to any specified e-mail address instead of "root". (Great for hosted websites and databases).
    • Can email the compressed database backup files to the specified email address.
    • Can specify maximun size backup to email.
    • Can be set to run PRE and POST backup commands.
    • Choose which day of the week to run weekly backups.
  • Apache Maven BEA Weblogic 10.3 remote deployment

     apache_maven

    In this small post I will show you how to deploy automatically some artifacts of your build into bea_logo1Weblogic 10.3 by using the weblogic-maven-plugin

    This plugin will support various tasks within the Weblogic 8.1 and 9.x environment. Such tasks as deploy, undeploy,clientgen,servicegen, and appc are supported as well as many others. The plugin uses exposed API's that are subject to change but have been tested in 8.1 SP 4-6 and 9.0 - 9.2 MP3. There are two versions of the plugin to support the two environments based on differences in the JDK. The 9.x version is currently being refactored to support the standard JSR supported deployment interface

  • AutoMySQLBackup, do not save backup on your server...

    One of the most important rule is to always backup your data with multiple tools on different medium, just in case of.

    Just imagine 5 minutes that you lose everything, what you have done in the last 3 months on your homepage..ok still not crying? you loose hundred of hours of work, nightly debugging, customizations, hours of Gimp/Photoshop, lengthy and good written articles...  

    With Joomla! and now with nearly all CMS, the most important thing to backup is the database. I recommend a daily backup at least!

    And now my solution: AutoMySQLBackup

    A script to take daily, weekly and monthly backups of your MySQL databases using mysqldump.

    Features

    • Backup mutiple databases 
    • Single backup file or to a seperate file for each DB
    • Compress backup files
    • Backup remote servers
    • E-mail logs
    • Backup mutiple MySQL databases with one script. (Now able to backup ALL databases on a server easily. no longer need to specify each database seperately)
    • Backup all databases to a single backup file or to a seperate directory and file for each database.
    • Automatically compress the backup files to save disk space using either gzip or bzip2 compression.
    • Can backup remote MySQL servers to a central server.
    • Runs automatically using cron or can be run manually.
    • Can e-mail the backup log to any specified e-mail address instead of "root". (Great for hosted websites and databases).
    • Can email the compressed database backup files to the specified email address.
    • Can specify maximun size backup to email.
    • Can be set to run PRE and POST backup commands.
    • Choose which day of the week to run weekly backups.

    Just download the file, save it somewhere, and configure automysqlbackup with your editor of choice (25 first line of file). To test if everything work,  just run the file.

    Note:

    automysqlbackup  require Mutt 

    # yast2 -i mutt
    Mutt (http://www.mutt.org) is a small but very powerful text-based mail client for Unix operating systems. It is used by the script automysqlbackup (sourceforge.net/projects/automysqlbackup/) to cut mail in part (automysqlbackup do ot use mail in that case)

    automysqlbackup send daily email with database content, so data are not on host in case of crash or on ftp. You may create 2 emails account and forward the result of the backup to 2 different free email hoster (Gmail and Hotmail for example)

  • Debian Lenny how to

    offVertColor-phil2.eps  Short description of how to accomplish some specific task in Debian Lenny.

    • How to install Sun java 1.6 on Debian Lenny
    • How to install Tomcat 6.x on Debian Lenny
    • How to install MySQL on Debian Lenny

     

     

     

     

     

     

    How to install Sun java 1.6 on Debian Lenny

    And not the OpenJDK, as sometimes some applications do not work without the SUN JDK

    Edit APT sources list

    # vi /etc/apt/sources.list

    The original Sun Java 2 is available in the ‘non-free’ section of the Debian repositories. To enable this section first add the non-free repository to the apt sources file - the /etc/apt/sources.list should look like, - important parts are in bold:

    deb http://ftp.ch.debian.org/debian/ lenny main non-free
    deb-src http://ftp.ch.debian.org/debian/  lenny main non-free
    deb http://security.debian.org  lenny/updates main

    Update the list of sources

    apt-get update

    Install java

    #  apt-get install sun-java6-bin

    Just follow and select all the time

    How to install Tomcat 6 on Debian Lenny

    Create new file

    # vi /etc/apt/sources.list.d/squeeze.list

    and put inside

    # Repository for Squeeze, to get Tomcat6
    deb http://ftp.ch.debian.org/debian/ squeeze main contrib non-free
    deb-src http://ftp.ch.debian.org/debian/ squeeze main contrib non-free

    Create new file

    # vi /etc/apt/preferences

    And put inside

    Package: *
    Pin: release o=Debian,a=stable
    Pin-Priority: 990
    Package: *
    Pin: release o=Debian,a=testing
    Pin-Priority: 500
    Package: tomcat6,tomcat6-admin,tomcat6-common,libtomcat6-java,libservlet2.5-java
    Pin: release o=Debian,a=testing
    Pin-Priority: 990

    Update sources

    # apt-get update

    Upgrade

    # apt-get upgrade

    To locate the correct package name, search for it

    # apt-cache search tomcat

    Found

    tomcat6

    Install

    # apt-get install -t testing tomcat6

    You may want also to install the tomcat manager

    # apt-get install -t testing tomcat6-admin

    Create new users/roles for tomcat manager

    # vi /etc/tomcat6/tomcat-users.xml

    NOTA: Webapp are located in

    /var/lib/tomcat6/webapp

    How to install MYSQL on Debian Lenny

    Luckily MYSQL 5.0 is available in the default source list of APT.

    # apt-cache search mysql
    # apt-get install mysql-server-5.0

    Open a mysql prompt

    Grant access to root from any host, this is insecure for production machine but in case of continuous build machine, development this is sometimes very useful.

    Let root connect not only from localhost by running

    # mysql –u root –p
    mysql> use mysql;
    mysql> update user set host = '%' where user = 'root' and host='127.0.0.1'
    mysql> flush privileges;

    Bad written code (DAO) will sometimes forces you to start MYSQL using insensitive casing table. In windows it will always work as the operating system do not make any difference between lower and upper case. In Linux, either you change your DAO’s or if you can’t, use this kind of trick:

    Case insensitive table in MYSQL

    # vi /etc/mysql/conf.d/lower_case_table_names.cnf

    Put inside file

    [mysqld]
    lower_case_table_names = 1

    Restart mysql

    # /etc/init.d/mysql restart

  • Enterprise grade performances tuning with critical memory constraints

    We are working since 3 days on tuning a big application: 

    • Client server enterprise grade application,
    • Run on 2 JVM  with 4Gb (Tomcat/Application server) of RAM each!
    • Run on 2 Double core AMD 64 bits server,
    • Linux 64bits,
    • Has a lot of parallel users and > 10000 are registered
    • Use a product meta model which separate definition from implementation data.
    • Java server faces, java, ajax
     This application  is just consuming too much memory for the offline version. Our objective is to make that big application run

    • The same code as above,
    • In windows XP, 
    • IBM T40,  Intel Pentium M 1.6 GHz,  DDR266/PC2100
    • 1 JVM with 500Mb in Tomcat,
    • 1 GB of physical Ram,
    • 1 Desktop user who may run also Lotus Notes, Microsoft Office at the same time...
    There is already a lot of good resources and valuable advices on internet (Google is your friend :-)). Before digging in the code, and since the code is already productive,  we have done some tuning on component first.
    By tuning each components involved one after the other, this follow the principle: Lets do some quick win first before changing algorithm and increasing risk of breaking something....
    In order to back up each changes made with some statistics, the first step was to develop a testcase with Web Stress Tool(Commercial) but Apache JMETER (... replace with your favorite web testing tool) would have do the job

    At the OS Level

    by trying to convince the company to turn the anti virus off on some files and directory. They were scanning XHTML, javascript, XML, class files, images, so nearly everything... during EACH file access. Note the user has no windows right to alter files.


    MySQL 5(we are already using the latest 5.X branch by luck)

    By removing TCP database access and using name pipe only (+30 to +50% performances),

    By Installing MySQL Enterprise Advisor and Monitor. (You can request a free trial key here) and looking at what the advisor recommend. Attention this tool has been developed for monitoring servers, some recommendations are simply not always usable. In our case we are constrained by the memory, remember less than 500Mb, so we did not blindly follow advices. Basic stuff were done, like adding indexes (were it make sense to avoid full tables scan and reduce slow queries), increasing  buffers,

    By switching to myISAM (multi threaded with table locking) instead of innoDB (multi threaded with row locking), and also avoiding other storage engine to run with different algorithm to run in parallel..

    MyISAM is the default storage engine for the MySQL relational database management system. It is based on the older ISAM code but has many useful extensions. In recent MySQL versions, the InnoDB engine has widely started to replace MyISAM due to its support for transactions, referential integrity constraints, and higher concurrency.Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. MySQL uses a .frm file to store the definition of the table, but this file is not a part of the MyISAM engine, but instead is a part of the server. The data file has a .MYD (MYData) extension. The index file has a .MYI (MYIndex) extension. [WikiPedia]

    InnoDB is a storage engine for MySQL, included as standard in all current binaries distributed by MySQL AB. Its main enhancement over other storage engines available for use with MySQL is ACID-compliant transaction support, similar to PostgreSQL, along with declarative referential integrity (foreign key support). InnoDB became a product of Oracle Corporation after their acquisition ofInnobase Oy, in October 2005. The software is dual licensed. It is distributed under the GNU General Public License, but can also be licensed to parties wishing to combine InnoDB in proprietary software. [WikiPedia]

    What are the differences, and you may want also to use myISAM for mono user applications...
    1. InnoDB recovers from a crash or other unexpected shutdown by replaying its logs. MyISAM must fully scan and repair or rebuild any indexes or possibly tables which had been updated but not fully flushed to disk. Since the InnoDB approach is approximately fixed time while the MyISAM time grows with the size of the data files, InnoDB offers greater perceived availability and reliability as database sizes grow.
    2. MyISAM relies on the operating system for caching reads and writes to the data rows while InnoDB does this within the engine itself, combining the row caches with the index caches. Dirty (changed) database pages are not immediately sent to the operating system to be written by InnoDB, which can make it substantially faster than MyISAM in some situations.
    3. InnoDB stores data rows physically in primary key order while MyISAM typically stores them mostly in the order in which they are added. This corresponds to the MS SQL Server feature of “Clustered Indexes” and the Oracle feature known as "index organized tables." When the primary key is selected to match the needs of common queries this can give a substantial performance benefit. For example, customer bank records might be grouped by customer in InnoDB but by transaction date with MyISAM, so InnoDB would likely require fewer disk seeks and less RAM to retrieve and cache a customer account history. On the other hand, inserting data in orders that differ substantially from primary key (PK) order will presumably require that InnoDB do a lot of reordering of data in order to get it into PK order. This places InnoDB at a slight disadvantage in that it does not permit insertion order based table structuring.
    4. InnoDB currently does not provide the compression and terse row formats provided by MyISAM, so both the disk and cache RAM required may be larger. A lower overhead format is available for MySQL 5.0, reducing overhead by about 20% and use of page compression is planned for a future version.
    5. When operating in fully ACID-compliant modes, InnoDB must do a flush to disk at least once per transaction, though it will combine flushes for inserts from multiple connections. For typical hard drives or arrays, this will impose a limit of about 200 update transactions per second. If you require higher transaction rates, disk controllers with write caching and battery backup will be required in order to maintain transactional integrity. InnoDB also offers several modes which reduce this effect, naturally leading to a loss of transactional integrity. MyISAM has none of this overhead, but only because it does not support transactions. [WikiPedia]
    For us the speed of myISAM is clearly balancing the drawback for a desktop applications.

    JSF tuning

    Obvious settings here:, JSF is lacking more fine tuning settings. Serialization is occurring during the model life cycle and consume memory and CPU. We may dig deeply later.
    • javax.faces.STATE_SAVING_METHOD to server
    • org.apache.myfaces.COMPRESS_STATE_IN_SESSIONto true since memory is the biggest constraint for us
    • org.apache.myfaces.NUMBER_OF_VIEWS_IN_SESSION to 0
    • facelets.BUFFER_SIZE to 8192

    Tomcat tuning

    Nothing big can be done here...For me Tomcat is really missing a dynamic web application loader:  Tomcat is simply installing all applications found in /webapps at startup even if they are not used. They are never remove from memory or serialized to disk. Tomcat 4.1 seems to have a memory footprint of 22 Mb, going to the latest Tomcat 6.0 is a too big changes for us now, but we might reconsider it in the future. Removing java library which are not use from WEB-INF/libby trial and error can save some precious Bytes through as it is pretty common when you use frameworks to have jar not desired. For example: junit.jar, jdbc drivers, jms.jar,...Moving common lib to shared/lib may also help remove duplicate jars from webapps class loader and memory.


    JVM tuning

    Java 1.5 and java 1.6 have made a lot of progress, and the JIT compiler found in JAVA 1.5/1.6 is getting  more and more aggressive...The basic rule is to turn the GC JVM log on (by adding -Xloggc:<file> [-XX:+PrintGCDetails]) and analyze it offline with a tool like GCViewer (free). The JIT is doing a pretty good job as the application run more and more faster with the time, but it is just a feeling ;-)
    By analyzing the GC logs we were able to optimize and avoid big mis configurations mistakes, one more time a lot of articles and books are available on how to tune a JVM. Sadly java has no advisor at the moment or is not using genetic algorithms to tune itself...It remains a dream for now.

    By using an empiric approach, which means:
    1 changing JVM parameters -> running test cases ->deciding if we give CPU away or minimize RAM usage -> go back to 1

    We come down to the following  exotic parameters (Xms and Xmx are not of any help since it is really depending on your application and how memory is managed internally)

     -XX:+AggressiveOpts -XX:-UseConcMarkSweepGC

    By the way I use them in Eclipse + JDK 1.6 since months. This page A Collection of JVM Options compiled by: Joseph D. Mocker (Sun Microsystems, Inc.) has been of a great help during this stage.

    Still not enough, we were forced to profile the java code and make some big changes....
  • How to Solve a Problem with Joomla! jos_session

    joomla_cms

    mysql

    I was not seeing this error since at least 3 years:

    DB function failed with error number 1016

    Can't open file: 'jos_session.MYI' (errno: 145) SQL=INSERT INTO `jos_session`
    ( `session_id`,`time`,`username`,`gid`,`guest`,`client_id` )
    VALUES ( '4bc998b10d92bf4107976d0edacdbb26','1246725653','','0','1','0' )

    It happen sometimes and cause are unknown. While it is quite disturbing to have no more access to Joomla! frontend or backend, the fix is quite easy. You must repair this table jos_session, If you have access to


    PhpMyAdmin

    Require an access to the Plesk/CPA panel.

    Go to your Joomla database, select the table jos_session (tick the checkbox) and choose "Repair table" from the drop-down you find at the bottom of the list of tables.

    Using PHP

    Maybe the most easiest for non techies people

    I found this useful script http://www.cafewebmaster.com/repair-all-mysql-databases-tables-php, upload it at root of your host (in /httpdocs) under repair.php for example and run it, you’ll see the following:

    repair.table.in.php

    Tick repair, provide your login and password for database,

    repair.table.in.php2

    Remove the scripts after use, or add a die(‘not for use, edit script first’); at the top of it just in case it may be use to inject something in your database!

    Shell access

    For the most advanced of us, which understand Unix and have shell access

    # mysql –udblogin -p

    mysql>  use joom;

    mysql> REPAIR TABLE jos_session;
    +-------------------------+--------+----------+------------------------------------+
    | Table | Op | Msg_type | Msg_text |
    +-------------------------+--------+----------+------------------------------------+
    | joom.jos_session | repair | warning | Number of rows changed from 0 to 2 |
    | joom.jos_session | repair | status | OK |
    +-------------------------+--------+----------+------------------------------------+

    Finding Joomla! login and password

    These informations can be found in httpdocs/configuration.php under $user and $password as seen below

    ..

    var $user = 'hkuin47Gdfver';
    var $db = 'joom';
    var $dbprefix = 'dfrtCsdef_';
    var $mailer = 'mail';
    var $fromname = 'Happpp';
    var $sendmail = '/usr/sbin/sendmail';
    var $smtpauth = '0';
    var $smtpsecure = 'none';
    var $smtpport = '25';
    var $smtpuser = '';
    var $smtppass = '';
    var $smtphost = 'localhost';
    var $MetaAuthor = '1';
    var $MetaTitle = '1';
    var $lifetime = '15';
    var $session_handler = 'database';
    var $password = afedfhrr546fdhcfcv;

    ..

    And if the repair failed?

    Run the following sql to drop the table and make it new. Copy it into the PhpMyAdmin SQL field.

    DROP table if exist `jos_session`

    CREATE TABLE `jos_session` (
      `username` varchar(150) default '',
      `time` varchar(14) default '',
      `session_id` varchar(200) NOT NULL default '0',
      `guest` tinyint(4) default '1',
      `userid` int(11) default '0',
      `usertype` varchar(50) default '',
      `gid` tinyint(3) unsigned NOT NULL default '0',
      `client_id` tinyint(3) unsigned NOT NULL default '0',
      `data` longtext,
      PRIMARY KEY  (`session_id`(64)),
      KEY `whosonline` (`guest`,`usertype`),
      KEY `userid` (`userid`),
      KEY `time` (`time`)
    ) TYPE=MyISAM CHARACTER SET `utf8`;

  • Huge milestones: mysql 5.0 and Wine 0.9

    MySQL is a multithreaded, multi-user, SQL (Structured Query Language) Database Management System (DBMS) with an estimated six million installations. MySQL AB makes MySQL available as open source software / free software under the GNU General Public License (GPL), but they also sell it under traditional commercial licensing arrangements for cases where the intended use is incompatible with use of the GPL. [Wikipedia]

    MySQl is powering a lot of open source application all over the world (for example my homepage with Joomla CMS, phpnuke, postnuke...), a lot of internet provider let you define at least 1 to 5 database.
    MySQL 5.0 is the most significant product upgrade in the company's ten-year history, and the list of new features is impressive and futhfill a lot of requirementsfrom small to middle complexity applications
     

    Wineis a project to allow a PC running a Unix-like operating system and the X Window System to run x86 programs for Microsoft Windows. Alternately, those wishing to port a Windows application to a Unix-like system can compile it against the Wine libraries. The name was derived from the recursive acronym "Wine Is Not an Emulator" (it implements a compatibility layer), although some have used the unofficial expansion "Windows Emulator". Although the forms "WINE" and "wine" of the name are sometimes used, the developers have agreed on the now correct "Wine". [Wikipedia]

    Note I have personally already tested following major tools (which I can not live without) under my SuSE box: XMLspy, Beyond compare, HP49 emulator without any pain using a daily build of WINE(beginn of 2005)

    This is release 0.9 ofWine, a free implementation of Windows on Unix. After 12 years of development, this release marks the beginning of the beta testing phase. Everybody is encouraged to try it; while there are still bugs, most applications are expected to at least install and do something useful. from release 0.9 anouncement

  • Joomla CMS GPL development news

    In order to better test the code I am giving You, I have now locally running...

    1. DEV: developing new PHP code, connected to CVS at Joomlaforge
    2. TEST to test component installation and code in a clean Joomla installation. If everything work, a binary version will be created at Joomla Forge in developer section.
    3. PROD is a copy of my production site (You are currently reading :-) )
    I have also set 2 machines with each time these 3 stages:
    1. WindowsXP - eclipse 3.1 - phpeclipse - mysql5 - php 4.1 - apache for DEV, TEST and PROD
    2. SuSE 10.0  - eclipse 3.1 - phpeclipse - mysql5 - php 4.1 - apache for DEV, TEST and PROD

    so i will be able to better test my components, You can still contact me and offer translations or beta testing....

    You want to have or see new functionnalities in one of these components?
    use my WIKI Order and priorities has to be discussed, a poll/vote can be open in a few days here as well.

    You have found bugs?

    Please, do not contact me and ask for support on my homepage, ALL Projects are now hosted at Joomlaforge and have each one their Tracker
    Project Report Bug here
    http://developer.joomla.org/sf/projects/com_securityimages   click on Tracker icon
    http://developer.joomla.org/sf/projects/com_hashcash   click on Tracker icon
    http://developer.joomla.org/sf/projects/com_bayesianspamfiltering   click on Tracker icon
    http://developer.joomla.org/sf/projects/com_log4php   click on Tracker icon
    http://developer.joomla.org/sf/projects/com_akocommentplus   click on Tracker icon
    http://developer.joomla.org/sf/projects/com_akobookplus   click on Tracker icon
  • Live Backups of MySQL Using Replication

    I am using Mambo now since august 2003 together with a mysql database (the most popular open source database). Right now I am doing backup on a weekly basis manually using the plesk panel of my provider. I found this article interesting even if it does not resolve my problem of backup.  As soon as I find something interesting, I will publish it (plesk script, CGI tool...). Back to the article, it is in fact more an example of database replication for an enterprise:

    One of the difficulties with a large and active MySQL database is making clean backups without having to bring the server down. Otherwise, a backup may slow down the system and there may be inconsistency with data, since related tables may be changed while another is being backed up. Taking the server down will ensure consistency of data, but it means interruption of service to users. Sometimes this is necessary and unavoidable, but daily server outages for backing up data may be unacceptable. A simple alternative method to ensure reliable backups without having to shut down the server daily is to set up replication for MySQL. Read more HERE at onLamp.com the open source web plattform of O'Reilly

  • Mysql database backup in Linux Crontab

    Here is the easiest way to run a daily backup of your database using linux crontab. Thanks to our everyday increasing mailbox size (Thanks Gmail) and their nature to be quite safe for saving document, let’s use them to store the full backup of all our Mysql database!

    Requirements

    • Having a shell access to your linux box
    • Creating a user with limited mysql rights: SELECT and LOCK_TABLES is enough, For example a user backup_user with a password ChhdeqyqUzd75687fOnmYar
    • Installing Mpack: Tools for encoding/decoding MIME messages. Mpack and munpack are utilities for encoding and decoding (respectively) binary files in MIME (Multipurpose Internet Mail Extensions) format mail messages. For compatibility with older forms of transferring binary files, the munpack program can also decode messages in split-uuencoded format.

    Edit your crontab

    crontab -e

    and put inside on one line the following

    0 1 * * * /usr/bin/mysqldump -ubackup_user –pChhdeqyqUzd75687fOnmYar yourdb |
    gzip > /database_`date +'%m-%d-%Y'`.sql.gz ;
    mpack -s "Databases backup"
    -c application/gzip /database_`date +'%m-%d-%Y'`.sql.gz This email address is being protected from spambots. You need JavaScript enabled to view it.

    You can replace the word yourdb with your tablename or --all-databases to dump all database. With the above line a backup will be run at 1AM every day and sent in your mailbox.

  • MYSQL tuning with mysqlreport

    mysql.logo I found this interesting tool (beside tuning-primer.sh) while trying to optimizing my server setting for Joomla!

    mysqlreport

    mysqlreport makes an easy-to-read report of important MySQL status values. Unlike SHOW STATUS which simply dumps over 100 values to screen in one long list, mysqlreport interprets, formats, and then nicely presents the values in report readable by humans. Numerous example reports are available at the mysqlreport web page.
    The benefit of mysqlreport is that it allows you to very quickly see a wide array of performance indicators for your MySQL server which would otherwise need to be calculated by hand from all the various SHOW STATUS values. For example, the Index Read Ratio is an important value but it is not present in SHOW STATUS; it is an inferred value (the ratio of Key_reads to Key_read_requests).

    Grab it at http://hackmysql.com/mysqlreport

    How to run it (more options), it require PERL to run.

    # ./mysqlreport --user xxxxx--password xxxxxx| more

    Running mysqlreport against my host, gave the following results:

    • very good table lock 0.02%
    • very good read ratio 99.93%
    • good query cache, but could be reduce to 40Mb to avoid wasting memory resource

    if you need something more professional and can afford it, you can try Mysql Enterprise (free for 30 days, enough to tune any small server bottleneck)

     

     

    MySQL 5.0.41-log         uptime 4 10:56:4       Fri Jan  2 22:45:47 2009

    __ Key _________________________________________________________________
    Buffer used     2.44M of   5.00M  %Used:  48.75
      Current       2.97M            %Usage:  59.38
    Write hit      47.41%
    Read hit       99.93%

    __ Questions ___________________________________________________________
    Total           4.07M    10.6/s
      QC Hits       1.93M     5.0/s  %Total:  47.35
      DMS         973.13k     2.5/s           23.89
      Com_        936.64k     2.4/s           23.00
      COM_QUIT    249.35k     0.6/s            6.12
      -Unknown     14.78k     0.0/s            0.36
    Slow 5 s      123.77k     0.3/s            3.04  %DMS:  12.72  Log:  ON
    DMS           973.13k     2.5/s           23.89
      SELECT      589.04k     1.5/s           14.46         60.53
      UPDATE      135.53k     0.4/s            3.33         13.93
      INSERT      125.80k     0.3/s            3.09         12.93
      DELETE      119.91k     0.3/s            2.94         12.32
      REPLACE       2.85k     0.0/s            0.07          0.29
    Com_          936.64k     2.4/s           23.00
      set_option  411.63k     1.1/s           10.11
      change_db   230.65k     0.6/s            5.66
      show_tables  68.89k     0.2/s            1.69

    __ SELECT and Sort _____________________________________________________
    Scan          205.15k     0.5/s %SELECT:  34.83
    Range          27.27k     0.1/s            4.63
    Full join      13.73k     0.0/s            2.33
    Range check         8     0.0/s            0.00
    Full rng join   4.46k     0.0/s            0.76
    Sort scan      76.29k     0.2/s
    Sort range    110.20k     0.3/s
    Sort mrg pass       0       0/s

    __ Query Cache _________________________________________________________
    Memory usage   25.86M of  70.00M  %Used:  36.94
    Block Fragmnt  16.52%

    Hits            1.93M     5.0/s
    Inserts       533.75k     1.4/s
    Insrt:Prune   13.29:1     1.3/s
    Hit:Insert     3.61:1

    __ Table Locks _________________________________________________________
    Waited            269     0.0/s  %Total:   0.02
    Immediate       1.71M     4.5/s

    __ Tables ______________________________________________________________
    Open             1482 of 2000    %Cache:  74.10
    Opened         44.50k     0.1/s

    __ Connections _________________________________________________________
    Max used           16 of   25      %Max:  64.00
    Total         250.45k     0.7/s

    __ Created Temp ________________________________________________________
    Disk table     65.75k     0.2/s
    Table         198.32k     0.5/s    Size: 120.0M
    File                5     0.0/s

    __ Threads _____________________________________________________________
    Running             1 of    3
    Cached             13 of   32      %Hit:  99.99
    Created            16     0.0/s
    Slow                0       0/s

    __ Aborted _____________________________________________________________
    Clients         2.20k     0.0/s
    Connects        3.41k     0.0/s

    __ Bytes _______________________________________________________________
    Sent            1.48G    3.8k/s
    Received      757.33M    2.0k/s

    __ InnoDB Buffer Pool __________________________________________________
    Usage           7.98M of   8.00M  %Used:  99.80
    Read hit       99.80%

    Pages
      Free              1            %Total:   0.20
      Data            510                     99.61 %Drty:   0.00
      Misc              1                      0.20
      Latched           0                      0.00
    Reads           1.03M     2.7/s
      From file     2.10k     0.0/s            0.20
      Ahead Rnd        79     0.0/s
      Ahead Sql         6     0.0/s
    Writes         45.01k     0.1/s
    Flushes        12.42k     0.0/s
    Wait Free           0       0/s

    __ InnoDB Lock _________________________________________________________
    Waits               0       0/s
    Current             0
    Time acquiring
      Total             0 ms
      Average           0 ms
      Max               0 ms

    __ InnoDB Data, Pages, Rows ____________________________________________
    Data
      Reads         2.30k     0.0/s
      Writes       23.18k     0.1/s
      fsync        14.15k     0.0/s
      Pending
        Reads           0
        Writes          0
        fsync           0

    Pages
      Created           5     0.0/s
      Read          2.60k     0.0/s
      Written      12.42k     0.0/s

    Rows
      Deleted         843     0.0/s
      Inserted      2.07k     0.0/s
      Read        107.49k     0.3/s
      Updated       2.83k     0.0/s

  • Optimizing MySQL jdbc under windows using named pipe

    According to this MySQL page here, you can win 30 to 50% more performances using MySQL jdbc named pipe!

    Named pipes only work when connecting to a MySQL server on the same physical machine as the one the JDBC driver is being used on.
    In simple performance tests, it appears that named pipe access is between 30%-50% fasterthan the standard TCP/IP access.


    As default, when you install mySQL on windows using the installer, TCP IP is the default option. The question remains why nobody seems
    to use named pipe, or has write any articles on internet about it. Lorenz and me were trying nearly 2 hours to make this damn things work.
    We googled on internet, finding nothing, e try and try until we finally succeed. That is the reason why I write this article now.

    We were testing successfuly MySQL 5.0 using named pipe, and what an increase in speed! 50% in the case of this big project
    First it may be a good idea to use the latest JConnector driver from MySQL

    MySQL Connector/J is a native Java driver that converts JDBC (Java Database Connectivity) calls into the network protocol used by
    the MySQL database. It lets developers working with the Java programming language easily build programs and applets that interact
    with MySQL and connect all corporate data, even in a heterogeneous environment. MySQL Connector/J is a Type IV JDBC driver and
    has a complete JDBC feature set that supports the capabilities of MySQL.

    Download Binaries & Source

    Just put the file mysql-connector-java-5.0.7-bin.jar in your classpath. You'll have to sligthly alter the my.ini file as following:

    [mysqld]
    skip-networking
    enable-named-pipe
    socket=mysql.sock


    Add these 3 keys in [mysqld]section of my.ini and restart MySQL. The first key switch the TCP-IP port off, so you wont be able
    now to connect to port 3306. The 2 others remaining just activate MySQL named pipe.

    Verify that everything has work correctly by firing MySQL Query Browser and connect to your database.
    (see details of connection below)
    mysql query browser with named pipe

     For the most intrepid of You of if wou want to add named pipe capabilities to previous saved connections, just can also use the menu "tools" - "manage connections" and under the tab "advanced parameters", just add these 2 new keys

    NAMED_PIPE    value  Yes
    SOCKET_PATH   value   mysql.sock

    We are nearly finished, all we have to do is to alter now the JDBC url, and this is where we fight against Windowstm till we find the path to the mysql.sock file handle.
    For the unpatient of you, I deliver here the solution:

    jdbc:mysql:///database
    ?socketFactory=com.mysql.jdbc.NamedPipeSocketFactory
    &namedPipePath=\\\\.\\Pipe\\mysql.sock


    database being the database/schema name

    socketFactory=com.mysql.jdbc.NamedPipeSocketFactory  JConnector also supports access to MySQL via named pipes on Windows NT/2000/XP using
    the
    NamedPipeSocketFactory as a plugin-socket factory via thesocketFactory property. If you don't use anamedPipePath property, the default
    of '\\.\pipe\MySQL' will be used. If you use the
    NamedPipeSocketFactory, the hostname and port number values in the JDBC url will be ignored.
    You can enable this feature using:   socketFactory=com.mysql.jdbc.NamedPipeSocketFactory

    namedPipePath=\\\\.\\Pipe\\mysql.sock The path to the file socket. Notice how strange the path is looking like under Windowstm (escaping  \ in java is normal).
    Under linux we would have write /var/log/mysql.sock and forget everything. In Windowstm  You really have no chance to find it until  you use
    FileMon(a SysInternals tool)

    FileMonmonitors and displays file system activity on a system in real-time. Its advanced capabilities make it a powerful tool for exploring the way
    Windows works, seeing how applications use the files and DLLs, or tracking down problems in system or application file configurations. Filemon's
    timestamping feature will show you precisely when every open, read, write or delete, happens, and its status column tells you the outcome.
    FileMon
    is so easy to use that you'll be an expert within minutes. It begins monitoring when you start it, and its output window can be saved to a file for off-line
    viewing. It has full search capability, and if you find that you're getting information overload, simply set up one or more filters.
    Download it HERE

    You can use FileMon to filter file by name, search for mysql* and you'll see that strange url. Note the documentation give some advice about this url
    (more or less)  the default of '\\.\pipe\MySQL', would have work if we have name the file MySQL and not mysql.sock

    But wait there is more to learn, MySQL is supporting a wide range of parameters when you open the connection. Just read this page, a lot of settings may also speed your application even more.
  • Optimizing MySQL settings for Joomla

    I publish here some of my server settings in the hope that it will also help others...

    Server Setup

    AMD64 1 Gb RAM, Linux OSS 10.0, 7 Joomla instances (one being waltercedric.com with 250'000 unique visitors per months), 1 simple machine forums, 3 gallery2 install

    All MySQL tables are myISAM (table locking instead of row loacking in innodb, myIsam make sense as ther is more read then insert) 

    # vi  in /etc/my.cnf

    Below the diff command between a standard MySQL install

    # diff my.cnf my-beforeOptimizations.cnf
    30c30
    < key_buffer = 50M
    ---
    > key_buffer = 16M
    32,33c32,33
    < table_cache = 1500
    < sort_buffer_size = 4M
    ---
    > table_cache = 64
    > sort_buffer_size = 512K
    35,46c35,37
    < read_buffer_size = 4M
    < read_rnd_buffer_size = 1024K
    < myisam_sort_buffer_size = 64M
    < join_buffer_size = 4M
    < thread_cache_size = 128
    < wait_timeout = 14400
    < connect_timeout = 10
    < max_connect_errors = 10
    < query_cache_limit = 2M
    < query_cache_size = 128M
    < query_cache_type = 1
    < thread_concurrency=4
    ---
    > read_buffer_size = 256K
    > read_rnd_buffer_size = 512K
    > myisam_sort_buffer_size = 8M
    163,166c154,157
    < key_buffer = 64M
    < sort_buffer = 64M
    < read_buffer = 16M
    < write_buffer = 16M
    ---
    > key_buffer = 20M
    > sort_buffer_size = 20M
    > read_buffer = 2M
    > write_buffer = 2M

  • Optimizing mysql with tuning-primer.sh

    Digging into mySQL settings can be time consuming, like with any other component. Most of the time, and just by changing a few settings, you can expect a performance increase. The problem is to change what, and  to which value. This is where tuning-primer.sh help you:

    Get this script, http://forge.mysql.com/projects/view.php?id=44 upload it, unzip it, and install it in your /etc folder. Then run it from the command line by entering ./path-to-file/tuning-primer.sh

    MySQL Server must run a few days or weeks, or it wont be be safe to follow these recommendations.

    To find out more information on how each of these runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

    Here is an example of  tuning-primer.sh output

    SLOW QUERIES
    Current long_query_time = 5 sec.
    You have 2856 out of 4725688 that take longer than 5 sec. to complete
    The slow query log is enabled.
    Your long_query_time seems to be fine

    WORKER THREADS

    Current thread_cache_size = 128
    Current threads_cached = 55
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine

    MAX CONNECTIONS

    Current max_connections = 100
    Current threads_connected = 15
    Historic max_used_connections = 55
    The number of used connections is 55% of the configured maximum.
    Your max_connections variable seems to be fine.

    MEMORY USAGE
    Max Memory Ever Allocated : 305 M
    Configured Max Per-thread Buffers : 1017 M
    Configured Max Global Buffers : 143 M
    Configured Max Memory Limit : 1 G
    Total System Memory : 2.99 G
    Max memory limit seem to be within acceptable norms

    KEY BUFFER
    Current MyISAM index space = 4 M
    Current key_buffer_size = 5 M
    Key cache miss rate is 1 : 3740
    Key buffer fill ratio = 35.00 %
    Your key_buffer_size seems to be too high.
    Perhaps you can use these resources elsewhere


    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 128 M
    Current query_cache_used = 27 M
    Current query_cach_limit = 2 M
    Current Query cache fill ratio = 21.13 %
    Your query_cache_size seems to be too high.
    Perhaps you can use these resources elsewhere
    MySQL won't cache query results that are larger than query_cache_limit in size


    SORT OPERATIONS
    Current sort_buffer_size = 4 M
    Current record/read_rnd_buffer_size = 1020 K
    Sort buffer seems to be fine

    JOINS
    Current join_buffer_size = 1.00 M
    You have had 7065 queries where a join could not use an index properly
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    If you are unable to optimize your queries you may want to increase your
    join_buffer_size to accommodate larger joins in one pass.


    Note! This script will still suggest raising the join_buffer_size when
    ANY joins not using indexes are found.

     

  • Percona XtraDB Storage Engine, scalling betten than innodb

    From http://www.mysqlperformanceblog.com

    Percona XtraDB includes all of InnoDB’s ACID-compliant design and advanced MVCC architecture, and adds features, more tunability, more metrics, more scalability on many cores, and better memory usage. We choose features and fixes based on customer requests and on our best judgment of real-world needs. We have not included all the InnoDB patches available. For example Google’s well-known InnoDB patch set is omitted (at least for now).

    The first version of our new storage engine is 1.0.2-1, which is forked from InnoDB-plugin-1.0.2. Percona XtraDB is released under GPL v2, as is InnoDB-plugin base source code. Percona XtraDB is released only under the GPL v2 with no dual-licensing, and commercial support is available from Percona.

    So what’s new in the engine? Here is a list of new features and enhancements:

    • INFORMATION_SCHEMA.XTRADB_ENHANCEMENTS. This table contains information about the differences between the Percona XtraDB and the same version number of standard InnoDB, so you can always learn what your engine can do. documentation
    • Improvements to SHOW INNODB STATUS. We’ve added more memory information and lock information, and fixed problems with lock information. documentation
    • Improvements to InnoDB IO. Improvements of InnoDB IO subsystem, such as multiple read and write threads, read-ahead control, control io capacity and adaptive checkpointing. documentation
    • InnoDB RW-lock fixes Improvements to scalability for systems with 8+ cores. documentation
    • Buffer pool fixes Improvements of buffer_pool scalability. documentation
    • innodb_buffer_pool_pages Information about content of buffer_pool pages. documentation

    That's a thing I will try for my new Internet server ;-) let's see if Joomla! appreciate and MySQL span better on a quad core server.