MySQL System Requirements

Anon

Does anyone know of the requirements of having a server setup to run MySQL specificly? I've looked all through mysql.com and came up with nothing (including manaul), maybe I'm missing something here. The reason I ask is that I have a machine setup to run MySQL and nothing more and it seems to take a lot of power when quieries are sent to it. I'll sent one query that wowuld take the CPU usage up to 30% to 45%. I did a test, sending a group of three queries (excuted one after another) and another group of three queries all running at the same time and it seem to use up to 70%+ CPU usage. This worries me since my web site is heavly database drive. Any ideas, thoughts or comments?

The system that runs MySQL is a 200Mhz, 128MBs of RAM, with two 30 Gig hard drives (mirrored using hardware) running on OpenBSD v2.8. All that is running on the system is MySQL daemon, crontabs and sendmail (rarely used). Everything else is disable or not even installed on the system.

[deleted]

And why shouldn't the CPU load go to 70%? 🙂

Theoretically it should be at 100% just running one query.
The point is that every query process should get as much CPU as possible.
It would be kind of strange if the OS decided that a query only needs 2% of the CPU, and it spent the remaining 98% idling wouldn't it?

Apart from that, you didn't tell us what kind of queries you were running and how long they took, so wether 70% is good or bad is impossible to say.

If you really want to know if your server can handle a big load, benchmark it.
Beat it over the head with a lot more than 6 queries at the same time, and see how much longer queries take when the load increases.

Anon

Alright your point is right and I didn't make myself clear as to what I ment.

I run the following queries one after another. It takes about 14 seconds to be displayed via PHP on dedicated internet connection. If these queries are run as a group by two people the database server almost becomes usesless. The queries never seems to return the results. Sometimes one will get the query results and the other user will time out. Other times the query seems to take forever well beyond 30 mins. This of course is to long for web site use even 14 seonds isn't exceptable. If I run these queries each manually directly in MySQL or via a MySQL GUI Client the results is almost instantaneous. Also if these queries are run and someone tries to log into the site (using MySQL for username/password authentication) will not be able to get in.

select
Company.Company_Name,
Company.Phone,
Company.Fax,
Company.Email,
Company.Web_Page,
CompanyShipInfo.Ship_Addr1,
CompanyShipInfo.Ship_Addr2,
CompanyShipInfo.Ship_City,
CompanyShipInfo.Ship_Postal_Code,
Countries.Country_Name,
ProvincesStates.Prov_State_Name
from
PostalCodeInfo,PostalCodeReps,Company,CompanyShipInfo,Countries,ProvincesStates,CompanyRep
where
(
PostalCodeInfo.Postal_Code_Info_ID = PostalCodeReps.Postal_Code_Info_ID and
PostalCodeInfo.Postal_Code = "55318" and
Company.Company_ID = PostalCodeReps.Company_ID_Applied and
Company.Company_Status_ID = 1 and
(Company.Company_Type_ID = 12 or

Company.Company_Type_ID = 14) and
CompanyShipInfo.Company_ID = Company.Company_ID and
Countries.Country_ID = CompanyShipInfo.Country_ID and
ProvincesStates.Prov_State_ID = CompanyShipInfo.Prov_State_ID and
CompanyRep.Company_ID = Company.Company_ID and
CompanyRep.Invoice_Only = 0
)

select
Company.Company_Name,
Company.Phone,
Company.Fax,
Company.Email,
Company.Web_Page,
CompanyShipInfo.Ship_Addr1,
CompanyShipInfo.Ship_Addr2,
CompanyShipInfo.Ship_City,
CompanyShipInfo.Ship_Postal_Code,
Countries.Country_Name,
ProvincesStates.Prov_State_Name
from
PostalCodeInfo,PostalCodeReps,Company,CompanyShipInfo,Countries,ProvincesStates,CompanyRep
where
(
PostalCodeInfo.Postal_Code_Info_ID = PostalCodeReps.Postal_Code_Info_ID and
PostalCodeInfo.Postal_Code = "12110" and
Company.Company_ID = PostalCodeReps.Company_ID_Unitary and
Company.Company_Status_ID = 1 and
(Company.Company_Type_ID = 12 or

Company.Company_Type_ID = 14) and
CompanyShipInfo.Company_ID = Company.Company_ID and
Countries.Country_ID = CompanyShipInfo.Country_ID and
ProvincesStates.Prov_State_ID = CompanyShipInfo.Prov_State_ID and
CompanyRep.Company_ID = Company.Company_ID and
CompanyRep.Invoice_Only = 0
)

SELECT
Company.Company_Name,
CompanyShipInfo.Ship_Addr1,
CompanyShipInfo.Ship_Addr2,
CompanyShipInfo.Ship_City,
ProvincesStates.Prov_State_Name,
Countries.Country_Name,
CompanyShipInfo.Ship_Postal_Code,
Company.Phone,
Company.Fax,
Company.Email,
Company.Web_Page
FROM
CompanyRep, Company, CompanyShipInfo, PostalCodeInfo, ProvincesStates, Countries
WHERE
(
CompanyShipInfo.Prov_State_ID=PostalCodeInfo.Prov_State_ID
AND PostalCodeInfo.Postal_Code=98188
AND CompanyRep.Company_ID=Company.Company_ID
AND CompanyShipInfo.Company_ID=Company.Company_ID
AND CompanyShipInfo.Prov_State_ID=ProvincesStates.Prov_State_ID
AND CompanyShipInfo.Country_ID=Countries.Country_ID
AND Company.Company_Status_ID=1
AND CompanyRep.Invoice_Only=0
AND CompanyRep.Wholesale_Rep=-1
)

[deleted]

Well, if the commandline runs quickly and the PHP doesn't, then are you sure
that the communication between apache, php and the database server are ok?
run the queries, and look at the stats with 'top' to see which process is taking so long, and how much memory is in use.
if the server runs low on memory, it will start to swap memory, at which point everything slows down to MS-speed.

thewarden

Well good point, I believe everything is fine. Mmm not sure what you are meaning but run the queries and then look at the stats with 'top'? Are you saying doing a ps -aux and look at the % of memory usage for the MySQL process? Or on the web server? MS-speed ha ha... I don't want that, that is the one reason why I'm not using MS products <G>.

Here's my setup...

Web Server : 200Mhz with 128MBs of RAM with a 4.3 Gig Hard Drive.
Database Server: 233Mhz with 128MBs of RAM with two 30 Gig Hard Drives (using hardware for mirroring)
Both systems run on OpenBSD.

I know I sad the DB server was a 200Mhz, I was wrong <G>.

I also have a dedicated internet connection getting about 1.2MBs per second.

vincent wrote:

[deleted]

I'm saying: start a bunch of queries through the webserver, then run the 'top' (basic unix program) program on the webserver and see how the load is distributed.
That will put the most active program at the top, and you can see which is slowing you down.
If it's the database, then the webserver should stay at near 0.0% whilethe query is running.

thewarden

Ahh sorry ok, I ran 'top' on the web server and the httpd barely goes beyond 0.0%. But when I run 'top' on the DB server mysqld is at 99.0%. I've been doing more testing of the three queries vis PHP and it seems to work one minute but then the next not. When mysqld process CPU goes to 99% it basicly renders MySQL useless. Can't even login to MySQL or get basic server status. Would me posting my three queries in PHP form? I could post the entire PHP file but it's about 400+ lines of code <G>. This problem of mine is really starting to get anonying <sign>.