Professional Web Applications Themes

Help me to limit queries or data bandwith on MySql - MySQL

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 ...

  1. #1

    Default Help me to limit queries or data bandwith on MySql

    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 Guest

  2. #2

    Default Re: Help me to limit queries or data bandwith on MySql

    mig wrote: 

    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.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Help me to limit queries or data bandwith on MySql

    On May 17, 11:06 pm, Jerry Stuckle <net> wrote:
     

    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

    mig Guest

  4. #4

    Default Re: Help me to limit queries or data bandwith on MySql

    On 18 May, 03:51, mig <com> wrote: 


    >
    > 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[/ref]

    How about FULLTEXT searching instead?

    Captain Guest

  5. #5

    Default Re: Help me to limit queries or data bandwith on MySql

    mig wrote: 
    >
    > 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
    >[/ref]

    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.
    net
    ==================
    Jerry Guest

  6. #6

    Default Re: Help me to limit queries or data bandwith on MySql

    On May 18, 5:32 am, Captain Paralytic <com> wrote:
     

    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 Guest

  7. #7

    Default Re: Help me to limit queries or data bandwith on MySql

    mig wrote: 
    >
    > 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.[/ref]

    This sort of search is what fulltext was made for.


    Paul Guest

  8. #8

    Default Re: Help me to limit queries or data bandwith on MySql

    On May 18, 5:07 pm, "Paul Lautman" <com>
    wrote:
     

    After yzing 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


    mig Guest

  9. #9

    Default Re: Help me to limit queries or data bandwith on MySql

    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 <com> wrote: 
    >
    > After yzing 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[/ref]


    cbmeeks Guest

  10. #10

    Default Re: Help me to limit queries or data bandwith on MySql

    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.

    christopher@dailycrossword.com Guest

Similar Threads

  1. Mysql limit for users
    By PI in forum MySQL
    Replies: 8
    Last Post: October 19th, 04:08 PM
  2. Caching Queries with CF MX 6.1 and MySQL 4.1
    By bluespoon in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: May 20th, 08:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139