MySQL and 80,000+ users

[Mohamed Hosam]

I have a web site, bentelhalal.com, for matchmaking services (ASP + MySQL).
I have currently like 83,000 members. "musers" table consists of 65 fields
containing all info. Fields are mostly varchar and indices are made for like
8 fields.

I have an average of 30 concurrent users at any time. The hosting account
was very good in the beginning but MySQL started to hit the CPU a month
ago. Now, MySQL consumes 90% of the CPU for 10 seconds, 99% for 10 seconds
then releases it and so on. Even if I have a dedicated server, it will stay
the same because other sites on the server do not use MySQL.

I double checked the code.. All connections are closed properly. I use
rs.open query, conn, 3, 3. Always use GetRows to release MySQL fast.

Any suggestions how to keep the site up and running?

Regards,
Mohamed Hossam

[Chris Hohmann]

"Mohamed Hosam" <info@internetplus.biz> wrote in message
news:eWL34ppvDHA.2464@TK2MSFTNGP12.phx.gbl...

> I have a web site, bentelhalal.com, for matchmaking services (ASP +

MySQL).

> I have currently like 83,000 members. "musers" table consists of 65

fields

> containing all info. Fields are mostly varchar and indices are made

for like

> 8 fields.
>
> I have an average of 30 concurrent users at any time. The hosting

account

> was very good in the beginning but MySQL started to hit the CPU a

month

> ago. Now, MySQL consumes 90% of the CPU for 10 seconds, 99% for 10

seconds

> then releases it and so on. Even if I have a dedicated server, it will

stay

> the same because other sites on the server do not use MySQL.
>
> I double checked the code.. All connections are closed properly. I use
> rs.open query, conn, 3, 3. Always use GetRows to release MySQL fast.
>
> Any suggestions how to keep the site up and running?
>
> Regards,
> Mohamed Hossam

Please provide relevant code snippets, the SQL for the poorly performing
queries and associated DDL.
[url]http://aspfaq.com/etiquette.asp?id=5006[/url]

[Tom B]

I'm sure others will provide better insight, but my understanding is that
Microsoft's SQL Server will use as much processing power and memory that is
available to complete the current "job." I would assume that MySQL would be
the same.

I don't see how 90% usage is a problem, unless it's bogging down other
applications.

I don't know if MySQL has a product like Microsoft's Profiler, but if it
does than that would help as you could see when it is that your database is
working hardest.

HTH

Tom B




"Mohamed Hosam" <info@internetplus.biz> wrote in message
news:eWL34ppvDHA.2464@TK2MSFTNGP12.phx.gbl...

> I have a web site, bentelhalal.com, for matchmaking services (ASP +

MySQL).

> I have currently like 83,000 members. "musers" table consists of 65 fields
> containing all info. Fields are mostly varchar and indices are made for

like

> 8 fields.
>
> I have an average of 30 concurrent users at any time. The hosting account
> was very good in the beginning but MySQL started to hit the CPU a month
> ago. Now, MySQL consumes 90% of the CPU for 10 seconds, 99% for 10 seconds
> then releases it and so on. Even if I have a dedicated server, it will

stay

> the same because other sites on the server do not use MySQL.
>
> I double checked the code.. All connections are closed properly. I use
> rs.open query, conn, 3, 3. Always use GetRows to release MySQL fast.
>
> Any suggestions how to keep the site up and running?
>
> Regards,
> Mohamed Hossam
>
>

[Mohamed Hosam]

Thanks for your reply. I believe that the most difficult parts for MySQL to
perform are:

