Deleting MySQL Database log files

We have a master database and its slave database running on different nodes. The master DB is subjected to HUGE no. of inserts/updates because of which the log files written by it grew by enormous proportions (log files at one time occupied more than 80% of the disk space).

Log files are important because using them we can track the changes made to the DB and in case of any DB crash, we can rebuild it again from the scratch. But in our case, we didn't require the log files and had the luxury to get rid of them instead of finding a reliable storage to keep them safe.

We decided to go ahead with deleting the log files. We automated this process by setting a flag in configuration file so that MySQL automatically deleted the log files which are older than 90 days.

This can be done as follows:

1) Stop the Master MySQL server.

2) Add the follwing parameter to the my.cnf file (here the figure on the right hand side is the no. of days after which the log file must be cleared)

expire_logs_days = 90

3) Start the server.

Simple !!!


Incase, you just need to delete the log files without automating the process then it can be done using the following command (use desired date value):

PURGE BINARY LOGS BEFORE '2010-05-03 14:56:23';

Since the replication node uses the log files, we need to make sure we only delete files that were created before the logfile that the slave node is currently reading. We can get this data by using the following command on the slave database:

show slave status;

This command gives the details about the slave. The value of the column 'Master_Log_file' is the name of the log file currently being read by the slave. Once we have the name of this file, we can get its creation date. Lets assume the creation date was '2010-05-03 14:56:23'. All log files created before this time can be deleted with the following command

PURGE BINARY LOGS BEFORE '2010-05-03 14:56:23';

References:
MySQL v5 documentation for purging log files

Move MySQL database location

MySQL DB might using some location as data repository where hard-disk space is limited. This will warrant moving the data from that location to some other location which can provide more room to accomodate the increasing data. Following steps can move this data to the new location:


du -h : will show the memory parition of the current folder

--------------------


-->> Set the password for mysqlmanager (required if password is not set already).
mysqlmanager --passwd >> /etc/mysqlmanager.passwd

--> log into mysql manager
mysql -uroot -psh*pp@r --socket=/tmp/mysqlmanager.sock

-->> kill all instances of mysql
ps afx | grep mysql //get process ids
kill -9 4465 4473 4474 // kill those processes

-->> create the directory where you want to transfer the files
mkdir /mnt/mysql/marketingportal

-->> Give mysql the ownership of the directory and its contents (presently they are owned ny root)
chown -R mysql.mysql /mnt/mysql/ //mysql.mysql : group:user

--> Open the folder where the current data is
cd mysql-marketingportal/

--> Copy this data to the new folder
cp -R * /mnt/mysql/marketingportal/

-->> Give mysql the ownership of the new directory and its contents (presently they are owned ny root)
chown -R mysql.mysql /mnt/mysql/ //mysql.mysql : group:user

-->> Change the name of the old directory (optional - this is done to make sure that it isn't beign used any longer)
mv mysql-marketingportal/ mysql-marketingportal-old/

-->> edit the my.cnf file
change the value of datadir

-->> Start the mysqlmanager
nohup mysqlmanager --user=mysql > mysqlmanager.out &

High Performance Database Tips

Some Database mantras discovered on the basis of practical experience.

• Use the smallest datatype possible. eg. where a integer can take care of all possible states, don't use bigint. Every bit of extra space alloted will make the DB to grow in size without any actual utility, and there by taking toll on the performance
• Choose what to index and how much to index. Indexing is creating a meta data of data. If data is being fetched on the basis of a particular attribute, make that attribute an index. It would will definitely boost-up the reads and updates. Also, keep in mind that indexing increases the insert time and these meta-data information do occupy disk space. Partial Indexing is a good way to create indexes not the on the complete field, but the deciding part of it. eg. Index can be created for the first few character's URLs if data to be fetched is on the basis of host name.
  
• Choose storage engine wisely. eg. If the DB is not relational, go for MyISAM. Read the pros-cons of each engine before choosing one.
  
• Controlled Data Redundancy. Higher the normalisation of a DB, lesser is the redundancy. But, one think to keep in mind is that retrieving data from such a DB might require joining of tables, which is a very expensive operation. If the size of DB is huge or if the data to be fetched is distributed across a significant no. of tables, then performance will take the hit. One has to make a educated choice between space and performance. My experience tells me that a bit of redudancy isn't that bad !
• Fetch only relevant data. If you require some fields of a table(s), retrieve only those fields. Don't do a 'select *'.
• Better programming logic: When programitically accessing DB use reuse connections. eg. If on the same DB schema, we need to fire 2 quries to retrive logical/related set of data, try using the same connection. Creating connection is costly. Also, Make sure to close all connections when use is over.
• Don't run all servers on the same node. If application receives a huge no. of hits, and also tremendous DB operations are taking place in the DB (like huge no. of inserts/read/writes), its better to host the application on a node diffrerent from the DB. There is only so much a single node can handle.
  

MySQL Table and Schema Size

I found these 2 commands for finding the size of a table or whole schema on MySQL mailing list (unable to recollect the URL)


Query To Find the size of a schema

#Query to find the size of a database schema (replace YOUR_SCHEMA_NAME with appropriate name)

#Run this query without selecting any schema

SELECT s.schema_name, CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length)) /1024/1024,2),0.00),"Mb") total_size, CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb") data_used, CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free, IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free)) /((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used, COUNT(table_name) total_tables FROM INFORMATION_SCHEMA.SCHEMATA s LEFT JOIN INFORMATION_SCHEMA.TABLES t ON s.schema_name = t.table_schema WHERE s.schema_name = "YOUR_SCHEMA_NAME" GROUP BY s.schema_name ORDER BY pct_used DESC\G;

