SQL Server ou Mysql with ASP ?

Discussion in 'ASP General' started by Fred, Jan 17, 2006.

  1. Fred

    Fred Guest

    Hello,

    Our website is currently developed in ASP/Mysql 4.
    The dedicated servers on which it is currently hosted arrive at saturation.

    Here is their configuration:
    - 1 server PIV 2,8Ghz 1GB RAM with IIS 5 on Windows 2000
    - 1 server Bi-xeon 3Ghz, 512 MB with MySQL 4 on Windows 2003

    The website makes approximately 10.000.000 of pages seen and 310.000
    visits each month (given by an external statistics tool), increasing by
    10% each month.

    The database is approximately 60 MB.
    I made, I think, most of the optimizations on the database (cache,
    indexes...).

    According to our current web hoster, this saturation is due to a bad
    management of connections between ASP and MySQL and recommends to us:
    1/ to put the site on Windows 2003/IIS 6.
    2/ to change our database from MySQL to SQL Server.
    3/ to change our PIV to a second Bi-xeon.

    What do you think of this solutions ?

    Thanks for your help.

    Fred
    Fred, Jan 17, 2006
    #1
    1. Advertising

  2. Fred

    Amit Guest

    Fred,

    You could actually optimize the code and improve the speeds a lot,
    depending on what your website does.

    Can you send more details about what kind of database interactivity
    happens on your website.
    Amit, Jan 17, 2006
    #2
    1. Advertising

  3. Fred

    Jevon Guest

    For such a small database, I'm surprised you're having so many issues - I
    run a number of sites on a P4 2.8 with the biggest (MySQL) database over 1Gb
    in size (with indexes) used by many of the sites, particularly the forums -
    the user information is shared across the others. We do have 2Gb of RAM
    however, and only about a third as many hits on average. I don't know if you
    need an entirely new server, the problem sounds more like the database
    server is the one getting saturated, so the webserver ends up sitting around
    waiting for it. What's the link between the two machines?

    My suggestion would be to up the amount of RAM on the database server -
    512Mb isn't really that much for a database server running on Win2k3. Next,
    look at how you've got MySQL configured - you mention optimising the
    database, does that include the ini file? Are you using transactions when
    you don't need to? Lastly, check that your site is opening and closing
    connections properly.

    I assume you've looked at:
    http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
    http://dev.mysql.com/doc/refman/5.0/en/optimization.html
    http://dev.mysql.com/doc/refman/5.0/en/show-status.html, and
    http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html
    ?
    That last one would be particularly helpful to show if there are any queries
    locking up or taking an excessive time to execute.

    Is the website a public one? If so, care to share the link?

    Jevon


    "Fred" <> wrote in message
    news:43ccbce9$0$21273$...
    > Hello,
    >
    > Our website is currently developed in ASP/Mysql 4.
    > The dedicated servers on which it is currently hosted arrive at
    > saturation.
    >
    > Here is their configuration:
    > - 1 server PIV 2,8Ghz 1GB RAM with IIS 5 on Windows 2000
    > - 1 server Bi-xeon 3Ghz, 512 MB with MySQL 4 on Windows 2003
    >
    > The website makes approximately 10.000.000 of pages seen and 310.000
    > visits each month (given by an external statistics tool), increasing by
    > 10% each month.
    >
    > The database is approximately 60 MB.
    > I made, I think, most of the optimizations on the database (cache,
    > indexes...).
    >
    > According to our current web hoster, this saturation is due to a bad
    > management of connections between ASP and MySQL and recommends to us:
    > 1/ to put the site on Windows 2003/IIS 6.
    > 2/ to change our database from MySQL to SQL Server.
    > 3/ to change our PIV to a second Bi-xeon.
    >
    > What do you think of this solutions ?
    >
    > Thanks for your help.
    >
    > Fred
    >
    Jevon, Jan 17, 2006
    #3
  4. Fred

    Fred Guest

    Thanks for your help, here is more informations about my configuration.

    Jevon a écrit :
    > waiting for it. What's the link between the two machines?


    They are connected directly via a private LAN (192.168.0.x IP adresses)

    >
    > My suggestion would be to up the amount of RAM on the database server -
    > 512Mb isn't really that much for a database server running on Win2k3. Next,
    > look at how you've got MySQL configured - you mention optimising the
    > database, does that include the ini file? Are you using transactions when
    > you don't need to? Lastly, check that your site is opening and closing
    > connections properly.


    The server uses about 350 MB of RAM.

    Here is my my.ini file :
    max_connections=100
    query_cache_type=1
    query_cache_limit=10M
    query_cache_size=50M
    table_cache=100
    tmp_table_size=5M
    thread_cache_size=8
    myisam_max_sort_file_size=100G
    myisam_max_extra_sort_file_size=100G
    myisam_sort_buffer_size=8M
    key_buffer_size=16M
    read_buffer_size=64K
    read_rnd_buffer_size=256K
    sort_buffer_size=212K
    >



    > I assume you've looked at:
    > http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
    > http://dev.mysql.com/doc/refman/5.0/en/optimization.html
    > http://dev.mysql.com/doc/refman/5.0/en/show-status.html, and
    > http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html
    > ?
    > That last one would be particularly helpful to show if there are any queries
    > locking up or taking an excessive time to execute.>


    Yes, it's the pages I used to optimize MySQL.
    For the cache, is it necessary to specify "SQL_CACHE" for SELECT queries ?

    > Is the website a public one? If so, care to share the link?


    here is the link : http://www.agriaffaires.co.uk
    >


    Fred
    Fred, Jan 17, 2006
    #4
  5. Fred

    Jevon Guest

    Answers inline...

    "Fred" <> wrote in message
    news:43ccde75$0$19688$...
    > Thanks for your help, here is more informations about my configuration.
    >
    > Jevon a écrit :
    >> waiting for it. What's the link between the two machines?

    >
    > They are connected directly via a private LAN (192.168.0.x IP adresses)
    >
    >>
    >> My suggestion would be to up the amount of RAM on the database server -
    >> 512Mb isn't really that much for a database server running on Win2k3.
    >> Next, look at how you've got MySQL configured - you mention optimising
    >> the database, does that include the ini file? Are you using transactions
    >> when you don't need to? Lastly, check that your site is opening and
    >> closing connections properly.

    >
    > The server uses about 350 MB of RAM.


    Yeah, we find that it always leaves some spare. If you can afford it for
    testing, I would recommend trying an extra 512mb - if it helps, it's cheaper
    than a whole new server :)


    >
    > Here is my my.ini file :
    > max_connections=100
    > query_cache_type=1
    > query_cache_limit=10M
    > query_cache_size=50M
    > table_cache=100
    > tmp_table_size=5M
    > thread_cache_size=8
    > myisam_max_sort_file_size=100G
    > myisam_max_extra_sort_file_size=100G
    > myisam_sort_buffer_size=8M
    > key_buffer_size=16M
    > read_buffer_size=64K
    > read_rnd_buffer_size=256K
    > sort_buffer_size=212K


    You could probably up your max_connections. It's set to over 1000 on ours
    and is working ok. Try 400 to start with, see if it has any affect. I assume
    you read the following, taken from the "default" ini file, talking about the
    query_cache values?
    # Query cache is used to cache SELECT results and later return them
    # without actual executing the same query once again. Having the query
    # cache enabled may result in significant speed improvements, if your
    # have a lot of identical queries and rarely changing tables. See the
    # "Qcache_lowmem_prunes" status variable to check if the current value
    # is high enough for your load.
    # Note: In case your tables change very often or if your queries are
    # textually different every time, the query cache may result in a
    # slowdown instead of a performance improvement.
    On our server the cache size is 128M, for a 60mb database you might find 92M
    would help, but you should read up on them though on mysql.com. Also, our
    key_buffer_size is 1024M - again, read up on this, but increasing it may
    help.
    All of your buffers seem a bit low really. Have you had a look at the values
    in the my-large.ini file? They're higher than your values for most if not
    all of them, and that file is "... for a large system with memory = 512M
    where the system runs mainly MySQL". Although the large probably refers to
    data, it would also be optimised for increased users.
    You might find that the low values are causing your server to read/write
    from the disk drive more than is necessary. Try looking at some of the
    performance monitors within Windows to see if that's the case. For a
    database of 60Mb I would expect it to cache quite a lot in memory, but your
    config values don't seem as though they'd allow this.

    >>

    >
    >
    >> I assume you've looked at:
    >> http://dev.mysql.com/doc/refman/5.0/en/query-cache.html
    >> http://dev.mysql.com/doc/refman/5.0/en/optimization.html
    >> http://dev.mysql.com/doc/refman/5.0/en/show-status.html, and
    >> http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html
    >> ?
    >> That last one would be particularly helpful to show if there are any
    >> queries locking up or taking an excessive time to execute.>

    >
    > Yes, it's the pages I used to optimize MySQL.
    > For the cache, is it necessary to specify "SQL_CACHE" for SELECT queries ?


    Not as far as I'm aware, but the structure and processes of your code could
    also be playing a (large) part, as Amit suggests.
    For example, the page
    http://www.agriaffaires.co.uk/rubriques/318-Tractors.asp has a long list of
    page numbers - how is the total number calculated?
    And on the front page, how is the list of categories retrieved? Assuming you
    are using a recent version of MySQL I believe you could achieve the list
    with one query, but depending on the code it could be doing it in 3 or more.

    >
    >> Is the website a public one? If so, care to share the link?

    >
    > here is the link : http://www.agriaffaires.co.uk
    >>

    >
    > Fred
    Jevon, Jan 17, 2006
    #5
  6. Fred

    Fred Guest

    Jevon a écrit :
    > You could probably up your max_connections. It's set to over 1000 on ours
    > and is working ok. Try 400 to start with, see if it has any affect.



    In Mysql Administraor, I have an average of 25 connections. Is it
    necessary to set max_connections to 400 in that case ?

    > I assume you read the following, taken from the "default" ini file, talking about the
    > query_cache values?
    > # Note: In case your tables change very often or if your queries are
    > # textually different every time, the query cache may result in a
    > # slowdown instead of a performance improvement.


    It seems to be the case for me. We have at least 500 changes par day in
    the main table.

    I'll try to change these values and see what happends.

    > For example, the page
    > http://www.agriaffaires.co.uk/rubriques/318-Tractors.asp has a long list of
    > page numbers - how is the total number calculated?
    > And on the front page, how is the list of categories retrieved? Assuming you
    > are using a recent version of MySQL I believe you could achieve the list
    > with one query, but depending on the code it could be doing it in 3 or more.


    In fact, the list on the main page (and many other parts like footers)
    are html pages generated from the database once a day.

    For the list, I have only one request, using LIMIT, SQL_CALC_FOUND_ROWS
    And "SELECT FOUND_ROWS()" to retrieve the total number of ads.

    I use MySQL 4.1.15.
    Will MySQL 5 or SQL Server help me to solve my problems ?

    Thanks,

    Fred
    Fred, Jan 17, 2006
    #6
  7. Fred

    Jevon Guest

    With that information, no, I don't think upping the maximum connections
    would have no noticeable affect.

    From what you've said, regarding generating pages once a day, I'm not
    convinced your database server is entirely the problem, as doing that would
    reduce the load on the database server quite a lot. Do you know how your
    host arrived at the conclusion that "this saturation is due to a bad
    management of connections between ASP and MySQL" ?

    I don't believe changing the database server would help, but I've just read
    the following on
    http://dev.mysql.com/doc/refman/5.0/en/information-functions.html :

    The above reads "If the preceding SELECT statement does not include the
    SQL_CALC_FOUND_ROWS option, then FOUND_ROWS() may return a different result
    when LIMIT is used than when it is not."
    The word "may" seems to be an understatement. Everytime I run a SELECT with
    LIMIT, FOUND_ROWS() returns whatever number I put after LIMIT. E.g. after
    SELECT * FROM table LIMIT 50
    FOUND_ROWS() will return 50.
    But if I do
    SELECT * FROM table LIMIT 10, 10,
    FOUND_ROWS() will return 20 though. :)
    This is annoying, since I need this information from a 20,000-row table
    fulltext search, and a 0,008 second search then suddenly takes about 20
    seconds... Haven't found a workaround yet...

    (This is the comment by [name withheld] on January 2 2006 7:52pm)

    That to me suggests that SQL_CALC_FOUND_ROWS might be quite inefficient. I
    assume in your query that the LIMIT value is 30, and you have a query
    similar to:
    SELECT SQL_CALC_FOUND_ROWS AdvertID, AdvertTitle, AdvertPrice FROM Adverts
    WHERE AdvertParentCategory = 318 LIMIT 30
    I would suggest _testing_ removing SQL_CALC_FOUND_ROWS from here, and
    instead of "SELECT FOUND_ROWS()" using:
    SELECT COUNT(*) FROM Adverts WHERE AdvertParentCategory = 318

    (I know yours is more complex as it also deals with child categories, but
    this should give you an idea what I mean.)

    However, I don't know if this will have much affect as I'm still not sure
    exactly what the problem is. As I've said, it could be to do with the
    webserver, and we might be going down the wrong path. Have you used the
    performance counters to log both hard disk usage (accesses, throughput, etc)
    and CPU usage over the course of a day or two for both machines? If not, I
    would suggest you do this and see if maybe it's actually your harddrives
    getting saturated, in which case RAID rather than more CPU would help.

    If you have msn messenger (or ICQ) and would like to talk "in person", we
    might be able to narrow down the problem a bit quicker! Email me if you want
    to take this route, and we can discuss it.
    newsgroupsdotspamatjevonfarrdotcom, replacing the 2 "dot"s and "at" with the
    correct email figures ("." and "@").

    Jevon


    "Fred" <> wrote in message
    news:43cd1ef3$0$6671$...
    > Jevon a écrit :
    >> You could probably up your max_connections. It's set to over 1000 on ours
    >> and is working ok. Try 400 to start with, see if it has any affect.

    >
    >
    > In Mysql Administraor, I have an average of 25 connections. Is it
    > necessary to set max_connections to 400 in that case ?
    >
    >> I assume you read the following, taken from the "default" ini file,
    >> talking about the query_cache values?
    >> # Note: In case your tables change very often or if your queries are
    >> # textually different every time, the query cache may result in a
    >> # slowdown instead of a performance improvement.

    >
    > It seems to be the case for me. We have at least 500 changes par day in
    > the main table.
    >
    > I'll try to change these values and see what happends.
    >
    >> For example, the page
    >> http://www.agriaffaires.co.uk/rubriques/318-Tractors.asp has a long list
    >> of page numbers - how is the total number calculated?
    >> And on the front page, how is the list of categories retrieved? Assuming
    >> you are using a recent version of MySQL I believe you could achieve the
    >> list with one query, but depending on the code it could be doing it in 3
    >> or more.

    >
    > In fact, the list on the main page (and many other parts like footers) are
    > html pages generated from the database once a day.
    >
    > For the list, I have only one request, using LIMIT, SQL_CALC_FOUND_ROWS
    > And "SELECT FOUND_ROWS()" to retrieve the total number of ads.
    >
    > I use MySQL 4.1.15.
    > Will MySQL 5 or SQL Server help me to solve my problems ?
    >
    > Thanks,
    >
    > Fred
    Jevon, Jan 17, 2006
    #7
  8. Fred

    Jevon Guest

    Small bit of clarification:

    > That to me suggests that SQL_CALC_FOUND_ROWS might be quite inefficient. I
    > assume in your query that the LIMIT value is 30, and you have a query
    > similar to:
    > SELECT SQL_CALC_FOUND_ROWS AdvertID, AdvertTitle, AdvertPrice FROM Adverts
    > WHERE AdvertParentCategory = 318 LIMIT 30
    > I would suggest _testing_ removing SQL_CALC_FOUND_ROWS from here, and
    > instead of "SELECT FOUND_ROWS()" using:
    > SELECT COUNT(*) FROM Adverts WHERE AdvertParentCategory = 318


    If you have an ORDER BY clause, keep this in the first query, but remove it
    for the second as there's no point sorting if you just want to know how many
    results there are :)

    Jevon


    "Jevon" <> wrote in message
    news:...
    > With that information, no, I don't think upping the maximum connections
    > would have no noticeable affect.
    >
    > From what you've said, regarding generating pages once a day, I'm not
    > convinced your database server is entirely the problem, as doing that
    > would reduce the load on the database server quite a lot. Do you know how
    > your host arrived at the conclusion that "this saturation is due to a bad
    > management of connections between ASP and MySQL" ?
    >
    > I don't believe changing the database server would help, but I've just
    > read the following on
    > http://dev.mysql.com/doc/refman/5.0/en/information-functions.html :
    >
    > The above reads "If the preceding SELECT statement does not include the
    > SQL_CALC_FOUND_ROWS option, then FOUND_ROWS() may return a different
    > result when LIMIT is used than when it is not."
    > The word "may" seems to be an understatement. Everytime I run a SELECT
    > with LIMIT, FOUND_ROWS() returns whatever number I put after LIMIT. E.g.
    > after
    > SELECT * FROM table LIMIT 50
    > FOUND_ROWS() will return 50.
    > But if I do
    > SELECT * FROM table LIMIT 10, 10,
    > FOUND_ROWS() will return 20 though. :)
    > This is annoying, since I need this information from a 20,000-row table
    > fulltext search, and a 0,008 second search then suddenly takes about 20
    > seconds... Haven't found a workaround yet...
    >
    > (This is the comment by [name withheld] on January 2 2006 7:52pm)
    >
    > That to me suggests that SQL_CALC_FOUND_ROWS might be quite inefficient. I
    > assume in your query that the LIMIT value is 30, and you have a query
    > similar to:
    > SELECT SQL_CALC_FOUND_ROWS AdvertID, AdvertTitle, AdvertPrice FROM Adverts
    > WHERE AdvertParentCategory = 318 LIMIT 30
    > I would suggest _testing_ removing SQL_CALC_FOUND_ROWS from here, and
    > instead of "SELECT FOUND_ROWS()" using:
    > SELECT COUNT(*) FROM Adverts WHERE AdvertParentCategory = 318
    >
    > (I know yours is more complex as it also deals with child categories, but
    > this should give you an idea what I mean.)
    >
    > However, I don't know if this will have much affect as I'm still not sure
    > exactly what the problem is. As I've said, it could be to do with the
    > webserver, and we might be going down the wrong path. Have you used the
    > performance counters to log both hard disk usage (accesses, throughput,
    > etc) and CPU usage over the course of a day or two for both machines? If
    > not, I would suggest you do this and see if maybe it's actually your
    > harddrives getting saturated, in which case RAID rather than more CPU
    > would help.
    >
    > If you have msn messenger (or ICQ) and would like to talk "in person", we
    > might be able to narrow down the problem a bit quicker! Email me if you
    > want to take this route, and we can discuss it.
    > newsgroupsdotspamatjevonfarrdotcom, replacing the 2 "dot"s and "at" with
    > the correct email figures ("." and "@").
    >
    > Jevon
    >
    >
    > "Fred" <> wrote in message
    > news:43cd1ef3$0$6671$...
    >> Jevon a écrit :
    >>> You could probably up your max_connections. It's set to over 1000 on
    >>> ours and is working ok. Try 400 to start with, see if it has any affect.

    >>
    >>
    >> In Mysql Administraor, I have an average of 25 connections. Is it
    >> necessary to set max_connections to 400 in that case ?
    >>
    >>> I assume you read the following, taken from the "default" ini file,
    >>> talking about the query_cache values?
    >>> # Note: In case your tables change very often or if your queries are
    >>> # textually different every time, the query cache may result in a
    >>> # slowdown instead of a performance improvement.

    >>
    >> It seems to be the case for me. We have at least 500 changes par day in
    >> the main table.
    >>
    >> I'll try to change these values and see what happends.
    >>
    >>> For example, the page
    >>> http://www.agriaffaires.co.uk/rubriques/318-Tractors.asp has a long list
    >>> of page numbers - how is the total number calculated?
    >>> And on the front page, how is the list of categories retrieved? Assuming
    >>> you are using a recent version of MySQL I believe you could achieve the
    >>> list with one query, but depending on the code it could be doing it in 3
    >>> or more.

    >>
    >> In fact, the list on the main page (and many other parts like footers)
    >> are html pages generated from the database once a day.
    >>
    >> For the list, I have only one request, using LIMIT, SQL_CALC_FOUND_ROWS
    >> And "SELECT FOUND_ROWS()" to retrieve the total number of ads.
    >>
    >> I use MySQL 4.1.15.
    >> Will MySQL 5 or SQL Server help me to solve my problems ?
    >>
    >> Thanks,
    >>
    >> Fred

    >
    >
    Jevon, Jan 17, 2006
    #8
  9. Fred

    Fred Guest

    Jevon a écrit :
    > Small bit of clarification:
    >
    >> That to me suggests that SQL_CALC_FOUND_ROWS might be quite inefficient. I
    >> assume in your query that the LIMIT value is 30, and you have a query
    >> similar to:

    >


    I testes both solutions and the result is the same in both case.

    In fact, It seems that our server need to be upgraded to a better one.

    I also tried MySQL 5, and with some requests, I can decrease the
    execution time by 20%.

    Thanks for your help.

    Fred
    Fred, Jan 19, 2006
    #9
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?UmV6YQ==?=
    Replies:
    3
    Views:
    17,934
    Carlos Barini
    Jun 7, 2004
  2. angel monson

    mySql vs. sql server in asp.net hoster

    angel monson, Apr 17, 2005, in forum: ASP .Net
    Replies:
    8
    Views:
    667
    Lucas Tam
    Apr 17, 2005
  3. Brian Ablaza
    Replies:
    1
    Views:
    121
    zakifataya
    Feb 27, 2006
  4. julian
    Replies:
    8
    Views:
    449
    Avatar
    Apr 6, 2006
  5. Allen Marshall
    Replies:
    1
    Views:
    192
    Gunnar Hjalmarsson
    Jan 24, 2004
Loading...

Share This Page