Command Center: MySQL

Showing posts with label MySQL. Show all posts

Monday, April 15, 2013

MySQL 4.1+ using old authentication

Category: MySQL — SkyHi @ Monday, April 15, 2013

When I was working with XAMPP in Ubuntu and asked write PHP script to connect to remote MySQL server which is using PASSWORD hash function to save the password for user, and I found following error.

Warning: mysql_connect() [function.mysql-connect]: Premature end of data (mysqlnd_wireprotocol.c:554) in path/to/the/file/where/connection/script/is/written/

Warning: mysql_connect() [function.mysql-connect]: OK packet 1 bytes shorter than expected in path/to/the/file/where/connection/script/is/written/

Warning: mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password'). This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnf file in path/to/the/file/where/connection/script/is/written/

As you will see, the core issue here is that MySQL can have passwords with hashes stored in the old 16-character format, which is not supported by PHP 5.3′s new mysqlnd library.

Since I couldn’t find a good solution with a quick Google, here is how I solved this without having to downgrade PHP or MySQL (as some of the solutions suggested):

1. Change MySQL to NOT to use old_passwords

It seems that even MySQL 5.x versions still default to the old password hashes. You need to change this in “my.cnf” (e.g. /etc/my.cnf): remove or comment out the line that says

old_passwords = 1

Restart MySQL. If you don’t, MySQL will keep using the old password format, which will mean that you cannot upgrade the passwords using the builtin PASSWORD() hashing function. You can test this by running:

mysql> SELECT Length(PASSWORD('xyz'));
+-------------------------+
| Length(PASSWORD('xyz')) |
+-------------------------+
|                      16 |
+-------------------------+
1 row in set (0.00 sec)

The old password hashes are 16 characters, the new ones are 41 characters.

2. Change the format of all the passwords in the database to the new format

Connect to the database, and run the following query:

mysql> SELECT user,  Length(`Password`) FROM `mysql`.`user`;

This will show you which passwords are in the old format, ex:

+----------+--------------------+
| user     | Length(`Password`) |
+----------+--------------------+
| root     |                 41 |
| root     |                 16 |
| user2    |                 16 |
| user2    |                 16 |
+----------+--------------------+

Notice here that each user can have multiple rows (one for each different host specification).

To update the password for each user, run the following:

UPDATE mysql.user SET Password = PASSWORD('password') WHERE user = 'username';

Finally, flush privileges:

FLUSH PRIVILEGES;

REFERENCE
http://lampsailesh.blogspot.com/2011_05_01_archive.html#5294498109303285481

Wednesday, June 20, 2012

SQL Delete Records within a specific Range

Category: MySQL — SkyHi @ Wednesday, June 20, 2012

delete from Table where id between 79 and 296

REFERENCES
http://stackoverflow.com/questions/8225036/sql-delete-records-within-a-specific-range

Thursday, March 8, 2012

Table mysql.servers doesn’t exist: Problem adding a database user in plesk Or restarting mysql

Category: MySQL — SkyHi @ Thursday, March 08, 2012

Update: If “mysql_fix_privilege_tables” command does not exist, look at Section 2.

You may receive a “Table ‘mysql.servers’ doesn’t exist” error message while adding a database user in Plesk OR while restarting the Mysql service. The complete error message look like:

Error: Connection to the database server has failed: 
Table 'mysql.servers' doesn't exist

Can't open and lock privilege tables: 
Table 'mysql.servers' doesn't exist

The problem mostly occurs when Mysql is upgraded which introduce newer version of tables and the default Mysql database is not aware of these changes. The “mysql_fix_privilege_tables” command is use to update the Mysql database with the latest contents of the newer version and to fix the privileges of the database users as well.

Section 1:

To fix the issue, SSH to your server and execute:

On a plain Linux OR Linux/cPanel server:

# mysql_fix_privilege_tables --user=root\
 --password=mysqlpasswordhere --verbose

On a Linux/Plesk server:

# mysql_fix_privilege_tables --user=admin\
 --password=`cat /etc/psa/.psa.shadow` --verbose

where, –verbose will display the detailed output.

Section 2:

mysql_upgrade command have superseded mysql_fix_privilege_tables. mysql_fix_privilege_tables is an older script that previously was used to upgrade the system tables in the Mysql database .

To fix the issue, you need to replace mysql_fix_privilege_tables with mysql_upgradein the above stated commands, i.e.

On a plain Linux OR Linux/cPanel server:

# mysql_upgrade --user=root --password=mysqlpasswordhere --verbose

On a Linux/Plesk server:

# mysql_upgrade --user=admin\
  --password=`cat /etc/psa/.psa.shadow` --verbose

REFERENCES
http://linuxhostingsupport.net/blog/problem-adding-a-database-user-in-plesk-or-restarting-mysql-table-mysql-servers-doesnt-exist
http://myeasylinux.wordpress.com/2010/07/20/table-mysql-servers/
http://stackoverflow.com/questions/3653021/error-table-mysql-servers-doesnt-exist

Wednesday, December 7, 2011

Backup all databases nightly w/ mysqldump

Category: Backup, MySQL, scripts — SkyHi @ Wednesday, December 07, 2011

So, I want to take a shell script and be able to put it on any machine - and have it backup the databases on that machine using mysqldump.. and put them each separately into a backup directory.. here's what I came up with.

Can you make it better?

#!/bin/bash
 
DB_BACKUP="/backups/mysql_backup/`date +%Y-%m-%d`"
DB_USER="root"
DB_PASSWD="secretttt"
HN=`hostname | awk -F. '{print $1}'`
 
# Create the backup directory
mkdir -p $DB_BACKUP
 
# Remove backups older than 10 days
find /backups/mysql_backup/ -maxdepth 1 -type d -mtime +10 -exec rm -rf {} \;
 
# Option 1: Backup each database on the system using a root username and password
for db in $(mysql --user=$DB_USER --password=$DB_PASSWD -e 'show databases' -s --skip-column-names|grep -vi information_schema);
do mysqldump --user=$DB_USER --password=$DB_PASSWD --opt $db | gzip > "$DB_BACKUP/mysqldump-$HN-$db-$(date +%Y-%m-%d).gz";
done
 
# Option 2: If you aren't using a root password then comment out option 1 and use this
# for db in $(mysql -e 'show databases' -s --skip-column-names|grep -vi information_schema);
# do mysqldump --opt $db | gzip > "$DB_BACKUP/mysqldump-$HN-$db-$(date +%Y-%m-%d).gz";
# done
 
# Make it so only root can read the backup files
chmod -R 600 $DB_BACKUP

If you use this, throw this text into something like /usr/local/bin/mysql_backup.sh and since it has mysql's root password in it, make sure that you chmod 700 to it so no one else can read it. Then just call it from cron like:

30 3 * * * /usr/local/bin/mysql_backup.sh

