Silver CHEN wrote:
>
> Dear Sir:
>
> My query is very simple, there are two tables for the virtual system working
> (most important).
>
> * the mail id check table - store mailid, password, mbox path...
> (select >>>>> update/insert)
> * the mail statastics data table - store smtp/pop3 data count, last ip, last
> commection time...
> (update >>>>> select/insert)
>
> There are more than 15 servers in this service, and under some conditions,
> queries will 'jammed'. 150-200 are just an 'average' value, and it won't cause
> the problem I described.
You have to understand that simply adding more hardware does not solve the
problem as well as doing it in software on the algorithmic level. A better
solution is to imlement some sort of arbitration in your code. You could, for
example, get a status info, and if you see that you have a certain number of
threads running in mysqld, you wait for a few seconds + some random period to
spread the load, and then re-try.
>
> I have to 'reduce the probability' of 'jammed' conditions. Add CPU is the
> only way that I can imagine now. Breaking DB, multiple instances are not what
> I want, like I said.
I think the problem in you case is actually not the CPU. If you have a lot of
queries, each of the using a certain amount of temporary RAM space, at a certain
point you run of out of physical RAM and start swapping, and then your system
will come to a grinding halt, even if it has 64 CPUs.
>
> I use FreeBSD for many years, I like it, but I have to migrate to enterprise
> -level OS, like Solaris. Sparc is too expensive for me, and I think PC with
> better architecture will help me for a while.
What makes you thing that FreeBSD is not "enterprise-level"? Just because it is
free does not mean it is not on par with the ones that are not. Solaris, Linux,
and BSD variants are the best OS's for Intel, and each has its own advantages
and drawbacks. If you have one, you should only change to the other if you know
exactly what advantages this will give you. "Enterprise-level" is a term used in
marketing, not in programming and system administration, and frankly speaking,
it is rather meaningless.
>
> We use BX chipset, 2940U2W, and PIII, it's OK, but I have to think about the
> near future. I don't know what it will be in the next year, but who cares?
> Inernet is changing every single day!
>
> Thanks for your great product, mysql is fast and stable, I will use the binary
> to try SMP recently.
>
> Best Regards.
>
> --
> (here are the tables)
> +-----------+--------------------+------+-----+---------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +-----------+--------------------+------+-----+---------+-------+
> | s_mailid | varchar(16) binary | | PRI | | |
> | s_mbox | varchar(48) binary | | | | |
> | s_encpass | varchar(24) binary | | | | |
> | s_active | enum('Y','N') | | | N | |
> | s_rawpass | varchar(16) binary | | | | |
> +-----------+--------------------+------+-----+---------+-------+
> (no other indexes)
>
> +--------------+--------------------+------+-----+---------------------+-------+
> | Field | Type | Null | Key | Default | Extra |
> +--------------+--------------------+------+-----+---------------------+-------+
> | s_mailid | varchar(16) binary | | PRI | | |
> | s_lastsmtp | datetime | | | 0000-00-00 00:00:00 | |
> | s_lastpop3 | datetime | | | 0000-00-00 00:00:00 | |
> | s_smtpbyte | int(11) | | | 0 | |
> | s_pop3byte | int(11) | | | 0 | |
> | s_smtplastip | varchar(20) | | | | |
> | s_pop3lastip | varchar(20) | | | | |
> +--------------+--------------------+------+-----+---------------------+-------+
> (no other indexes)
This does not give enough information as you are not telling us what queries you
are running, and not providing explain on your queries. Here are the steps in
identifying and solving performance problems:
- gather good diagnostic information during times when you system is
experiencing load problems. you must know the queries you are running, the CPU
and RAM utilization, and which program is causing most problems. Indeed it would
be rather wasteful to try to optimize your tables when the problems are caused
by some CGI that gets into and infinite loop.
- run explain on your trouble queries and see if it is using an index, and if it
is using a good index. Fix your query and add more indeces if needed.
- determine how much RAM running each query would use. Do you have enough RAM on
your system? How many concurrent queries of the type can you system handle
without terribly detiorating response time?
- once you have gathered the info, write a benchmark to replicate the problem on
your developement server ( get one if you don't have it). try to isolate as much
stuff as possible, so that when you go to us for help you could clearly state
the problem, and provide some code that you could give us to demonstrate it
apart from your test and deveopment setup.
The more legwork you do gather diagonstics, the better help you will get from
our developemnt team and from other users on this list.
And of course, you are awlays welcome to buy some level of support. With
extended login support we will login to your machine and see what we can do to
optimize your system.
--
Sasha Pachev
+------------------------------------------------------------------+
| TcX ____ __ _____ _____ ___ == mysql@stripped |
| /*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sasha Pachev |
| /*/ /*/ /*/ \*\_ |*| |*||*| mailto:sasha@stripped |
| /*/ /*/ /*/\*\/*/ \*\|*| |*||*| Provo, Utah, USA |
| /*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____ |
| ^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^ |
| /*/ \*\ Developers Team |
+------------------------------------------------------------------+
