SEO

I have a mysql-based dynamic web app that's slowing down at an unacceptable
rate as my numbers of users climbs. Can anyone help me figure out how to
tune the database so that it runs faster? I've looked at the my.ini file
(I'm on windows server) and beefed up most of the numbers, but don't see any
change after rebooting my server. I'm relatively experienced at creating
mysql DBs, but a relative newbie when it comes to administering mysql -- so
I don't really know where to start. I have already tuned the application to
minimize the number of calls to the database, so that base is covered.
Thanks for helping out.

weheh wrote:
>I have a mysql-based dynamic web app that's slowing down at an
> unacceptable rate as my numbers of users climbs. Can anyone help me
> figure out how to tune the database so that it runs faster? I've
> looked at the my.ini file (I'm on windows server) and beefed up most
> of the numbers, but don't see any change after rebooting my server.
> I'm relatively experienced at creating mysql DBs, but a relative
> newbie when it comes to administering mysql -- so I don't really know
> where to start. I have already tuned the application to minimize the
> number of calls to the database, so that base is covered. Thanks for
> helping out.

Since you have told us almost nothing about the application, there is not a
great deal we can do to advise you.

What makes you so sure that the bottleneck is mysql?

To me tuning a database application means looking at the data and the
queries to ensure that I have that part fully optimised and indexed
correctly.

weheh wrote:
> I have a mysql-based dynamic web app that's slowing down at an unacceptable
> rate as my numbers of users climbs. Can anyone help me figure out how to
> tune the database so that it runs faster? I've looked at the my.ini file
> (I'm on windows server) and beefed up most of the numbers, but don't see any
> change after rebooting my server. I'm relatively experienced at creating
> mysql DBs, but a relative newbie when it comes to administering mysql -- so
> I don't really know where to start. I have already tuned the application to
> minimize the number of calls to the database, so that base is covered.
> Thanks for helping out.
>
>


You are on a windows box and you wonder why your app slows down when you
add too many users?

Your next step is to run explain plans for the most called sql
statements to ensure you have them indexed properly. If you have a few
users doing full table scans - that may not be too bad - but increase
that and depending on the size of the query, you can bring down a peecee
pretty easily. I once was called to help fix a problem where a web page
submitted 5 full table scans returning over 400Mb for each query to a
mainframe database. It crippled the mainframe and took more than 8 hrs
for it to recover.

After doing this - move the database to a server that can handle it.
Just because it can run on a platform, doesn't mean that it should. And
ALL of the Windows OS's are bloated and perform very poorly regardless
of OS tuning.

No disagreement with any of your observations about the PC platform, but I'm
stuck there for the time being. Is there some good doc on optimizing MySQL
that you could recommend? My DB is around 10K records and around 15-20
tables, so not too huge, yet. When the server isn't loaded, my app is really
zippy. But get a few too many people running the app and performance falls
off a cliff. Is there somewhere that I can specify how many simultaneous
queries can be run? Also, does the DB get locked only on updating or
inserting, but not selecting? That is my expectation.

--
Richard Gordon
YAKiToMe! Co.
Lead YAK
"Michael Austin" <maustin@firstdbasource.com> wrote in message
news:sfNRj.3129$1b7.1204@newssvr13.news.prodigy.ne t...
> weheh wrote:
>> I have a mysql-based dynamic web app that's slowing down at an
>> unacceptable rate as my numbers of users climbs. Can anyone help me
>> figure out how to tune the database so that it runs faster? I've looked
>> at the my.ini file (I'm on windows server) and beefed up most of the
>> numbers, but don't see any change after rebooting my server. I'm
>> relatively experienced at creating mysql DBs, but a relative newbie when
>> it comes to administering mysql -- so I don't really know where to start.
>> I have already tuned the application to minimize the number of calls to
>> the database, so that base is covered. Thanks for helping out.
>
>
> You are on a windows box and you wonder why your app slows down when you
> add too many users?
>
> Your next step is to run explain plans for the most called sql statements
> to ensure you have them indexed properly. If you have a few users doing
> full table scans - that may not be too bad - but increase that and
> depending on the size of the query, you can bring down a peecee pretty
> easily. I once was called to help fix a problem where a web page
> submitted 5 full table scans returning over 400Mb for each query to a
> mainframe database. It crippled the mainframe and took more than 8 hrs for
> it to recover.
>
> After doing this - move the database to a server that can handle it. Just
> because it can run on a platform, doesn't mean that it should. And ALL of
> the Windows OS's are bloated and perform very poorly regardless of OS
> tuning.
>

weheh wrote:
> No disagreement with any of your observations about the PC platform, but I'm
> stuck there for the time being. Is there some good doc on optimizing MySQL
> that you could recommend? My DB is around 10K records and around 15-20
> tables, so not too huge, yet. When the server isn't loaded, my app is really
> zippy. But get a few too many people running the app and performance falls
> off a cliff. Is there somewhere that I can specify how many simultaneous
> queries can be run? Also, does the DB get locked only on updating or
> inserting, but not selecting? That is my expectation.
>

Richard,

The MySQL manual has a lot of good information on tuning.

But Michael gave you the most important tip. Before you do ANYTHING
ELSE, run EXPLAIN on each of your queries. Look at the output and find
those which are doing full table scans.

You can't specify how many queries can be run simultaneously, but you
can specify the number of connections which can be active concurrently
in your my.ini file.

But I highly doubt that's your problem. Most likely your problem is
inefficient database design and/or inefficient queries.

And no, MySQL does not lock tables for SELECT statements. What gets
locked when you update the database depends on the request and engine
being used.

P.S. Please don't top post. Thanks.

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

"weheh" <weheh@yahoo.com> writes:

> I have a mysql-based dynamic web app that's slowing down at an unacceptable
> rate as my numbers of users climbs. Can anyone help me figure out how to
> tune the database so that it runs faster? I've looked at the my.ini file
> (I'm on windows server) and beefed up most of the numbers, but don't see any
> change after rebooting my server. I'm relatively experienced at creating
> mysql DBs, but a relative newbie when it comes to administering mysql -- so
> I don't really know where to start. I have already tuned the application to
> minimize the number of calls to the database, so that base is covered.
> Thanks for helping out.

If you have not already done so, turn on query caching in the
my.ini (documentation has details). Also, make sure you have indexes,
and to the greatest extent possible make sure all your rows are fixed
length (that will help a lot with searches and indexing) -- that means
no 'varchar' fields. We have found it is sometimes better to just
declare the field extra big, instead of saving some space with varchar.

If you have long 'essay' type fields, put them in another table.

Make sure you periodically run the optimize commands -- that should
all help!

--
John
__________________________________________________ _________________
John Murtari Software Workshop Inc.
jmurtari@following domain 315.635-1968(x-211) "TheBook.Com" (TM)
http://thebook.com/

Hi Jerry,

Thanks for your rapid response. Much appreciated. I've read the tuning
manual at this point and it is full of good suggestions. I've started by
tinkering with my swap space, but haven't gotten any positive results, yet.
My server has 4GB of memory but only 1GB is getting used, max.

A few questions:

- I find that my cgi-bin script that does a database access refreshes my
browser in a herky-jerky fashion. I'll get a bunch of data. Then a long
pause. Then a bunch of data. Then wait again. Etc. What could be causing
that?

- My DB tables are all built using CREATE TABLE. Primary tables have keys.
There are some many to many tables that have no keys. For instance: user is
member of group that has acess to record of another table that can belong to
multiple groups. I don't really understand what MYISAM and INNODB are and
what they can do for me. Do I need to understand that to tune performance?
My queries seem straightforward and are all done with LEFT JOINs. Nothing
too fancy.

- Can EXPLAIN be run from within a cgi-bin script? I must learn this
capability.

Sorry for the top-post.

R

> Richard,
>
> The MySQL manual has a lot of good information on tuning.
>
> But Michael gave you the most important tip. Before you do ANYTHING ELSE,
> run EXPLAIN on each of your queries. Look at the output and find those
> which are doing full table scans.
>
> You can't specify how many queries can be run simultaneously, but you can
> specify the number of connections which can be active concurrently in your
> my.ini file.
>
> But I highly doubt that's your problem. Most likely your problem is
> inefficient database design and/or inefficient queries.
>
> And no, MySQL does not lock tables for SELECT statements. What gets
> locked when you update the database depends on the request and engine
> being used.
>
> P.S. Please don't top post. Thanks.
>
> --
> ==================
> Remove the "x" from my email address
> Jerry Stuckle
> JDS Computer Training Corp.
> jstucklex@attglobal.net
> ==================
>

On 2 May, 16:38, "weheh" <we...@yahoo.com> wrote:
> > The MySQL manual has a lot of good information on tuning.
>
> > But Michael gave you the most important tip. Before you do ANYTHING ELSE,
> > run EXPLAIN on each of your queries. Look at the output and find those
> > which are doing full table scans.
>
> > You can't specify how many queries can be run simultaneously, but you can
> > specify the number of connections which can be active concurrently in your
> > my.ini file.
>
> > But I highly doubt that's your problem. Most likely your problem is
> > inefficient database design and/or inefficient queries.
>
> > And no, MySQL does not lock tables for SELECT statements. What gets
> > locked when you update the database depends on the request and engine
> > being used.
>
> > P.S. Please don't top post. Thanks.
>
> > --
> > ==================
> > Remove the "x" from my email address
> > Jerry Stuckle
> > JDS Computer Training Corp.
> > jstuck...@attglobal.net
> > ==================
> Hi Jerry,
>
> Thanks for your rapid response. Much appreciated. I've read the tuning
> manual at this point and it is full of good suggestions. I've started by
> tinkering with my swap space, but haven't gotten any positive results, yet.
> My server has 4GB of memory but only 1GB is getting used, max.
>
> A few questions:
>
> - I find that my cgi-bin script that does a database access refreshes my
> browser in a herky-jerky fashion. I'll get a bunch of data. Then a long
> pause. Then a bunch of data. Then wait again. Etc. What could be causing
> that?
>
> - My DB tables are all built using CREATE TABLE. Primary tables have keys.
> There are some many to many tables that have no keys. For instance: user is
> member of group that has acess to record of another table that can belong to
> multiple groups. I don't really understand what MYISAM and INNODB are and
> what they can do for me. Do I need to understand that to tune performance?
> My queries seem straightforward and are all done with LEFT JOINs. Nothing
> too fancy.
>
> - Can EXPLAIN be run from within a cgi-bin script? I must learn this
> capability.
>
> Sorry for the top-post.
>
> R
>
> > Richard,
>
Jerry asked you very nicely not to top post and you apologised IN
ANOTHER TOP POST!

Please don't top post. I have fixed the top posting (again).

Now, everyone here has pointed out that you are trying to optimise the
wrong thing. You are looking at the system, before you have optimised
your queries/indexes.

You say that you are "relatively experienced at creating mysql DBs",
yet you seem to be unsure about EXPLAIN and you make a statement like
"My DB tables are all built using CREATE TABLE", as if there was
another way to create a table, so I somehow doubt just how competent
you actually are with RDBMSs.

You say that "Primary tables have keys.", but there are no such things
as "Primary tables". You even admit that "There are some many to many
tables that have no keys." and yet you claim to have optimised the
application!

You need to go away and use EXPLAIN plus knowledge of the data in your
tables and the way the application works to optimise the structure,
indexing and access queries. This is where you will get you
performance gains.

I told you this in the first response to your query and everyone else
has told you the same.

It is now up to you to take the advice we have all offered.

Please stop coming back here and top posting bleats about server size
and other irrelevancies until you have done the basics.

weheh wrote:
> Hi Jerry,
>
> Thanks for your rapid response. Much appreciated. I've read the tuning
> manual at this point and it is full of good suggestions. I've started by
> tinkering with my swap space, but haven't gotten any positive results, yet.
> My server has 4GB of memory but only 1GB is getting used, max.
>
> A few questions:
>
> - I find that my cgi-bin script that does a database access refreshes my
> browser in a herky-jerky fashion. I'll get a bunch of data. Then a long
> pause. Then a bunch of data. Then wait again. Etc. What could be causing
> that?
>

That's simple. Both PHP and your server buffer data for performance
reasons. When PHP's buffer fills, it sends the data to the server.
Then when the server's buffer fills, it sends the data to the client.

> - My DB tables are all built using CREATE TABLE. Primary tables have keys.
> There are some many to many tables that have no keys. For instance: user is
> member of group that has acess to record of another table that can belong to
> multiple groups. I don't really understand what MYISAM and INNODB are and
> what they can do for me. Do I need to understand that to tune performance?
> My queries seem straightforward and are all done with LEFT JOINs. Nothing
> too fancy.
>

All tables should have primary keys. The key may not be a unique
column. For instance, for a table creating a multi-multi link between a
table of employees and a table of skills, I might have two columns -
"employeeid" and "skillid". The primary key would consist of both columns.

MyISAM and InnoDB are two different engines for storing the underlying
data. Each has its advantages and disadvantages - hence two engines.
For instance, MyISAM is generally faster, but InnoDB enforces
referential integrity. You really should understand the differences so
you pick the appropriate engine.

> - Can EXPLAIN be run from within a cgi-bin script? I must learn this
> capability.
>

Sure. Run it just like any other MySQL statement - just place EXPLAIN
in front of the SELECT keyword to get the EXPLAIN output. I generally
run it from PHPMyAdmin, though. Easier than writing even a short CGI.

> Sorry for the top-post.
>
> R
>
>> Richard,
>>
>> The MySQL manual has a lot of good information on tuning.
>>
>> But Michael gave you the most important tip. Before you do ANYTHING ELSE,
>> run EXPLAIN on each of your queries. Look at the output and find those
>> which are doing full table scans.
>>
>> You can't specify how many queries can be run simultaneously, but you can
>> specify the number of connections which can be active concurrently in your
>> my.ini file.
>>
>> But I highly doubt that's your problem. Most likely your problem is
>> inefficient database design and/or inefficient queries.
>>
>> And no, MySQL does not lock tables for SELECT statements. What gets
>> locked when you update the database depends on the request and engine
>> being used.
>>
>> P.S. Please don't top post. Thanks.
>>
>> --
>> ==================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstucklex@attglobal.net
>> ==================
>>
>
>
>


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