1- advanced search (when I allow visitors and members to search using like
30 fields, and
2- matchmaker. When member clicks and I find his match from like 40,000
members of the other gender. I open the database to get the specs he wants
then open it again to search for those specs at all other members.

I have noticed, when members became 60,000+, that "select count(ID) .." is
too slow. It even caused the server to crash so I had to remove it and use
the regular rs.recordcount.

Regards,
Mohamed

[Mohamed Hosam]

Thanks.. In fact, it causes the server to slow down (it works like 20% of
its regular speed) and crashes like every three days sometimes. Host has to
restart the server..

Regards,
Mohamed


"Tom B" <shuckle@NOSPAMhotmail.com> wrote in message
news:#1PgoGsvDHA.1576@TK2MSFTNGP11.phx.gbl...

> I'm sure others will provide better insight, but my understanding is that
> Microsoft's SQL Server will use as much processing power and memory that

is

> available to complete the current "job." I would assume that MySQL would

be

> the same.
>
> I don't see how 90% usage is a problem, unless it's bogging down other
> applications.
>
> I don't know if MySQL has a product like Microsoft's Profiler, but if it
> does than that would help as you could see when it is that your database

is

> working hardest.
>
> HTH
>
> Tom B
>
>
>
>
> "Mohamed Hosam" <info@internetplus.biz> wrote in message
> news:eWL34ppvDHA.2464@TK2MSFTNGP12.phx.gbl...

> > I have a web site, bentelhalal.com, for matchmaking services (ASP +

> MySQL).

> > I have currently like 83,000 members. "musers" table consists of 65

fields

> > containing all info. Fields are mostly varchar and indices are made for

> like

> > 8 fields.
> >
> > I have an average of 30 concurrent users at any time. The hosting

account

> > was very good in the beginning but MySQL started to hit the CPU a month
> > ago. Now, MySQL consumes 90% of the CPU for 10 seconds, 99% for 10

seconds

> > then releases it and so on. Even if I have a dedicated server, it will

> stay

> > the same because other sites on the server do not use MySQL.
> >
> > I double checked the code.. All connections are closed properly. I use
> > rs.open query, conn, 3, 3. Always use GetRows to release MySQL fast.
> >
> > Any suggestions how to keep the site up and running?
> >
> > Regards,
> > Mohamed Hossam
> >
> >

>
>

[Jeff Cochran]

On Tue, 9 Dec 2003 23:19:52 +0200, "Mohamed Hosam"
<info@internetplus.biz> wrote:

>I have a web site, bentelhalal.com, for matchmaking services (ASP + MySQL).
>I have currently like 83,000 members. "musers" table consists of 65 fields
>containing all info.

There's a definite opportunity for normalization available there...

Jeff

[Mark Schupp]

It might be time for a dedicated Database server.

Are your tables indexed in ways that support typical queries?

Does MySQL require that maintenance routines be run to optimize indices
(some DBMSs used to require and "update statistics" command periodically)?

Can you show some of the SQL Statements that are slow?

Check the MySQL documentation to see if you can optimize your queries (some
DBMSs used to be sensitive to the order of joins and where clause
statements).

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"Mohamed Hosam" <info@internetplus.biz> wrote in message
news:%23i87wivvDHA.2464@TK2MSFTNGP12.phx.gbl...

> Thanks.. In fact, it causes the server to slow down (it works like 20% of
> its regular speed) and crashes like every three days sometimes. Host has

to

> restart the server..
>
> Regards,
> Mohamed
>
>
> "Tom B" <shuckle@NOSPAMhotmail.com> wrote in message
> news:#1PgoGsvDHA.1576@TK2MSFTNGP11.phx.gbl...

> > I'm sure others will provide better insight, but my understanding is

that

> > Microsoft's SQL Server will use as much processing power and memory that

> is

> > available to complete the current "job." I would assume that MySQL

would

> be

> > the same.
> >
> > I don't see how 90% usage is a problem, unless it's bogging down other
> > applications.
> >
> > I don't know if MySQL has a product like Microsoft's Profiler, but if it
> > does than that would help as you could see when it is that your database

> is

> > working hardest.
> >
> > HTH
> >
> > Tom B
> >
> >
> >
> >
> > "Mohamed Hosam" <info@internetplus.biz> wrote in message
> > news:eWL34ppvDHA.2464@TK2MSFTNGP12.phx.gbl...

> > > I have a web site, bentelhalal.com, for matchmaking services (ASP +

> > MySQL).

> > > I have currently like 83,000 members. "musers" table consists of 65

> fields

> > > containing all info. Fields are mostly varchar and indices are made

for

> > like

> > > 8 fields.
> > >
> > > I have an average of 30 concurrent users at any time. The hosting

> account

> > > was very good in the beginning but MySQL started to hit the CPU a

month

> > > ago. Now, MySQL consumes 90% of the CPU for 10 seconds, 99% for 10

> seconds

> > > then releases it and so on. Even if I have a dedicated server, it will

> > stay

> > > the same because other sites on the server do not use MySQL.
> > >
> > > I double checked the code.. All connections are closed properly. I use
> > > rs.open query, conn, 3, 3. Always use GetRows to release MySQL fast.
> > >
> > > Any suggestions how to keep the site up and running?
> > >
> > > Regards,
> > > Mohamed Hossam
> > >
> > >

> >
> >

>
>

[Mohamed Hosam]

:) What do you suggest?

