Pass application user id to MySQL database??

In all our application we connect to MySQL database as single db user, and we need to pass the end user id to the mysql database?

So how can we pass it?

I've idea to create plugin to add mysql variables ex. "app_userid" and set it when user login??!!

but i don't have any idea how to create plugin?

Or do you have another idea??

Note: i don't want to change the application code, and i need it portable.

InnoDB tablespace, single Vs. multiple, and InnoDB defragment

The ibdata file is too big 10GB, and actually we've only about 2GB (data+index) in innodb storage engine.

How we can defragment this file and reduce it?

How is this happened?

By default the ibdata file created initially by (innodb_data_file_path = ibdata1:10M:autoextend) and auto extended by (innodb_autoextend_increment = 8MB) when it’s needed, and this file (tablespace) contain all innodb tables (innodb_file_per_table=OFF) single tablespace.

In this configuration the file will too big, especially when u need to test something in innodb engines and create a lot of innodb tables.

another problem if you truncate or delete all or some data the ibdata file will not decrease size, and when optimize the table the data part of this table will optimize but still there are gaps between tables in tablespace.


In my case:

ibdata file in 10GB and the total sum of (data_length+index_length) of all innodb tables not exceed 2GB!!!!!!!!!!

OS and I/O manipulate with 10GB file but we need only 2GB in worst case!!!!!

If we set innodb_file_per_table=ON then alter all innodb tables, mysql will generate ibdata for each table with its size, its good!!!, but the shared tablespace still exist (10GB) and cannot delete it (it’s necessary even innodb_file_per_table enable or disable) BAD!!!!!!


The Solution:

1- Get all innodb tables in your database:
Select table_name, table_schema from information_schema.tables where engine = 'innodb';

Convert all to myisam by using: alter table table_name engine myisam;
2- Then shutdown mysql.

Till now ibdata file same size 10GB

3- Rename or move to another directory ibdata and ib_log files.
Check this global variables innodb_data_home_dir and innodb_log_group_home_dir to know where are there.

4- Add innodb_file_per_table to cnf file.

5- Then start mysql

mysql will create ibdata file with default size 10MB, and ib_logfile0 and ib_logfile1 (logs).

6- Return back tables to innodb by using: alter table table_name engine innodb;

mysql will create file for each table in database directory.


See this scenario:

My.cnf
innodb_additional_mem_pool_size = 128M
innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 16M
innodb_log_file_size = 16M
innodb_file_per_table

note:
this [user@server~]$ in shell command
and this mysql> in mysql command
in sequence order

mysql> create table musers_test like musers;
Query OK, 0 rows affected (0.02 sec)

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 432K Jun 16 06:28 /var/lib/mysql/test_db/musers_test.ibd

mysql> insert into musers_test select * from musers limit 500000;
Query OK, 500000 rows affected, 1 warning (1 min 12.43 sec)
Records: 500000 Duplicates: 0 Warnings: 0

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:31 /var/lib/mysql/test_db/musers_test.ibd

mysql> delete from musers_test;
Query OK, 500000 rows affected (1 min 26.64 sec)

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:34 /var/lib/mysql/test_db/musers_test.ibd

mysql> insert into musers_test select * from musers limit 500000;
Query OK, 500000 rows affected, 1 warning (2 min 58.44 sec)
Records: 500000 Duplicates: 0 Warnings: 0
/* notice the execution time is double previous insert (from 1 min 12.43 sec to 2 min 58.44 sec) */

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 416M Jun 16 06:38 /var/lib/mysql/test_db/musers_test.ibd

mysql> alter table musers_test;
Query OK, 0 rows affected (0.00 sec)

/* decrease file size because innodb_file_per_table=ON */
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:43 /var/lib/mysql/test_db/musers_test.ibd

mysql> delete from musers_test;
Query OK, 500000 rows affected (1 min 2.82 sec)

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:47 /var/lib/mysql/test_db/musers_test.ibd

mysql> alter table musers_test engine innodb;
Query OK, 0 rows affected (5.91 sec)
Records: 0 Duplicates: 0 Warnings: 0

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 432K Jun 16 06:48 /var/lib/mysql/test_db/musers_test.ibd

mysql> insert into musers_test select * from musers limit 5000;
Query OK, 5000 rows affected, 1 warning (1.18 sec)
Records: 5000 Duplicates: 0 Warnings: 0

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 13M Jun 16 06:49 /var/lib/mysql/test_db/musers_test.ibd

mysql> delete from musers_test limit 4000;
Query OK, 4000 rows affected, 1 warning (0.52 sec)

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 13M Jun 16 06:49 /var/lib/mysql/test_db/musers_test.ibd

mysql> alter table musers_test engine innodb;
Query OK, 1000 rows affected (0.37 sec)
Records: 1000 Duplicates: 0 Warnings: 0

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 9.0M Jun 16 06:50 /var/lib/mysql/test_db/musers_test.ibd

also you can run shel command from mysql as mysql> \! ls -lh /var/lib/mysql/test_db/musers_test.ibd


