items tagged with database

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)

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% faster than 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

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

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.