Archive

For the MySQL category

Move and secure the MySQL data directory

No Comments

By default, the data directory of MySQL is /var/lib/mysql. This can be a security issue, especially on a laptop. If your home directory is encrypted, you can easily secure you MySQL data. Backup you data before using this information. Read the procedure until the end before doing anything.

First, follow these steps to move the data directory of MySQL.

  1. Stop MySQL.
  2. sudo service mysql stop
  3. Copy the current database to its new location /home/yourhomedir/mysql.
  4. sudo mv /var/lib/mysql /home/yourhomedir/
  5. Create a link to prevent issues with innoDB tables
  6. ln -s /home/yourhomedir/mysql /var/lib/mysql
  7. Change the value of datadir in the file /etc/mysql/mysql.conf.d/mysqld.cnf for your new data directory location /home/yourhomedir/mysql
  8. Modify the two occurences of /var/lib/mysql with /home/yourhomedir/mysql in /etc/apparmor.d/usr.sbin.mysqld
  9. Reload apparmor
  10. sudo service apparmor reload

If you moved the MySQL data directory to an encrypted home directory, you will need to perform these supplementary steps.

  1. Disable MySQL from starting at system boot.
  2. sudo update-rc.d mysql remove
  3. Add MySQL to the group related to the user that owns the encrypted home directory
  4. sudo usermod -a -G yourhomedir mysql
  5. Change the permissions of the encrypted directory
  6. sudo chmod 750 /home/yourhomedir

In this scenario, you will need to start MySQL after you logged on and shut it down before loggoff which include shutting down the system. To start mysql, use the following command:

sudo service mysql start

To stop MySQL, use the following command:

sudo service mysql stop

If everything works fine, you can remove the original MySQL data directory using this command:

sudo rm -fr /var/lib/mysql

Tomcat and MySQL benchmarks

No Comments

Benkmarks made using a Tomcat and a MySQL database. These figures are provided to give you an idea of the performance you should expect.

The tests were done in the following conditions:

  • Intel Q6600 processor (Core 2 Quad) running at 2.40 GHz
  • 3 GB of DDR2 memory
  • 1066 MHz system bus
  • 500 GB, 7200 rpm SATA hard drive
  • Tomcat version 6.0.16
  • MySQL 5.0.45
  • mysql-connector-java 5.0.8
  • Sun JVM 1.5.0_14-b03
  • Windows Vista

Random record access
Description

20,000 records have been randomly accessed from tables containing various number of records. The record size is 1367 bytes. The table structure is:

CREATE TABLE table01 (
  id int(11) NOT NULL,
  field1 varchar(255) NOT NULL,
  field2 varchar(255) NOT NULL,
  field3 varchar(255) NOT NULL,
  field4 varchar(255) NOT NULL,
  field5 varchar(255) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Observations

This table resumes the test results.

Table size (records) Table size Total time (secs) Access rate (reqs/sec.)
100 160.0 KB 2.52 7935.3
500 800.0 KB 2.57 7791.3
1,000 1.5 MB 2.74 7303.3
5,000 7.5 MB 4.62 4332.2
10,000 13.5 MB 6.50 2858.5
50,000 65.6 MB 97.50 204.6
100,000 130.6 MB 116.50 171.0
500,000 653.0 MB 138.50 144.2
1,000,000 1.3 GB 147.00 135.8
5,000,000 6.4 GB 194.00 102.7
10,000,000 12.7 GB 264.00 75.7

Conclusions

As expected, the access rate decreases when the table size increases. When the table has 10,000 records and less, the data is in the caches which lead to a very high access rate. When the tables are larger, the rate decreases since the data must be fetched from the disk. The access rate decreases when the table size increases.

Sequential record insertion
Description

1,000,000 records have been inserted into a empty table which structure is:

CREATE TABLE table01 (
  id int(11) NOT NULL,
  field1 varchar(255) NOT NULL,
  field2 varchar(255) NOT NULL,
  field3 varchar(255) NOT NULL,
  field4 varchar(255) NOT NULL,
  field5 varchar(255) NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Observations

The total database size is 1304 MB. The record size is 1367 bytes. The insertion rate (insertions per second) was computed each time 100,000 records were inserted. The average insertion rate observed is 721.37 with a standard deviation of 26.07. Similar result was obtained when inserting 10,000,000 records into an empty table. A rate of 740.71 insertions per second was measured.

Conclusions

The insertion rate does not vary whether the table is empty of has a significant number of records.

Blue Taste Theme created by Jabox