Folks,

A needed. My manager's pushed me to optimize the tables that I
created in distributed in several DBs. I have tried best to explain to him
that I have followed the strict formula design to do the Join, Search and
so on. However, the system performance is not good enough yet when our
system get thousands' users visiting in the same time.

Could anyone point me something so that I can tune the system performance?
I don't think my db tables have problems. I guess I should investigate on
the deployment, for example, cluster, load-balancing and so on? Our system
hs big traffic at daytime, usually at noon time or at evening.

Thanks in advance,

------------------------
Hailiang Ji, Developer
Email: hji@mydanwei.com
Web: www.mydanwei.com
myDanwei, Inc.
-----------------------

You should read up on the "show status" and "show variables" output.
That will give you a start as to perhaps some obvious issues. For
instances, your opened_tables and threads_created should not be
large. Using the right table types is also a very big issue, although
you may have gone down that path already.
You can try doing a search on remember.yahoo.com and mysql, they ran
into some interesting performance bottlenecks they had to solve.
Installing mytop may also be ful to determine what's going on.
Finally, you should also consider what is going on in the OS, I like
vmstat for a quick overview. I don't know what you definition of
"performance is not good". Following a "strict formula" may be part
of your problem. Nobody normalizes their database to fifth normal
form, it would be too slow and complicated.

Check this link out:
http://dev.mysql.com/books/hpmysql-excerpts/ch06.html
The last paragraph has a bit on yahoo.


On Sep 11, 2007, at 2:10 AM, Hailiang Ji wrote:

> Folks,
>
> A needed. My manager's pushed me to optimize the tables that I
> created in distributed in several DBs. I have tried best to explain
> to him
> that I have followed the strict formula design to do the Join,
> Search and
> so on. However, the system performance is not good enough yet when our
> system get thousands' users visiting in the same time.
>
> Could anyone point me something so that I can tune the system
> performance?
> I don't think my db tables have problems. I guess I should
> investigate on
> the deployment, for example, cluster, load-balancing and so on? Our
> system
> hs big traffic at daytime, usually at noon time or at evening.
>
> Thanks in advance,
>
> ------------------------
> Hailiang Ji, Developer
> Email: hji@mydanwei.com
> Web: www.mydanwei.com
> myDanwei, Inc.
> -----------------------
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> unsub=brenttech@gmail.com
>

Hailiang Ji wrote:
> Folks,
>
> A needed. My manager's pushed me to optimize the tables that I
> created in distributed in several DBs. I have tried best to explain to him
> that I have followed the strict formula design to do the Join, Search and
> so on. However, the system performance is not good enough yet when our
> system get thousands' users visiting in the same time.

Maybe you're missing some indexes.

Enable slow-query-log and see where your time is being spent.

http://dev.mysql.com/doc/refman/4.1/...query-log.html

Once you have some data, use explain to work out whether a query is
using an index.

Or try mysql_explain_log:

http://dev.mysql.com/doc/refman/4.1/...plain-log.html