Regards,
Mohammad Lahlouh
:)

vBulletin, session table is InnoDB

In large vBulletin forum we had strange problem in memory table "session", we've 25M post, 1.7M user, 20K online user.

So we change engine of session table to InnoDB and set configuration of innoDB as follow (be careful this configuration is not proper for other tables because this is good in performance but bad in crash and recovery, and data reliability)

innodb_data_home_dir = /dev/shm/mysql/ #this path in memory partition
innodb_log_group_home_dir = /dev/shm/mysql/
innodb_flush_method = O_DIRECT
innodb_support_xa = 0
innodb_thread_concurrency = 20
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 50
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite = 0

thanks

any comment?

UPDATE:


In this configuration when restart MySQL in error log will see:

090620 1:20:06 InnoDB: Failed to set O_DIRECT on file /dev/shm/mysql/ibdata1: OPEN: Invalid argument, continuing anyway
090620 1:20:06 InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662
090620 1:20:06 InnoDB: Failed to set O_DIRECT on file /dev/shm/mysql/ibdata1: OPEN: Invalid argument, continuing anyway
090620 1:20:06 InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662

So may be a bug with tmpfs and O_DIRECT!!!

see http://bugs.mysql.com/45671

Upgrading MySQL with minimal downtime through Replication

Problem

With the release of MySQL 5.1, many DBAs are going to be scheduling downtime to upgrade their MySQL Server. As with all upgrades between major version numbers, it requires one of two upgrade paths:

• Dump/reload: The safest method of upgrading, but it takes out your server for quite some time, especially if you have a large data set.
• mysql_upgrade: A much faster method, but it can still be slow for very large data sets.

I’m here to present a third option. It requires minimal application downtime, and is reasonably simple to prepare for and perform.

Preparation

First of all, you’re going to need a second server (which I’ll refer to as S2). It will act as a ’stand-in’, while the main server (which I’ll refer to as S1) is upgraded. Once S2 is ready to go, you can begin the preparation:

• If you haven’t already, enable Binary Logging on S1. We will need it to act as a replication Master.
• Add an extra bit of functionality to your backup procedure. You will need to store the Binary Log position from when the backup was taken.
  • If you’re using mysqldump, simply add the –master-data option to your mysqldump call.
  • If you’re using InnoDB Hot Backup, there’s no need to make a change. The Binary Log position is shown when you restore the backup.
  • For other backup methods, you will probably need to get the Binary Log position manually:

    mysql> FLUSH TABLES WITH READ LOCK;
    mysql> SHOW MASTER STATUS;
    (Perform backup now...)
    mysql> UNLOCK TABLES;

Once you have a backup with the corresponding Binary Log position, you can setup S2:

• Install MySQL 5.1 on S2.
• Restore the backup from S1 to S2.
• Create the Slave user on S1.
• Enter the Slave settings on S2. You should familiarise yourself with the Replication documentation.
• Enable Binary Logging on S2. We’ll need this during the upgrade process.
• Setup S2 as a Slave of S1:
  • If you used mysqldump for the backup, you will need to run the following query:

    mysql> CHANGE MASTER TO MASTER_HOST='S2.ip.address', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password';

  • For any other method, you’ll need to specify the Binary Log position as well:

    mysql> CHANGE MASTER TO MASTER_HOST='S2.ip.address', MASTER_USER='repl_user', MASTER_PASSWORD='repl_password', MASTER_LOG_FILE='mysql-bin.nnnnnnn', MASTER_LOG_POS=mmmmmmmm;

• Start the Slave on S2:

  mysql> START SLAVE;

The major pre-upgrade work is now complete.

Upgrade

Just before beginning the upgrade, take a backup of S2. For speed, I’d recommend running the following queries, then shutting down the MySQL server and copying the data files for the backup.

mysql> STOP SLAVE;
mysql> SHOW MASTER STATUS;

Once the backup is complete, restart S2 and let it catch up with S1 again.

When you’re ready to begin the upgrade, you will need a minor outage. Stop your application, and let S2 catch up with S1. Once it has caught up, they will have identical data. So, switch your application to using S2 instead of S1. Your application can continue running unaffected while you upgrade S1 server.

• Stop the Slave process on S2:

  mysql> STOP SLAVE;

• Stop S1.
• Upgrade S1 to MySQL 5.1.
• Move the S1 data files to a backup location.
• Move the backup from S2 into S1’s data directory.
• Start S2.
• Setup S2 as a Slave to S1, same as when we made S1 a Slave of S2.
• Let S2 catch up with S1. When it has caught up, stop your application, and make sure S2 is still caught up with S1.
• Switch your application back to using S1.

Complete! Hooray! You just need to run a couple of queries on S1 to clean up the Slave settings:

mysql> STOP SLAVE;
mysql> CHANGE MASTER TO MASTER_HOST='';

Conclusion

You can keep the outage to only a few minutes while performing this upgrade, removing the need for potentially expensive downtime. If you need the downtime to be zero, you probably want to be looking at a Circular Replication system, though that’s getting a little outside of this blog post.