Bastien/vanekl, Do you think the ram could be the problem????? 2

[Robbie2003]

HANDLE: Robbie2003
POSTED ON: Nov 24

Bastien/vanekl

Hi guy's we have done everything suggested unfortunatly this has not resolved our problem it does not seem to have made any difference.

Do you think the ram could be the problem we are using a server with only 256 meg of ram from a hosting company, do you think if we upgraded to 1 gig it would resolve the problem. We obiously dont want to go to all the expense and trouble of moving the info from one server to another unless we know that this will definatly resolve the problem.

QUESTION:
The table in question has 5 columns:
An autoincrement mediumint (primary key)
An e-mail address varchar column
A tinyint (boolean) column denothing wether the user has receieved a particular kind of e-mail
Another tinyint (boolean) column denothing wether the user has receieved a particular kind of e-mail
A tinyint (boolean) column denothing wether the user has unsubscribed to the list

There are four indexes:
A unique index on the e-mail column
A unique on each of the boolean columns

Our problem is, any query executed on this table is extremely slow, despite the fact that we're not amending any data in the address column or adding any more. There are currently around 5.5 million rows in the database, which we expect to grow substantially.

The cardinality of the boolean indexes was being reported at None. When the unique index was deleted, the cardinality for all three indexes changed to 2 (which was the expected value). Executing COUNT(*) queries on these indexes are extremely slow, taking up to 4 minutes.

Any queries that change the structure of the table, or the indexes take in insane amount of time, typically 14 to 17 hours. The server is not busy on any other tasks, and is dedicated to the operation of this database.

Inserting a single row takes approximately 31 seconds.

We can see no reason why this table should be so slow in comparison to a similar table we operate, which has a very similar structure and volume of data.

Can you resolve this mystery for me????

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

HANDLE: Bastien
POSTED ON: Nov 20, 2003

REPLY:
Indexes play hell with boolean columns due to cardinality. Better to remove those indexes. As it is, the index isn't used anyway, because there are only 2 values in each of those columns so the whole table is scanned when a query is run and that query is likely to return more than 30% of the rows.

Also each insert requires that each of those indexes on the boolean field gets an insert as well. So any changes that affect the Db need to have the indexes played with as well. That is the bottleneck. Oracle provides something called a Bitmap index which is ideal for these kinds of situations, but MySql does not offer a similar type of index.

Solution is to kill the indexes on the boolean fields as they are not helping the speed of the application.

Bastien

Robbie2003

Thanks for your help, we have done as suggested and deleted the indexes on the boolean fields but unfortunatly this does not seem to have made any difference.

Can anyone give any further advice, we are really stuck with this one.


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

HANDLE: Bastien
POSTED ON: Nov 24, 2003

REPLY:
Hi again.

Try seeing if there are a lot of connections or that are idle that are limiting access to the server.

There might also be memory issues with the size of the instance etc. This is where an experienced DBA comes in handy. Check the docs for things like waits, concurrent connections, sizing etc.

Also try bouncing the DB, bring it down in a proper shutdown and then shutdown the server and restart both. This might kill some old connections that are hanging around as well as cleaning up the memory on the server

Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!
-------------------------------------------------------------------

HANDLE: vanekl
POSTED ON: Nov 25, 2003

REPLY:
There may be a lot of old deleted records in the table.
Try exporting the data, drop the table and recreate it,
and importing the data back in. If there were a lot of
deleted records they're gone now.

Also, how much RAM are you using? Your hard drive isn't
thrashing all the time you're performing an op on this table,
is it? Hard drive access is at least a 1000 times slower
than RAM access.

Finally, just a tip, count(primary_key_field) is faster than count(*)
because count(*) has to look at the entire record whereas
count(primary_key_field) only has to retrieve one column.

 

[vanekl]

Yes, yes, yes, RAM is definitely constraining this table.

Estimate the minimum RAM required to perform one
table insert quickly.

First, the Operating System. Not sure what your hosting
company is using, but if it is anything like Win XP then
that would mean about 190 MB is gone right off the start.
(This includes the MySQL database daemons.)

Next, you have to store the table's indexes into RAM.

The primary index takes up 55 MB of room on disk:
5.5 million records x 7 bytes per record [index] x 3/2
[indexes are on average filled only 66%] = 55 MB

Now, in order to perform an insert, not only must you have your primary index in memory,
but you also must update ALL other indexes on the table.
You have at least one more index, a unique index on email. This takes up an additional 71 MB.
So, to insert one record quickly, you have to have at least 190 MB (OS) + 55 MB (primary index) + 71 MB (email index) = 316 MB stored
in RAM.

So, the minimum RAM you need just for this one SQL op is
316 MB. I wouldn't even consider using less than 512 MB,
but I would prefer 1 GB for a table this large, especially
since you say this table is going to grow.

 

[Robbie2003]

Thanks Vanekl, I will let you know how we get on it's going to take us a few days to get a new server ordered and set-up + some time to transfer all the data over.

Once again thanks!

 

[Bastien]

Way more memory is needed as Vankel said. The load and concurrency will also affect the server and the ram, esp. if sessions are being used as more memory gets eaten up.

Ram is cheap. Have the host pack in as much as they can for the server. Way better to have too much memory than not enough

Bastien

Any one have a techie job in Toronto, I need to work...being laid off sucks!

 

[Robbie2003]

Thanks Bastien

I will let you know how we get on!