1

I am running MySQL server on the server's which has following specifications -

Dual Xeon Quad Core 2.0, 2048 MB RAM, 1x 160 GB SATA Fedora Core + SSH

But MySQL process for inserting 10000 records take more than 100% of CPU and up to 1 GB of RAM. It's a plain insert statement.

Why is MySQL is taking so much of memory and what can done about it?.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
MySQL DBA
  • 5,692
  • 21
  • 54
  • 71
  • 1
    Not enough information to answer and probably belongs on serverfault.com – jitter Nov 07 '09 at 11:33
  • Please post your schema and the insert statement. – brian-brazil Nov 07 '09 at 11:46
  • Sorry jitter. If I posted on wrong forum. These are the details :- Use Test1; Insert into Customer(FirstName, LastName, CountryID, Address, Phone) Select FirstName, LastName, CountryID, Address, Phone From Test2.Customer Where UserID Between 1 and 5000 and CustomerID in (Select CustId from Cust_Details Where CustName like 'Mi%'); – MySQL DBA Nov 07 '09 at 12:04
  • Sure more than 100% is due to multiple processes/threads. – Jé Queue Nov 07 '09 at 14:49

1 Answers1

1

The heaviest part in your query is the SELECT:

Select FirstName, LastName, CountryID, Address, Phone
From Test2.Customer
Where UserID Between 1 and 5000
and CustomerID in (Select CustId from Cust_Details Where CustName like 'Mi%')

The nested query probably is repeated for each row. You can check this running EXPLAIN PLAN + all the SELECT query. I guess the 'like' operator is used against a non-indexed column. In this case (like 'xyz%') a simple index can improve performance a lot.

[Added: moreover, SELECT CustId ... must output id's that are greater than 5000, that aren't needed at all. A composite index (CustId, CustName) on Cust_Details must also be useful.]

Try usign a join instead:

Select FirstName, LastName, CountryID, Address, Phone
From Test2.Customer c, Cust_Details cd
Where c.UserID Between 1 and 5000
and c.CustomerID=cd.CustId
and left(cd.CustName) = 'Mi'
culebrón
  • 34,265
  • 20
  • 72
  • 110