BTW, a simpler way to grab all of them is to use the --all-databases flag in the mysqldump command.. but it doesn't make nice separate files for you..

REFERENCES
http://www.linuxforum.com/content.php/147-Backup-all-databases-nightly-w-mysqldump

Thursday, November 10, 2011

MySQL server has gone away max_allowed_packet.

Category: MySQL — SkyHi @ Thursday, November 10, 2011

Most MySQL users have tried getting this rather cryptic error message: “MySQL server has gone away”. The MySQL documentation describes lots of possible reasons for this here:http://dev.mysql.com/doc/refman/5.1/en/gone-away.html
However this page is of little help for most users, I think. Dozens of reasons are listed, but except for the trivial ones (like physical connection was lost, the MySQL server or the machine where it runs has crashed etc.) there are a few reasons for this that are very common in our experience and a lot of those mentioned are not.

Here we will discuss one situation that to our experience happens very frequently for people working across multiple servers. The situation is that if a client sends a SQL-statement longer than the server max_allowed_packet setting, the server will simply disconnect the client. Next query from the same client instance will find that the ‘MySQL server has gone away’. At least it is like that with recent server versions.

1)
But the documentation at
http://dev.mysql.com/doc/refman/5.1/en/error-messages-client.html
.. also lists another client error:
Error: 2020 (CR_NET_PACKET_TOO_LARGE) Message: Got packet bigger than ‘max_allowed_packet’ bytes
along with
Error: 2006 (CR_SERVER_GONE_ERROR): Message: MySQL server has gone away.

Actually I have not seen the ‘got packet bigger ..’ error myself for many years. Not since MySQL 3.23 or 4.0. I am uncertain if a recent server will sometimes still return ‘got packet bigger’ or not or if also this error message itself has ‘gone away’. If the ‘got packet bigger’ message is still relevant with recent servers it would be nice to have it specified under what conditions it occurs and when only ‘gone away’ will. If this error mesage is now ‘historical’ it should at least be removed from documentation or it should be mentioned that the error no. is reserved for this message – but not used anymore. But it would of course be much preferable to have the ‘got packet bigger’ error returned if that is the problem. It tells what the problem is – “MySQL server has gone away” does not tell anything specific. So ‘got packet bigger’ is a *much* better message than ‘gone away’. Also ‘got packet bigger’ is listed among client errors and not server errors what I would expect. So maybe some problem with my understanding of things here?
Does anybody have any idea about if and why ‘got packet bigger’ now effectively seems to have ‘gone away’ too?
And most important: why disconnect the client? There are reconnect options of course, but it does not really help here. After a reconnect and executing the same query things just repeat themselves.
2)
Basically I never understood why MySQL stick with the default 1M setting for [mysqld] when it is 16M for [mysqldump] in configuration ‘templates’ shipped with the MySQL server (I have tried to ‘hint’ them several times over the last 3-4 years). Obviously they realize that 1M is often too little for backup/restore since they use a larger setting for mysqldump. However users use all other sorts of tools for backup: other script-based tools running on the server, third-party (and MySQL) GUI clients, web-based tools (hosting control panels, phpMyAdmin), backup/restore routines shipping with or built-in applications etc. Often users do not have access to run mysqldump at all on hosted servers (at least not if they are shared servers). Further often Sysadmins are unwilling to change configuration settings and users are left with the option to generate SINGLE INSERTS – with horrible restore performance as a consequence – to ensure cross-server exports/imports (and still it fails with a well-grown MEDIUMBLOB). I deliberately use the term ‘exports/imports’ and not ‘backup/restore’ because it also applies to various tools that can connect to two or more servers at a time and copy data using various techniques without actually generating a file.

The max_allowed_packet problem as described here has been a big problem for us over time. I do not think MySQL fully realises the importance of the problem – mostly because our tools and the tools/clients shipped with the server respectively are used primarily by different segments of users (with some significant overlapping of course). We handle this problem now 100% in SQLyog (we generate the largest BULK INSERTS possible up to 16M everywhere when transferring data from one server to another with all the methods available) but we cannot prevent user - if he wants to use BULK INSERTS - to generate a SQL-DUMP on one server that will not import another because BULK INSERTS are too large. We will of course only be able to handle it if we are connected to both servers.

3)
One solution would be to allow for max_allowed_packet as a SESSION variable. After a long time of unclarity about this – refer to http://bugs.mysql.com/bug.php?id=22891 andhttp://bugs.mysql.com/bug.php?id=32223
- it is now clear that it is not and will not be possible to override the GLOBAL setting for the SESSION. I regret this! It would be very nice to be able to “SET max_allowed_packet ..” on top of a SQL-script for instance.

4)
And actually – and most basically – I also do not really understand why a max_allowed_packet setting is required at all – except that it makes sense of course that a server admin should be able to restrict not-well-behaving users in bombing the server with statements containing 1 GB large WHERE-clauses! But then we are not talking about 1M but rather something like 16-64-100M as a critical threshold, I think.

Also I am not sure if the reason is that the setting is used to allocate a fixed-size memory buffer for handling the query string or if it is related to handling network packages or whatever. I just wondered for quite some time if such restriction could not be avoided and whether this implementation is a deliberate choice for some reason or rather some consequence of coding techniques used currently. I would like to get rid of it!

================================================================

max_allowed_packet= 64M
wait_timeout= 6000

solution worked for me as well – thanks a lot

REFERENCES
http://www.webyog.com/blog/2009/08/10/mysql-server-has-gone-away-part-1-max_allowed_packet/
http://bogdan.org.ua/2008/12/25/how-to-fix-mysql-server-has-gone-away-error-2006.html
http://dev.mysql.com/doc/refman/5.1/en/gone-away.html
http://chrisjean.com/2011/01/21/fix-amember-mysql-error-mysql-server-has-gone-away/
http://stackoverflow.com/questions/1644432/mysql-server-has-gone-away-in-exactly-60-seconds
http://robsnotebook.com/wordpress-mysql-gone-away

Monday, October 17, 2011

Reduce High CPU usage overload problem caused by MySql

Category: MySQL, Performance Tune — SkyHi @ Monday, October 17, 2011

In the recent years, many of us were facing the problem of "database overload" and "High CPU usage overload" problem. After a benchmark testing about this issue, I reach at point where I found, there were too many database connections, unnecessary queries execution and unnecessary HTTP request. So, Today I am really interested to share my little knowledge with you.

I feel this problem when I have made penny auction. If you are made swoopo/madbid clone or similar type of penny/Live auction then I'm sure you guys used Ajax or jQuery function which you made to call in a second because there must be display recent updated data like winner name, his bids and auction countdown timer without any page refresh. If you are in product details page then there must be display recent 10 bids history without any refresh.

In the similar type of web development, what I have realized that there were too many database connections, unnecessary query execution and unnecessary HTTP request. These problems were creating the database overload problem and then CPU usage goes to 100%. Why?

