SQL Server ou Mysql with ASP ?

[Fred]

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

 

[Amit]

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.

 

[Jevon]

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]

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

 

[Jevon]

Answers inline...

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

Jevon a écrit :

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


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.

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.

 

[Fred]

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]

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

 

[Jevon]

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

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

Jevon a écrit :


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


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.


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]

Jevon a écrit :

Small bit of clarification:

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