Mysql insert,updates very slow

Question

Our server database is in mysql 5.1 we have 754 tables in our db.We create a table for each project. Hence the large no of tables. From past one week i have noticed a very long delay in inserts and updates to any table.If i create a new table and insert into it,It takes one min to insert around 300 recs.

Where as our test database in the same server has 597 tables Same insertion is very fast in test db.

Default engine is MYISAM. But we have few tables in INNODB .

There were a few triggers running. After i deleted triggers it has become some what faster. But it is not fast enough.

Answer 1

USE DESCRIBE to know your query execution plans.

Look more at http://dev.mysql.com/doc/refman/5.1/en/explain.html for its usage.

Answer 2

Inserts are typically faster when made in bulk rather than one by one. Try inserting 10, 30, or 100 records per statement.

If you use jdbc you may be able to achieve the same effect with batching, without changing the SQL.

Answer 3

As @swapnesh mentions, the DESCRIBE command is very usefull for performance debugging. You can also check your installation for issues using:

• https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl

You use it like this:

wget https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

Of course, here I am assuming that you run some kind of a Unix based system.

You can use OPTIMIZE. According to Manual it does the following:

Reorganizes the physical storage of table data and associated index data, to reduce storage space and improve I/O efficiency when accessing the table. The exact changes made to each table depend on the storage engine used by that table

The syntax is:

OPTIMIZE TABLE tablename