Mohamed


"Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
news:3fd82fb9.787472@msnews.microsoft.com...

> On Tue, 9 Dec 2003 23:19:52 +0200, "Mohamed Hosam"
> <info@internetplus.biz> wrote:
>

> >I have a web site, bentelhalal.com, for matchmaking services (ASP +

MySQL).

> >I have currently like 83,000 members. "musers" table consists of 65

fields

> >containing all info.

>
> There's a definite opportunity for normalization available there...
>
> Jeff

[Raymond D'Anjou \(raydan\)]

Have you tried using count(*) instead of count(ID) ?
Since all rows have an ID...

"Mohamed Hosam" <info@internetplus.biz> wrote in message
news:uyajFivvDHA.3220@tk2msftngp13.phx.gbl...

> Thanks for your reply. I believe that the most difficult parts for MySQL

to

> perform are:
>
> 1- advanced search (when I allow visitors and members to search using like
> 30 fields, and
> 2- matchmaker. When member clicks and I find his match from like 40,000
> members of the other gender. I open the database to get the specs he wants
> then open it again to search for those specs at all other members.
>
> I have noticed, when members became 60,000+, that "select count(ID) .." is
> too slow. It even caused the server to crash so I had to remove it and use
> the regular rs.recordcount.
>
> Regards,
> Mohamed
>
>

[Jeff Cochran]

>:) What do you suggest?

One table with all information about 83,000 members? Has to be a lot
of normalization that can occur. This is into database design, and
you may want to do some reading on it, but basically, any attribute
which may apply to more than one user could be in a table of its own.
Suppose for example you had typical address info of street, city,
state and zip. Since Zip Codes designate the city and state, all you
need is a zip code for the user address, and a separate table for zip
codes that contains the city and state for every zip code.

Jeff

>"Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
>news:3fd82fb9.787472@msnews.microsoft.com...

>> On Tue, 9 Dec 2003 23:19:52 +0200, "Mohamed Hosam"
>> <info@internetplus.biz> wrote:
>>

>> >I have a web site, bentelhalal.com, for matchmaking services (ASP +

>MySQL).

>> >I have currently like 83,000 members. "musers" table consists of 65

>fields

>> >containing all info.

>>
>> There's a definite opportunity for normalization available there...
>>
>> Jeff

>

[McKirahan]

"Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
news:3fd9fa86.183776436@msnews.microsoft.com...

> >:) What do you suggest?

>
> One table with all information about 83,000 members? Has to be a lot
> of normalization that can occur. This is into database design, and
> you may want to do some reading on it, but basically, any attribute
> which may apply to more than one user could be in a table of its own.
> Suppose for example you had typical address info of street, city,
> state and zip. Since Zip Codes designate the city and state, all you
> need is a zip code for the user address, and a separate table for zip
> codes that contains the city and state for every zip code.
>
> Jeff
>
>

> >"Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
> >news:3fd82fb9.787472@msnews.microsoft.com...

> >> On Tue, 9 Dec 2003 23:19:52 +0200, "Mohamed Hosam"
> >> <info@internetplus.biz> wrote:
> >>
> >> >I have a web site, bentelhalal.com, for matchmaking services (ASP +

> >MySQL).

> >> >I have currently like 83,000 members. "musers" table consists of 65

> >fields

> >> >containing all info.
> >>
> >> There's a definite opportunity for normalization available there...
> >>
> >> Jeff

While I agree that the poster may want to consider normalizing his database
I disagree with your example.

A zip code does not always identify the city someone lives in, just the
postal district serving them.

I know from experience as my zip code does not identify the city that I live
in!

A better example might be Membership Type.

[Jeff Cochran]

On Fri, 12 Dec 2003 17:49:36 GMT, "McKirahan" <News@McKirahan.com>
wrote:

>"Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
>news:3fd9fa86.183776436@msnews.microsoft.com...

>> >:) What do you suggest?

>>
>> One table with all information about 83,000 members? Has to be a lot
>> of normalization that can occur. This is into database design, and
>> you may want to do some reading on it, but basically, any attribute
>> which may apply to more than one user could be in a table of its own.
>> Suppose for example you had typical address info of street, city,
>> state and zip. Since Zip Codes designate the city and state, all you
>> need is a zip code for the user address, and a separate table for zip
>> codes that contains the city and state for every zip code.
>>
>> Jeff
>>
>>

>> >"Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
>> >news:3fd82fb9.787472@msnews.microsoft.com...
>> >> On Tue, 9 Dec 2003 23:19:52 +0200, "Mohamed Hosam"
>> >> <info@internetplus.biz> wrote:
>> >>
>> >> >I have a web site, bentelhalal.com, for matchmaking services (ASP +
>> >MySQL).
>> >> >I have currently like 83,000 members. "musers" table consists of 65
>> >fields
>> >> >containing all info.
>> >>
>> >> There's a definite opportunity for normalization available there...
>> >>
>> >> Jeff

>
>
>While I agree that the poster may want to consider normalizing his database
>I disagree with your example.
>
>A zip code does not always identify the city someone lives in, just the
>postal district serving them.
>
>I know from experience as my zip code does not identify the city that I live
>in!

All US zip codes map to a specifc city/state combination, which is the
official mailing designation. This is one of the most common forms of
normalization, since the US Post Office provides the zip code registry
database and a company need only plug in the database to its table to
have correct spelling of cities and states, as well as an accyrate
cross reference of zip codes, so nobody has an address of Salt lake
City, UT with a zip of 60609, which maps to Chicago, IL.

Note that the city you live in may not be the official city for your
mailing address. :)

Jeff

[Mohamed Hosam]

Thanks for explaining. The problem is that I do not have something with
which I can deal the way you advise. Here are some of the fields:

id
username
email
pass
name
sex
age
bday
bmonth
byear
address
city
country (in Arab world, we do not use ZIP code)
membership
headline1
headline2
eyecolor
bodytype
etc...

and most of them are repeated (sepcs he is looking for e.g. eyecolor1,
bodytype1, etc.)

Any help? :)

Mohamed

[Jeff Cochran]

On Sat, 13 Dec 2003 00:53:47 +0200, "Mohamed Hosam"
<info@internetplus.biz> wrote:

>Thanks for explaining. The problem is that I do not have something with
>which I can deal the way you advise. Here are some of the fields:
>
>id
>username
>email
>pass
>name
>sex
>age
>bday
>bmonth
>byear
>address
>city
>country (in Arab world, we do not use ZIP code)
>membership
>headline1
>headline2
>eyecolor
>bodytype
>etc...
>
>and most of them are repeated (sepcs he is looking for e.g. eyecolor1,
>bodytype1, etc.)
>
>Any help? :)

You'll want to ask in a database design group since we're straying off
topic, and do some reading on database design. But for example you
could have the following tables:

Table: Members Fields: MemberID(PK), UserName, Password, Membership
Table: Addresses Fields: MemberID(FK), Address, City, Country
Table: PersonalInfo Fields: MemberID(FK), FullName, Sex, BirthDate,
EyeColor, etc.

When you need to query a username and password, on a sign in, it's a
single table with four columns, presumably all you'd need to have
queried. Likewise, you wnat an address, you use the MemberID and the
address is there. For dates, you don't need BirthDay, BirthMonth,
BirthYear as separate fields, dates are easily manipulated in both ASP
and queries.

In all your queries, just return the information you need:

SELECT Members.Username, PersonalInfo.FullName FROM Members,
PersonalInfo WHERE Members.MemberID = "007"

Naturally, the JOIN language depends on the database, but this returns
a very small amount of data. Want a list of members names and
addresses, sorted by country? How about:

SELECT PersonalInfo.FullName,Addresses.Address, Addresses.City,
Addresses.Country FROM PersonalInfo, Addresses ORDER BY
Addresses.Country

Big databases aren't an issue, it's the amount of data you retireve
that is. Only get what you need, and break databases up into either
similar attributes for an entity, or separate entities.

Jeff