Because there might be call PHP page in each second through JavaScript to update recent bidding information. Now suppose if there are 1000 users in your website, 1000 database connection was created and if you have made 5 queries for getting updated bidding information, then 1000*5= 5000 queries are execute in a second which is big problem for any server and due to these causes High CPU usage overload problem, database overload problem and too many database connection problem occurs. These things happen when users open one page of your website if they are open 2 or 3 or more then you imagine how many connections will open and queries will execute in a second.

Now your question is how I have resolved these problems?

Well! First of all we have to completely remove database connection by using file handling and accordingly this we need to maintain some functions.

Second, I have taken current updated bidder information and bid history from database when user place a bid. So, there are only one connection happen not multiple. Due to the bidbutler or autobid causes also I have face overload problem so I have remove this function from frontend and handle it from cron scheduler.

Now the main query is how to reduce High CPU usage overload problem cause by MySql?

Here are some of the following points which will help you to reduce High CUP usage overload or Database overload problem.

Establishes a persistent connection: Persistent connection (mysql_pconnect) gives two major benefits than MySql connection. First, when connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection. Second, the connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect()).
Make a database connection and closed connection: Make database connection at the top of the page and closed connection in the bottom of the page.
Change database type from MyISAM to Innodb: we also need to change MyISAM to Innodb because InnoDB supports some newer features: Transactions, row-level locking, foreign keys. InnoDB is for high volume, high performance.
Create Temporary Tables: The best place to use temporary tables is when we need to pull a bunch of data from multiple tables. In the penny auction, you need when you display bid history where we required to display only last 10 bidder history and latest updated winner information like auction price, winner name and auction end date.
Select only those elements from database which are required: Don't select all the values from all table, only select that element which we required.
Optimize database query:Optimizing your queries can help them run more efficiently, which can save a significant amount of time.
MySQL Query Cache: MySQL Query Cache is simply speed up your query performance. As we know, speed is always the most important element in developing a website especially for those high traffic database driven website. You can try to turn on query cache to speed up query. Whenever query cache is enable, it will cache the query in memory and boost query performance.
Create indexes (Single or combine) based on requirement: Indexes are used for reading the data from the table with comparatively faster execution time.
Distribute Cron scheduler load: Never handle entire function from one cron scheduler. Try to distribute in multiple scheduler.
Check your server configuration: Due to the low server configuration or distributed server also you have face high CPU usage problem or database overloaded problem. (check mysqltuner.pl query_cache_size and tmp_table_size haven't been set)

REFERENCES
http://www.sks.com.np/article/4/reduce-high-cpu-usage-overload-problem-caused-by-mysql.html
http://www.howtoforge.com/tuning-mysql-performance-with-mysqltuner
http://cruncht.com/89/drupal-lamp-server-tuning/

Wednesday, September 14, 2011

Flush and Reset MySQL Binary Logs

Category: MySQL — SkyHi @ Wednesday, September 14, 2011

I had an issue with free disk space (or, more appropriately, a lack there of) on my server a while back. After some investigation, I discovered that my MySQL databases had ballooned in size to nearly 10 GB. Actually, figuring out that the /var/lib/mysql directory was taking up so much space wasn't that hard, but understanding why and what to do about it took a while (yes, I'm sometimes slow about such things).

It turns out I had two issues. The first is that MySQL configuration, by default, maintains binary logs. These logs "contain all statements that update data or potentially could have updated it (for example, a DELETE which matched no rows). Statements are stored in the form of 'events' that describe the modifications. The binary log also contains information about how long each statement took that updated data."[1] This is fine and all, but (again by default) these log files are never deleted. There is a (configurable) max file size for each log, but MySQL simply rolls over to a new log when it's reached. Additionally, MySQL rolls over to a new log file on every (re)start. After a few months of operation, it's easy to see how this can take up a lot of space, and my server had been running for nearly four years.

