Hello people, I use hosting services, then I don't have control over
MySql server parameters. Worst part is my domain is reversed with
several other hundreds domains in the same sever.

Hosting warned to me my site is demanding so much MySql searches that
probably they will be obligued to cut MySql from my site to avoid
chaos with the other domains. I've optimized the queries all possible
ways, creating indexes and just calling the necessary fields to
display, but even so qtty of users that visit my page increases day
after day and I am concearned about.

I am seeking some way for preventing searches when MySql reachs some
critical limit (which I don't know what it could be), and then send
the user a msg 'please, try again', or else 'please, try to use this
system by nigh when traffic is lower'.

I don't know what parameters I have at disposal to measure / control
traffic, maybe MySql has some command telling me how many records are
being requested by all users in this table at this moment?

If this command exists, then it would possible to check 'if #records-
hold > 100.000 then echo 'please try again later'.

Any idea will be welcome.

Thanks very much for your help

Mig

mig wrote:
> Hello people, I use hosting services, then I don't have control over
> MySql server parameters. Worst part is my domain is reversed with
> several other hundreds domains in the same sever.
>
> Hosting warned to me my site is demanding so much MySql searches that
> probably they will be obligued to cut MySql from my site to avoid
> chaos with the other domains. I've optimized the queries all possible
> ways, creating indexes and just calling the necessary fields to
> display, but even so qtty of users that visit my page increases day
> after day and I am concearned about.
>
> I am seeking some way for preventing searches when MySql reachs some
> critical limit (which I don't know what it could be), and then send
> the user a msg 'please, try again', or else 'please, try to use this
> system by nigh when traffic is lower'.
>
> I don't know what parameters I have at disposal to measure / control
> traffic, maybe MySql has some command telling me how many records are
> being requested by all users in this table at this moment?
>
> If this command exists, then it would possible to check 'if #records-
> hold > 100.000 then echo 'please try again later'.
>
> Any idea will be welcome.
>
> Thanks very much for your help
>
> Mig
>

Mig,

Get a better hosting company. Unless you're doing something really
strange, it sounds like yours is overselling their servers.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On May 17, 11:06 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:

> Mig,
>
> Get a better hosting company. Unless you're doing something really
> strange, it sounds like yours is overselling their servers.
>
> --

Hello Jerry, I think hosting serv. are right, I know where the problem
is, but I can't solve it, so I am trying to cut some transactions
until definitive solution found. I think other hosting serv. also will
face the same problem. Even for a customized server , it is just a
matter of time until problem to appear again.

My problem is because I have a table with some 500.000 (and growing)
records plenty of brand names and product models / serial # / codes
and so, and my visitors need to browse into these data to find items.

So they should search into 'sub string' scheme on table description
field. Lets say I have a record 'camcorder sony model 73299/sn w/
shuttle', then user would search 'sony cam 299' and I must show him/
her that record.

When I created this scenario some year ago I had very few products and
hits by day, but now they grew to ten of thousands searches every day.

I couldn't solve this problem usind indexes because sub-strings
searching into words, now I am trying other approaches, but all
solutions are difficult or expensive (physical space) , so I need a
time to think about, and need for a while to limit accesses to avoid
database cut.

Thanks again

Mig

On 18 May, 03:51, mig <m02041...@yahoo.com> wrote:
> On May 17, 11:06 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>
> > Mig,
>
> > Get a better hosting company. Unless you're doing something really
> > strange, it sounds like yours is overselling their servers.
>
> > --
>
> Hello Jerry, I think hosting serv. are right, I know where the problem
> is, but I can't solve it, so I am trying to cut some transactions
> until definitive solution found. I think other hosting serv. also will
> face the same problem. Even for a customized server , it is just a
> matter of time until problem to appear again.
>
> My problem is because I have a table with some 500.000 (and growing)
> records plenty of brand names and product models / serial # / codes
> and so, and my visitors need to browse into these data to find items.
>
> So they should search into 'sub string' scheme on table description
> field. Lets say I have a record 'camcorder sony model 73299/sn w/
> shuttle', then user would search 'sony cam 299' and I must show him/
> her that record.
>
> When I created this scenario some year ago I had very few products and
> hits by day, but now they grew to ten of thousands searches every day.
>
> I couldn't solve this problem usind indexes because sub-strings
> searching into words, now I am trying other approaches, but all
> solutions are difficult or expensive (physical space) , so I need a
> time to think about, and need for a while to limit accesses to avoid
> database cut.
>
> Thanks again
>
> Mig

How about FULLTEXT searching instead?

mig wrote:
> On May 17, 11:06 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>
>> Mig,
>>
>> Get a better hosting company. Unless you're doing something really
>> strange, it sounds like yours is overselling their servers.
>>
>> --
>
> Hello Jerry, I think hosting serv. are right, I know where the problem
> is, but I can't solve it, so I am trying to cut some transactions
> until definitive solution found. I think other hosting serv. also will
> face the same problem. Even for a customized server , it is just a
> matter of time until problem to appear again.
>
> My problem is because I have a table with some 500.000 (and growing)
> records plenty of brand names and product models / serial # / codes
> and so, and my visitors need to browse into these data to find items.
>
> So they should search into 'sub string' scheme on table description
> field. Lets say I have a record 'camcorder sony model 73299/sn w/
> shuttle', then user would search 'sony cam 299' and I must show him/
> her that record.
>
> When I created this scenario some year ago I had very few products and
> hits by day, but now they grew to ten of thousands searches every day.
>
> I couldn't solve this problem usind indexes because sub-strings
> searching into words, now I am trying other approaches, but all
> solutions are difficult or expensive (physical space) , so I need a
> time to think about, and need for a while to limit accesses to avoid
> database cut.
>
> Thanks again
>
> Mig
>

OK, in this case maybe your hosting company isn't overselling and you
are putting a big load on the server (normally when a host oversells,
the first thing they do when they get performance complaints is start
blaming the users for using too many resources).

But now it looks like you are needing a lot of resources. As Paul
indicates, a full text search might be faster for now but for how long?

As as you indicate, you can delay the problem. In this case you might
look for a VPS - it's cheaper than a dedicated server and you won't get
as many users as on a shared hosting plan. Eventually you may need to
go to a dedicated server.


--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On May 18, 5:32 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:

>
> How about FULLTEXT searching instead?- Hide quoted text -
>

Hello Captain, thanks for your idea, I am currently using INSTRING
command to locate these records.

Do you think FULLTEXT have better performance?, I can't figure a trick
to solve this problem other than reading every record and then
comparing description against sub-string(s).

I will check your idea, and hope some good luck to measure performance
accurately, but probably I won't escape from dedicated server.

mig wrote:
> On May 18, 5:32 am, Captain Paralytic <paul_laut...@yahoo.com> wrote:
>
>>
>> How about FULLTEXT searching instead?- Hide quoted text -
>>
>
> Hello Captain, thanks for your idea, I am currently using INSTRING
> command to locate these records.
>
> Do you think FULLTEXT have better performance?, I can't figure a trick
> to solve this problem other than reading every record and then
> comparing description against sub-string(s).
>
> I will check your idea, and hope some good luck to measure performance
> accurately, but probably I won't escape from dedicated server.

This sort of search is what fulltext was made for.

On May 18, 5:07 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:

>
> This sort ofsearchis whatfulltextwas made for.-

After analyzing all options FULLTEXT offers, I concluded it will not
work for me. Apparently indexing fulltext first breaks entire text
into words separated by spaces or special chars, then indexes these
words separately, so when you search strings into full text, it first
goes to these words to find the text (and rows).

Problem is I need a 'really full text' search, lets say, I have the
word 'database' and user wants a record containing 'tabas' then I must
list this record for him, because users try locate bits of code for
products, and they don't exactly know how these codes comes from
seller.

In the basis MATCH command was built, it only allows pos-wilcard (for
example 'datab*' but not in-between wilcards '*tabas*' , because
isolated words of text are individually indexed in some way, so a pre-
wilcard never will work into index.

My next goal is to check if %LIKE% command is faster than INSTR ,
since %LIKE$ makes just what I want in the same way INSTR does.

Case I am wrong, please let me knonw, anyway I liked your comments and
suggestions

Mig

I really think you need a better host with more bandwidth. Even a VPS
or dedicated server could have the same issues if you aren't allotted
much bandwidth. So, what kind of growth are you experiencing per
day?

Also, check out Amazon's EC2.

http://www.signaldev.com



On May 20, 8:27 am, mig <m02041...@yahoo.com> wrote:
> On May 18, 5:07 pm, "Paul Lautman" <paul.laut...@btinternet.com>
> wrote:
>
>
>
> > This sort ofsearchis whatfulltextwas made for.-
>
> After analyzing all options FULLTEXT offers, I concluded it will not
> work for me. Apparently indexing fulltext first breaks entire text
> into words separated by spaces or special chars, then indexes these
> words separately, so when you search strings into full text, it first
> goes to these words to find the text (and rows).
>
> Problem is I need a 'really full text' search, lets say, I have the
> word 'database' and user wants a record containing 'tabas' then I must
> list this record for him, because users try locate bits of code for
> products, and they don't exactly know how these codes comes from
> seller.
>
> In the basis MATCH command was built, it only allows pos-wilcard (for
> example 'datab*' but not in-between wilcards '*tabas*' , because
> isolated words of text are individually indexed in some way, so a pre-
> wilcard never will work into index.
>
> My next goal is to check if %LIKE% command is faster than INSTR ,
> since %LIKE$ makes just what I want in the same way INSTR does.
>
> Case I am wrong, please let me knonw, anyway I liked your comments and
> suggestions
>
> Mig

look at cacheing results -- maybe there is an optimization possible.
Frankly, I would expect you to be bandwidth limited before you are
query limited. ask your ISP specifically how much is too much. At
the very least you can take that # to your new ISP.

One thought -- you can run all your connections through a gate (an
intermediary object that hands out database connections). You can
count your connections, throttle them to so many a minute or
whatever. Similarly you can cordon off the pages that perform
searches, so they are only available at so many a second.

You might look for bots abusing your service.

Luck.