-------------------------------------------------------

Query To Find the size of a table

# Query to find size of a particular table (replace YOUR_TABLE_NAME with appropriate name)

# Run this after choosing the schema

show table status like 'YOUR_TABLE_NAME'

MySQL database dump

How to take MySQL database dump:

mysqldump -hhostname-uusername -ppassword schemaname tablename (contents will be printed on the console)

mysqldump -hhostname -uusername -ppassword schemaname tablename> filename (contents will be written on file)

hostname: name of the host. If the db is on your local system, then this might not be needed

schemaname: name of the schema which you want to back up

tablename: name of the table which you want to back up

filename: name of the file which will hold the dump. You can specify the location of the file too. Eg. C:\sql\testsqldump.sql. If 'sql' directory doesn't exist and error will be generated.

eg. mysqldump -uroot -padmin userinfo_schema USER_TABLE > D:\sql\userinfo.sql


If you don't provide the tablename, then the dump of whole schema will be taken.

To take dump of all the schema
mysqldump -uroot -padmin --all-databases > filename


To populate the MySQL database:

mysql -uusername -ppassword schemaname < filename
filename: name of the file that contains the mysqldump

eg. mysql -uroot -padmin userinfo_schema < D:\sql\userinfo.sql

Derby

I had a project work in which I needed to port an existing web-application running on MySQL toDerby. The SQL and datatypes of both are fairly same with minor variations (like syntax for triggers), though Derby does NOT support a few features like 'limit'.

TIPS:

If you are using embedded derby, remember, only ONE connection can be made to the database at a time. If applications running on different JVM's try to access the same database resource at the same time, then except the first one, none will be able to get access. So, go for Network server, if you have any such requirements.

----------------------------------------------

Username & Password

While creating the derby database, if username and password were not specified, and the application you are using requires one, then use the following if it doesn't accept blank/null fields

username: app
password: app

----------------------------------------------

Derby Network Server (for Windows)

Goto
%DERBY_INSTALL%\frameworks\NetworkServer\bin
and type "startNetworkServer.bat"

This will start the server and by default it will listen on port 1527

example:

In my machine Derby is installed in
C:\Program Files\Apache Software Foundation

So, value of the DERBY_INSTALL variable is "C:\Program Files\Apache Software Foundation\db-derby-10.2.1.6-bin"

So, the command "cd %DERBY_INSTALL%\frameworks\NetworkServer\bin"
opens up the following:
C:\Program Files\Apache Software Foundation\db-derby-10.2.1.6-bin\frameworks\Net
workServer\bin>

Now entering "startNetworkServer.bat" starts the server on port 1527

Remember, your derby database files must be located in the
"C:\> cd %DERBY_INSTALL%\frameworks\NetworkServer\bin" directory,
if you are run the server following the aforesaid.

From the program through which you wish access the database, give the following path of the database as:

"jdbc:derby://localhost:1527/dbname"

where 'dbname' is the name of the database.

If the database is located in some other folder, say D:/dbfolder, then modify the above as follows:

"jdbc:derby://localhost:1527/D:/dbfolder/dbname"

----------------------------------------------

Resources:

• Tutorial on Apache Website