Complicating matters somewhat was the fact that the default name of the binary logs changed at some point (and, according to the current docs, now appears to have changed back. As a result, I have several gigabytes worth of logs using the old naming convention, as well as several gigabytes worth of logs using the newer convention. Yay.

Like I said, recognizing that MySQL was taking up a lot of space is not hard, but I'm paranoid about my data and didn't want to risk losing anything. So, I kept putting it off until I was literally running out of space on a near daily basis. At that point I began doing research and figured out all of the above information. I also found a quick an easy way to fix the problem.

Note: This is meant for a standalone MySQL server. I'm not sure how it may affect replication, so please do not follow these instructions on a replicated server without additional research.

First of all, the binary logs typically reside in /var/lib/mysql/. You can check to see how much space they're currently taking up with this one-liner: du -hcs /var/lib/mysql/*bin.* | tail -n 1. If it's more than a few hundred megabytes, you may want to continue on.

Next, check to see if you were affected by the name switch like I was. This is unlikely unless you've been running the server for at least a year or so, but it definitely doesn't hurt to check. Look at all *bin.* files. If they're all named the same, such as mysqld-bin.000001, then you're fine. If you see some with a different name, such as both mysqld-bin.000001 and hostname-bin.000001, then you have an outdated set of logs doing nothing but taking up space. Look at the timestamps of the .index file for each set. One should be very recent (such as today), the other not. Once you've identified the older set, go ahead and delete all of them; they're no longer being used.

Finally, for the current set, login to MySQL as an admin user (eg., mysql -u root -p). You'll want to run the following two commands:
mysql> FLUSH LOGS;
mysql> RESET MASTER;

That's it. Depending on the size and number of your logs, those two commands may take a while to run, but the end result is that any unsaved transactions will be flushed to the database, all older logs will be dropped, and the log index will be reset to 1. In my case, these two steps dropped my from 9.6 GB down to about 5 MB. Good stuff.

Of course, this is simply a workaround to the problem, not a proper solution. What I'd really like to do is either automate this process so that I don't have to worry about the logs getting out of control, or even better configure MySQL to automatically flush its own logs after some period of time or it reaches a certain total file size. I haven't found any way to do this just yet, though I admittedly haven't looked too hard. I'd appreciate any recommendations, though.

[1] http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

REFERENCES
http://legroom.net/2008/06/29/flush-and-reset-mysql-binary-logs

MySQL Bin Files Eating Lots of Disk Space

Category: MySQL — SkyHi @ Wednesday, September 14, 2011

Q. I get a large amount of bin files in the MySQL data directory called "server-bin.n" or mysql-bin.00000n, where n is a number that increments. What is MySQL Binary Log? How do I stop these files being created?

A. Usually /var/lib/mysql stores the binary log files. The binary log contains all statements that update data or potentially could have updated it. For example, a DELETE or UPDATE which matched no rows. Statements are stored in the form of events that describe the modifications. The binary log also contains information about how long each statement took that updated data.

The purpose of MySQL Binary Log

The binary log has two important purposes:

Data Recovery : It may be used for data recovery operations. After a backup file has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.
High availability / replication : The binary log is used on master replication servers as a record of the statements to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.

Disable MySQL binlogging

If you are not replicating, you can disable binlogging by changing your my.ini or my.cnf file. Open your my.ini or /etc/my.cnf (/etc/mysql/my.cnf), enter:
# vi /etc/my.cnf

Find a line that reads "log_bin" and remove or comment it as follows:

#log_bin                        = /var/log/mysql/mysql-bin.log

You also need to remove or comment following lines:

#expire_logs_days        = 10
#max_binlog_size         = 100M

Close and save the file. Finally, restart mysql server:
# service mysql restart

Purge Master Logs

If you ARE replicating, then you need to periodically RESET MASTER or PURGE MASTER LOGS to clear out the old logs as those files are necessary for the proper operation of replication. Use following command to purge master logs:

$ mysql -u root -p 'MyPassword' -e "PURGE BINARY LOGS TO 'mysql-bin.03';"

$ mysql -u root -p 'MyPassword' -e "PURGE BINARY LOGS BEFORE '2008-12-15 10:06:06';"

Tuesday, August 30, 2011

mysql last update file

Category: bash, find, MySQL — SkyHi @ Tuesday, August 30, 2011

#find . -iname "*.MYD" -mtime -720

Find Files By Access, Modification Date / Time Under Linux or UNIX do not remember where I saved pdf and text files under Linux. I have downloaded files from the Internet a few months ago. How do I find my pdf or text files?
You need to use the find command. Each file has three time stamps, which record the last time that certain operations were performed on the file:

[a] access (read the file's contents) - atime
[b] change the status (modify the file or its attributes) - ctime
[c] modify (change the file's contents) - mtime
You can search for files whose time stamps are within a certain age range, or compare them to other time stamps.
You can use -mtime option. It returns list of file if the file was last accessed N*24 hours ago. For example to find file in last 2 months (60 days) you need to use -mtime +60 option.

-mtime +60 means you are looking for a file modified 60 days ago.
-mtime -60 means less than 60 days.
-mtime 60 If you skip + or - it means exactly 60 days.

So to find text files that were last modified 60 days ago, use
$ find /home/you -iname "*.txt" -mtime -60 -print

Display content of file on screen that were last modified 60 days ago, use
$ find /home/you -iname "*.txt" -mtime -60 -exec cat {} \;

Count total number of files using wc command
$ find /home/you -iname "*.txt" -mtime -60 | wc -l

You can also use access time to find out pdf files. Following command will print the list of all pdf file that were accessed in last 60 days:
$ find /home/you -iname "*.pdf" -atime -60 -type -f

List all mp3s that were accessed exactly 10 days ago:
$ find /home/you -iname "*.mp3" -atime 10 -type -f

There is also an option called -daystart. It measure times from the beginning of today rather than from 24 hours ago. So, to list the all mp3s in your home directory that were accessed yesterday, type the command
$ find /home/you -iname "*.mp3" -daystart -type f -mtime 1

Where,

-type f - Only search for files and not directories

-daystart option

The -daystart option is used to measure time from the beginning of the current day instead of 24 hours ago. Find out all perl (*.pl) file modified yesterday, enter:

find /nas/projects/mgmt/scripts/perl -mtime 1 -daystart -iname "*.pl"

You can also list perl files that were modified 8-10 days ago, enter:
To list all of the files in your home directory tree that were modified from two to four days ago, type:

find /nas/projects/mgmt/scripts/perl -mtime 8 -mtime -10 -daystart -iname "*.pl"

-newer option

To find files in the /nas/images directory tree that are newer than the file /tmp/foo file, enter:

find /etc -newer /tmp/foo

You can use the touch command to set date timestamp you would like to search for, and then use -newer option as follows

touch --date "2010-01-05" /tmp/foo

# Find files newer than 2010/Jan/05, in /data/images
find /data/images -newer /tmp/foo

Read the man page of find command for more information:
man find

REFERENCES
http://www.cyberciti.biz/faq/howto-finding-files-by-date/

Thursday, August 25, 2011

MySQL configuration file variables Explained

Category: MySQL — SkyHi @ Thursday, August 25, 2011

This MySQL server has been running for 0 days, 2 hours, 9 minutes and 22 seconds. It started up on Aug 25, 2011 at 09:34 AM.

SQL query SSL Handler Query cache Threads Binary log Temporary data Delayed inserts Key cache Joins Replication Sorting Tables Transaction coordinator

Server traffic: These tables show the network traffic statistics of this MySQL server since its startup.

Traffic ¹		ø per hour
Received	15 KiB	7,284 B
Sent	43 KiB	20 KiB
Total	59 KiB	27 KiB

Connections		ø per hour	%
max. concurrent connections	2	---	---
Failed attempts	0	0.00	0.00%
Aborted	0	0.00	0.00%
Total	87	40.35	100.00%

Query statistics: Since its startup, 284 queries have been sent to the server.

Total	ø per hour	ø per minute	ø per second
284	131.72	2.20	0.04

Query type		ø per hour	%
select	158	73.280	80.20%
set option	15	6.957	7.61%
show tables	8	3.710	4.06%
show binlogs	3	1.391	1.52%
change db	3	1.391	1.52%
show variables	3	1.391	1.52%

Query type		ø per hour	%
admin commands	2	0.928	1.02%
show slave status	1	0.464	0.51%
show master status	1	0.464	0.51%
show databases	1	0.464	0.51%
flush	1	0.464	0.51%
show status	1	0.464	0.51%

Query type		ø per hour	%
show charsets	1	0.464	0.51%
show collations	1	0.464	0.51%
show grants	1	0.464	0.51%
show plugins	1	0.464	0.51%

SQL query
Variable	Value	Description
Flush_commands	1	The number of executed FLUSH statements.
Last_query_cost	0	The total cost of the last compiled query as computed by the query optimizer. Useful for comparing the cost of different query plans for the same query. The default value of 0 means that no query has been compiled yet.
Slow_queries	0	The number of queries that have taken more than long_query_time seconds.

SSL
Variable	Value	Description
Ssl_accept_renegotiates	0
Ssl_accepts	0
Ssl_callback_cache_hits	0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects	0
Ssl_connect_renegotiates	0
Ssl_ctx_verify_depth	0
Ssl_ctx_verify_mode	0
Ssl_default_timeout	0
Ssl_finished_accepts	0
Ssl_finished_connects	0
Ssl_session_cache_hits	0
Ssl_session_cache_misses	0
Ssl_session_cache_mode	NONE
Ssl_session_cache_overflows	0
Ssl_session_cache_size	0
Ssl_session_cache_timeouts	0
Ssl_sessions_reused	0
Ssl_used_session_cache_entries	0
Ssl_verify_depth	0
Ssl_verify_mode	0
Ssl_version

Handler
Variable	Value	Description
Handler_commit	0	The number of internal COMMIT statements.
Handler_delete	0	The number of times a row was deleted from a table.
Handler_discover	0	The MySQL server can ask the NDB Cluster storage engine if it knows about a table with a given name. This is called discovery. Handler_discover indicates the number of time tables have been discovered.
Handler_prepare	0
Handler_read_first	4	The number of times the first entry was read from an index. If this is high, it suggests that the server is doing a lot of full index scans; for example, SELECT col1 FROM foo, assuming that col1 is indexed.
Handler_read_key	0	The number of requests to read a row based on a key. If this is high, it is a good indication that your queries and tables are properly indexed.
Handler_read_next	0	The number of requests to read the next row in key order. This is incremented if you are querying an index column with a range constraint or if you are doing an index scan.
Handler_read_prev	0	The number of requests to read the previous row in key order. This read method is mainly used to optimize ORDER BY ... DESC.
Handler_read_rnd	0	The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.
Handler_read_rnd_next	1,878	The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
Handler_rollback	0	The number of internal ROLLBACK statements.
Handler_savepoint	0
Handler_savepoint_rollback	0
Handler_update	0	The number of requests to update a row in a table.
Handler_write	1,819	The number of requests to insert a row in a table.

Query cache
Variable	Value	Description
Flush query cache
Qcache_free_blocks	1	The number of free memory blocks in query cache.
Qcache_free_memory	17 M	The amount of free memory for query cache.
Qcache_hits	0	The number of cache hits.
Qcache_inserts	0	The number of queries added to the cache.
Qcache_lowmem_prunes	0	The number of queries that have been removed from the cache to free up memory for caching new queries. This information can help you tune the query cache size. The query cache uses a least recently used (LRU) strategy to decide which queries to remove from the cache.
Qcache_not_cached	158	The number of non-cached queries (not cachable, or not cached due to the query_cache_type setting).
Qcache_queries_in_cache	0	The number of queries registered in the cache.
Qcache_total_blocks	1	The total number of blocks in the query cache.

Threads
Variable	Value	Description
Show processes
Slow_launch_threads	0	The number of threads that have taken more than slow_launch_time seconds to create.
Threads_cached	1	The number of threads in the thread cache. The cache hit rate can be calculated as Threads_created/Connections. If this value is red you should raise your thread_cache_size.
Threads_connected	1	The number of currently open connections.
Threads_created	2	The number of threads created to handle connections. If Threads_created is big, you may want to increase the thread_cache_size value. (Normally this doesn't give a notable performance improvement if you have a good thread implementation.)
Threads_running	1	The number of threads that are not sleeping.
Threads_cache_hitrate_%	97.70 %

Binary log
Variable	Value	Description

Binlog_cache_disk_use	0	The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction.
Binlog_cache_use	0	The number of transactions that used the temporary binary log cache.

Temporary data
Variable	Value	Description
Created_tmp_disk_tables	49	The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.
Created_tmp_files	0	How many temporary files mysqld has created.
Created_tmp_tables	165	The number of in-memory temporary tables created automatically by the server while executing statements.

Delayed inserts
Variable	Value	Description
Delayed_errors	0	The number of rows written with INSERT DELAYED for which some error occurred (probably duplicate key).
Delayed_insert_threads	0	The number of INSERT DELAYED handler threads in use. Every different table on which one uses INSERT DELAYED gets its own thread.
Delayed_writes	0	The number of INSERT DELAYED rows written.
Not_flushed_delayed_rows	0	The number of rows waiting to be written in INSERT DELAYED queues.

Key cache
Variable	Value	Description

Key_blocks_not_flushed	0	The number of key blocks in the key cache that have changed but haven't yet been flushed to disk. It used to be known as Not_flushed_key_blocks.
Key_blocks_unused	13 k	The number of unused blocks in the key cache. You can use this value to determine how much of the key cache is in use.
Key_blocks_used	0	The number of used blocks in the key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time.
Key_read_requests	0	The number of requests to read a key block from the cache.
Key_reads	0	The number of physical reads of a key block from disk. If Key_reads is big, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests.
Key_write_requests	0	The number of requests to write a key block to the cache.
Key_writes	0	The number of physical writes of a key block to disk.
Key_buffer_fraction_%	18.24 %

Joins
Variable	Value	Description
Select_full_join	0	The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
Select_full_range_join	0	The number of joins that used a range search on a reference table.
Select_range	0	The number of joins that used ranges on the first table. (It's normally not critical even if this is big.)
Select_range_check	0	The number of joins without keys that check for key usage after each row. (If this is not 0, you should carefully check the indexes of your tables.)
Select_scan	26	The number of joins that did a full scan of the first table.

Replication
Variable	Value	Description

Rpl_status	NULL	The status of failsafe replication (not yet implemented).
Slave_open_temp_tables	0	The number of temporary tables currently open by the slave SQL thread.
Slave_retried_transactions	0	Total (since startup) number of times the replication slave SQL thread has retried transactions.
Slave_running	OFF	This is ON if this server is a slave that is connected to a master.

Sorting
Variable	Value	Description
Sort_merge_passes	0	The number of merge passes the sort algorithm has had to do. If this value is large, you should consider increasing the value of the sort_buffer_size system variable.
Sort_range	0	The number of sorts that were done with ranges.
Sort_rows	0	The number of sorted rows.
Sort_scan	0	The number of sorts that were done by scanning the table.

Tables
Variable	Value	Description
Flush (close) all tables Show open tables
Open_tables	64	The number of tables that are open.
Opened_tables	418	The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.
Table_locks_immediate	89	The number of times that a table lock was acquired immediately.
Table_locks_waited	0	The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.

Transaction coordinator
Variable	Value	Description
Tc_log_max_pages_used	0
Tc_log_page_size	0
Tc_log_page_waits	0


Variable	Value	Description
Compression	OFF
Open_files	128	The number of files that are open.
Open_streams	0	The number of streams that are open (used mainly for logging).
Open_table_definitions	69
Opened_files	769
Opened_table_definitions	69
Prepared_stmt_count	0
Queries	284
Uptime_since_flush_status	0 days, 2 hours, 9 minutes and 22 seconds

¹ On a busy server, the byte counters may overrun, so those statistics as reported by the MySQL server may be incorrect.

Friday, August 5, 2011

mysqldump

Category: MySQL — SkyHi @ Friday, August 05, 2011

@echo off

@echo Backing Up Server1
mysqldump -A -Q -R -c -e --lock-tables=FALSE -uXXXX -pXXXX -hX.X.X.1 > c:\backup\01.sql

@echo Backing Up Server2
mysqldump -A -Q -R -c -e --lock-tables=FALSE -uXXXX -pXXXX -hX.X.X.2  > c:\backup\02.sql

@echo Backing Up Server3
mysqldump -A -Q -R -c -e --lock-tables=FALSE-uXXXX -pXXXX -hX.X.X.3  > c:\backup\03.sql

set folderdate=%date:~7,2%-%date:~4,2%-%date:~10,4%
mkdir c:\backup\%folderdate%
set foldertime=%time:~0,2%-%time:~3,2%
mkdir c:\backup\%folderdate%\%foldertime%
move c:\backup\*.sql c:\backup\%folderdate%\%foldertime%\

@ECHO OFF
set folderdate=%date:~7,2%-%date:~4,2%-%date:~10,4%
move D:\seccam-data\0 D:\seccam-data\0_%folderdate%
mkdir D:\seccam-date\0

REFERENCES
http://www.robvanderwoude.com/datetiment.php#SetDate
http://forums.techguy.org/dos-other/79088-how-rename-folder-dos.html
http://twigstechtips.blogspot.com/2009/03/cant-change-directory-to-another-drive.html

Monday, June 13, 2011

Yes, You Can Run 18 Static Sites on a 64MB Link-1 VPS

Category: Apache, MySQL, Performance Tune — SkyHi @ Monday, June 13, 2011

One thing I hated about WebHostingTalk is how much bad advice the so-called “professionals” are giving out to the world. Some poor college student asked in the VPS forums whether he is able to run 18 static HTML sites on VPSLink.com Link-1 plan (64MB RAM, 2.5GB storage & 100GB/month data), and the typical responses are:

“I do not believe you can host 18 websites on 64MB of RAM. I’d bump that up to at least 128 or 256.” –nexbyte
“I really wouldn’t advise anything lower than 265MB RAM for website hosting.” –RikeMedia

(Well, there are some more optimistic comments but I mainly list out those “with things to sell”)
So, just trying to prove the point that yes, 64MB is more than enough to host 18 static sites, I decided to add a Link-1 Xen to my account and document the process. Btw, thanks to Dan @ VPSLink for getting my billing issue resolved :) You can get 10% recursive discount here, or 66% off for the first 3 months here.

Setting Up the VPS

After my order has been provisioned, I re-image the server with a Debian 5 “Lenny” image. I normally pick Debian or Ubuntu because apt-get uses much less memory than RedHat/Fedora’s equivalent, and it’s also my personal preference. I named my new VPS “endor” as I usually just name my boxes after Star Wars systems. Re-imaging a VPS is pretty fast — 2 minutes later I have my root password sent to my email address so I can ssh in to set up the new system.

$ ssh root@endor
root@endor's password:
Linux 66671 2.6.18-53.1.13.el5xen #1 SMP Tue Feb 12 14:04:18 EST 2008 i686

endor:~# free
             total       used       free     shared    buffers     cached
Mem:         65704      64008       1696          0       5616      44100
-/+ buffers/cache:      14292      51412
Swap:       131064          0     131064
endor:~# cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 15
model name      : Intel(R) Core(TM)2 Duo CPU     E4500  @ 2.20GHz
stepping        : 13
cpu MHz         : 2194.496
cache size      : 2048 KB
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 10
wp              : yes
flags           : fpu tsc msr pae mce cx8 apic mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc up pni monitor ds_cpl est tm2 cx16 xtpr lahf_lm
bogomips        : 5558.81

Plenty of free memory and a single core of C2Duo E4500 — although not a high-end Xeon CPU, but should be more than sufficient to do what we need it to do. The next thing I want to do is to make sure every package is up to date.

endor:~# apt-get update && apt-get upgrade
Get:1 http://debrepo.mirror.vpslink.com lenny Release.gpg [386B]
Get:2 http://debrepo.mirror.vpslink.com lenny Release [63.2kB]
Get:3 http://debrepo.mirror.vpslink.com lenny/main Packages [5295kB]
Get:4 http://security.debian.org lenny/updates Release.gpg [197B]
Get:5 http://security.debian.org lenny/updates Release [40.8kB]
Get:6 http://debrepo.mirror.vpslink.com lenny/contrib Packages [76.1kB]
Ign http://security.debian.org lenny/updates/main Packages/DiffIndex
Get:7 http://security.debian.org lenny/updates/contrib Packages [14B]
Get:8 http://security.debian.org lenny/updates/main Packages [50.6kB]
Fetched 5526kB in 4s (1330kB/s)
Reading package lists... Done
...

Setting Up Web Server

Okay. The 64MB VPS is now up and running. What should we do next? Installing a web server of course, so we can start serving our static pages! Which web server? Definitely not Apache as it would be a waste of valuable memory here. Again my personal favourite is Nginx (pronounces Engine X), which currently powers LowEndBox.com. However, in this exercise I will go for Lighttpd because I found it easier to set up for abitary sites.
First of all — get Lighttpd installed.

endor:~# apt-get install lighttpd
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
...
Setting up libterm-readkey-perl (2.30-4) ...
Setting up libterm-readline-perl-perl (1.0302-1) ...
Setting up lighttpd (1.4.19-5) ...
Starting web server: lighttpd.

endor:~# ps -u www-data u
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
www-data  1690  0.0  1.5   5416  1008 ?        S    07:17   0:00 /usr/sbin/lighttpd -f /etc/lighttpd/lighttpd.conf

Plain vanilla stripped down and un-configured 32 bit Lighttpd sits around 1MB RSS — not bad.
Next, we need to get our websites up there and point Lighttpd to them. It’s a good idea to put the web sites in an organised structure inside the file system. I usually just place them this way:

/var/www//html

So if I have an HTML file at http://www.example.com/testing.html, it will sit on the file system at /var/www/www.example.com/html/testing.html. Unfortunately I do not have 18 static sites. For testing purpose I am only going to display a very basic HTML page at http://test.lowendbox.com/.

endor:~# mkdir -p /var/www/test.lowendbox.com/html
endor:~# echo 'Low End Box Rocks!
' > /var/www/test.lowendbox.com/html/index.html

So now our “website” is ready — how does Lighttpd, our webserver, knows where to find the files corresponding to the website? That’s where Lighttpd’s mod_simple_vhost comes in handy.

endor:~# lighttpd-enable-mod simple-vhost
Available modules: auth cgi fastcgi proxy rrdtool simple-vhost ssi ssl status userdir
Already enabled modules:
Enabling simple-vhost: ok
Run /etc/init.d/lighttpd force-reload to enable changes
endor:~# /etc/init.d/lighttpd force-reload
Stopping web server: lighttpd.
Starting web server: lighttpd.

Now navigate to test.lowendbox.com (which already has an A record to my new VPS’s IP address) — here we have it! Low End Box Rocks!!!

Prerequisite:
You must be already familiar with DNS and know how to create records to point to IP addresses. For free DNS hosting I recommend EveryDNS, which has also been hosting LowEndBox’s domain.

You can now basically just dump static files at /var/www//html, with resolved to your VPS’s IP address, and you will have your static websites over there in no time. You do not even need to tell Lighttpd to reload, as mod_simple_vhost automatically maps the hostname to appropriate file name. Repeat it 18 times and problem solved!
At 1 single testing site with no traffic, Lighttpd sits at around 1.5MB RSS, although I doubt it would increase significantly when you increase the number of sites or the traffic. Lighttpd and Nginx are single-threaded poll-based asynchronised web servers so for static file serving, the bottle-neck would be disk/network IO rather than amount of memory or CPU performance.
There are still lots of memory left. Maybe we can have some fun.

Installing WordPress

So you think, “hey Low End Box rocks and it runs on WordPress. So maybe I will have that installed as well!” Wow. But WordPress is a content management system for creating dynamic websites! It simply cannot be possible on a 64MB VPS, the WHT crowd says! Grrr!! Let’s give it a try.
To run WordPress from your static-file serving Lighttpd, you need a few more packages — namely MySQL and PHP in CGI/FastCGI mode.

endor:~# apt-get install mysql-server php5-cgi php5-mysql
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
...
Creating config file /etc/php5/cgi/php.ini with new version
Setting up php5-mysql (5.2.6.dfsg.1-1+lenny2) ...
Setting up sgml-base (1.26) ...
Setting up xml-core (0.12) ...
Setting up mailx (1:20071201-3) ...

I know it installs whole lot of other junks but don’t worry — we will live with them first and will try to optimise later. It also requires you to set up the root password for MySQL server, and I conveniently chose the most obscured password in this exercise — “root” (yes, don’t use that because I am already using it as my root password :)
We then need to configure lighttpd to handle PHP files.

endor:~# cat > /etc/lighttpd/conf-enabled/10-cgi-php.conf
server.modules += ("mod_cgi")
cgi.assign = (".php" => "/usr/bin/php5-cgi")^D
endor:~# /etc/init.d/lighttpd force-reload
Stopping web server: lighttpd.
Starting web server: lighttpd.

Done! It should be able to serve PHP files. Just to test it out:

endor:~# echo '' > /var/www/test.lowendbox.com/html/phpinfo.php

Now navigate to http://test.lowendbox.com/phpinfo.php — you should be able to see the output of phpinfo() function. What we are going to do next is to set up a WordPress blog under http://test.lowendbox.com/blog/. WordPress.org already provides a great tutorial on installing WordPress, but let’s do it step by step on the command prompt.
My plan:

Create database “test_blog”
Download the latest WordPress and unzip to test.lowendbox.com/blog
Set up configuration file and run the WordPress install
Update Lighttpd to provide clean URL, aka Pretty Permalinks.

Let’s go!

endor:~# mysqladmin -uroot -proot create test_blog
endor:~# wget http://wordpress.org/latest.tar.gz
Resolving wordpress.org... 72.233.56.138, 72.233.56.139
Connecting to wordpress.org|72.233.56.138|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/x-gzip]
Saving to: `latest.tar.gz'

...

2009-03-17 13:03:15 (1.01 MB/s) - `latest.tar.gz' saved [1624416]

endor:~# tar zxf latest.tar.gz -C /var/www/test.lowendbox.com/html
endor:~# cd /var/www/test.lowendbox.com/html
endor:/var/www/test.lowendbox.com/html# mv wordpress blog
endor:/var/www/test.lowendbox.com/html# mv blog/wp-config-sample.php blog/wp-config.php
endor:/var/www/test.lowendbox.com/html# vi blog/wp-config.php

When you are editing WordPress’ configuration file, set DB_NAME to “test_blog”, DB_USER and DB_PASSWORD to “root” for something quick, dirty and potentially insecure. Here is one final step — navigate to http://test.lowendbox.com/blog/, and WordPress will guide you through the rest of setup.
It is also relatively easy to set up pretty permalinks for WordPress on Lighttpd. In our example,

endor:~# cat > /etc/lighttpd/conf-enabled/lowendbox.conf
$HTTP["host"] == "test.lowendbox.com" {
    $HTTP["url"] =~ "^/blog/" {
        server.error-handler-404 = "/blog/index.php"
    }
}^D
endor:~# /etc/init.d/lighttpd force-reload
Stopping web server: lighttpd.
Starting web server: lighttpd.

That’s it! You can now go into WordPress to configure the desirable Permalink Structure. Do note that the current WordPress dashboard page is very resource intensive, as it fetches development blog, other WP news, incoming links, etc from various sources, concurrently on separate PHP CGI processes. There might be plugins to turn off this server-killing behavior (or just use older version of WordPress like 2.0.x which is still maintained). Likewise some WP caching plugin can be very useful in reducing the load. Google them and you shall find.

Optimisation — Squeeze More Memory!

So now we have a Debian 5 web server box that can handle lots of static sites + a few WordPress blogs, and it fits “fine” on a 64MB Xen VPS. Let’s see what processes are running:

endor:~# ps aux
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
...
root       325  0.0  0.4   2032   292 ?        S<s  04:25   0:00 udevd --daem
root       879  0.0  0.4   2788   304 ?        Ss   Mar17   0:00 /bin/bash --
root      1216  0.0  0.0      0     0 ?        S    Mar17   0:00 [pdflush]
root      1649  0.0  0.2   3144   188 ?        Ss   Mar17   0:00 /usr/sbin/famd
root      6427  0.0  4.4   8024  2928 ?        Ss   Mar17   0:00 sshd: root@pts/
root      6429  0.0  2.3   2804  1564 pts/0    Ss   Mar17   0:00 -bash
root      6441  0.0  1.8  33092  1200 ?        Sl   Mar17   0:00 /usr/sbin/rsysl
root      6453  0.0  1.4   5284   976 ?        Ss   Mar17   0:00 /usr/sbin/sshd
root      6470  0.0  1.3   2048   896 ?        Ss   Mar17   0:00 /usr/sbin/cron
daemon    6482  0.0  0.8   1772   560 ?        Ss   Mar17   0:00 /sbin/portmap
www-data  6510  0.0  2.6   5848  1736 ?        S    Mar17   0:00 /usr/sbin/light
root      6572  0.0  1.7   2488  1156 pts/0    S    Mar17   0:00 /bin/sh /usr/bi
mysql     6611  0.0 26.2 143652 17228 pts/0    Sl   Mar17   0:00 /usr/sbin/mysql
root      6613  0.0  0.8   1636   536 pts/0    S    Mar17   0:00 logger -p daemo
103       6973  0.0  1.3   6112   908 ?        Ss   Mar17   0:00 /usr/sbin/exim4
root      6986  0.0  1.3   2308   908 pts/0    R+   00:01   0:00 ps aux
endor:~# free
             total       used       free     shared    buffers     cached
Mem:         65704      51424      14280          0        936      22588
-/+ buffers/cache:      27900      37804
Swap:       131064        864     130200

Note that it’s an idle box. The swap is slightly used and at 37MB free it is actually not too bad. Let’s try to squeeze a little bit more memory out from the factory setup.
MySQL is by far the biggest offender, and I have talked about how to reduce MySQL memory usage here. If you are just running simple CMS, InnoDB is probably not required — it uses more memory and a lot heavier on IO as well. We can simply use the LxAdmin’s mysql.cnf which I linked on the other blog post to get the bare-minimum MySQL running.

endor:~# cat > /etc/mysql/conf.d/lowendbox.cnf
[mysqld]
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K
skip-bdb
skip-innodb^D

As mysqld_safe script uses /bin/sh for scripting, it’s also a good idea to check whether dash is used instead of bash.

endor:~# apt-get install dash
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following NEW packages will be installed:
...
Unpacking dash (from .../dash_0.5.4-12_i386.deb) ...
Processing triggers for man-db ...
Setting up dash (0.5.4-12) ...

endor:~# dpkg-reconfigure dash
Adding `diversion of /bin/sh to /bin/sh.distrib by dash'
Adding `diversion of /usr/share/man/man1/sh.1.gz to /usr/share/man/man1/sh.distrib.1.gz by dash'
endor:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.
Checking for corrupt, not cleanly closed and upgrade needing tables..

One thing I don’t like about Debian 5 is its default inclusion of rsyslog. Well — it’s feature rich, but I don’t need MySQL and TCP syslog support. Weight at 1.2MB RSS is just a bit too fat I reckon. I am not game enough to go without a syslog daemon, so I just go for syslog-ng. Probably not the most light weight, but it’s just something I have been using for the last couple of years.

endor:~# ps -C rsyslogd v
  PID TTY      STAT   TIME  MAJFL   TRS   DRS   RSS %MEM COMMAND
 6441 ?        Sl     0:00      0   207 32936  1260  1.9 /usr/sbin/rsyslogd -c3

endor:~# apt-get install syslog-ng && dpkg --purge rsyslog
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following extra packages will be installed:
...
Setting up syslog-ng (2.0.9-4.1) ...
Starting system logging: syslog-ng.
(Reading database ... 16517 files and directories currently installed.)
Removing rsyslog ...
Purging configuration files for rsyslog ...

endor:~# ps -C syslog-ng v
  PID TTY      STAT   TIME  MAJFL   TRS   DRS   RSS %MEM COMMAND
 8215 ?        Ss     0:00      0   105  2754   708  1.0 /usr/sbin/syslog-ng -p

Shedding 500kb RSS — not too bad I guess :)
Next — Portmap and FAM got installed when Lighttpd was first installed. Lighttpd does not really need FAM. It’s used for stat cache to reduce seeks, but can live without. Not that I have noticed any performance difference anyway for small traffic anyway. Having both of them removed from the process list would give us extra 750KB.

endor:~# apt-get remove --purge portmap
eading package lists... Done
Building dependency tree
Reading state information... Done
The following packages will be REMOVED:
...

OpenSSH can be replaced by dropbear to save memory.

endor:~# touch /etc/ssh/sshd_not_to_be_run
endor:~# apt-get install dropbear
...
endor:~# vi /etc/default/dropbear
endor:~# /etc/init.d/dropbear start
Starting Dropbear SSH server: dropbear.

Just remember to set NO_START=0 in /etc/default/dropbear so dropbear can run as a daemon. Dropbear daemon is using around 500KB less than OpenSSH daemon during idle, and for each connection it uses 1.5MB less on this Debian 5 box — that’s quite a saving!
That’s probably it! Vixie cron can be replaced by a light weight DCRON but I can’t seem to be able find it in Debian’s repository. Exim4 is probably one of the most light weight mail daemon you can have, but then again you might want to question — “do I need a mail daemon running”? You can probably bring it down, and just run /usr/sbin/runq once every 2 hours to process the queue, in case the previous delivery failed. That would probably give you another 1MB to play with.
You can also use PDKSH to replace BASH as interactive shell to loose some weight.

endor:~# ps -C bash v
  PID TTY      STAT   TIME  MAJFL   TRS   DRS   RSS %MEM COMMAND
 8409 pts/1    Ss     0:00      2   663  2140  1568  2.3 -bash
endor:~# apt-get install pdksh
endor:~# chsh -s /bin/pdksh

# ps -C pdksh v
  PID TTY      STAT   TIME  MAJFL   TRS   DRS   RSS %MEM COMMAND
 8550 pts/0    Rs     0:00      0   174  1633   588  0.8 -pdksh

That’s 1 full megabyte off the scale! Also note that VPSLink’s /etc/inittab automatically spawn a BASH process on the console — just in case you got locked out from firewall. For me it’s the last line of inittab file. Change it to /bin/sh or /bin/pdksh, run init q to reload init(1), and then kill that bash process.
Here’s the end result:

# ps aux
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
...
root       325  0.0  0.4   2032   292 ?        S<s  Mar17   0:00 udevd --daem
root      1216  0.0  0.0      0     0 ?        S    Mar17   0:00 [pdflush]
root      6470  0.0  1.3   2048   896 ?        Ss   Mar17   0:00 /usr/sbin/cron
103       6973  0.0  1.3   6112   912 ?        Ss   Mar17   0:00 /usr/sbin/exim4
root      7953  0.0  0.7   1716   524 ?        S    00:23   0:00 /bin/sh /usr/bi
mysql     7992  0.0  8.2  37904  5404 ?        Sl   00:23   0:00 /usr/sbin/mysql
root      7994  0.0  0.8   1636   536 ?        S    00:23   0:00 logger -p daemo
root      8215  0.0  1.1   2860   776 ?        Ss   00:31   0:00 /usr/sbin/syslo
www-data  8313  0.0  2.4   5712  1640 ?        S    00:37   0:00 /usr/sbin/light
root      8418  0.0  0.7   2052   468 ?        Ss   00:51   0:00 /usr/sbin/dropb
root      8527  0.0  0.7   1712   468 ?        Ss   01:19   0:00 /bin/sh --
root      8549  0.0  1.9   2712  1300 ?        Ss   01:21   0:00 /usr/sbin/dropb
root      8550  0.0  0.9   1808   600 pts/0    Rs   01:21   0:00 -pdksh
root      8562  0.0  1.3   2308   908 pts/0    R+   01:26   0:00 ps aux
# free
             total       used       free     shared    buffers     cached
Mem:         65704      58852       6852          0       2180      40344
-/+ buffers/cache:      16328      49376
Swap:       131064        380     130684

That’s 12MB trimmed, which can be used in disk cache to improve static file serving.

Conclusion

So how do we conclude? 64MB is more than enough to serve a few low traffic static websites. You can actually run a few WordPress sites with a few hundred visitors a day — at the price equivalent to many heavily oversold shared hosting and you get root access!
One thing about root access though — in all my examples above I used root account and never bothered to use a normal user account. It is bad from security aspect so don’t do it. Or at least don’t tell anyone that you use nothing but root :)

REFERNCES
http://www.lowendbox.com/blog/yes-you-can-run-18-static-sites-on-a-64mb-link-1-vps/