Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I was still new to the server, I have a hosting server, the specification DUAL CORE CPU, 4GB RAM, 500GB HDD.

I use OS Centos 5 64bit, Kloxo Panel, Apache Web Server & MySQL sevrer.

I have a problem, MySQL service I often suddenly die alone, especially when I try to do as much as 10-20 times the mysql_query command simultaneously, but I see only the status of unused RAM Memory <512MB, 3GB left over. How do I optimize my MySQL server to be able to do a lot of queries and sequential, 100 a query for example. Please help. Thank you

share|improve this question
    
IN addition: Maybe you need a real server? You know - one with non pathetic disc system? 500gb sounds simply like a very slow end user disc. Consider moving to something more approrpiate for a database - multiple discs, ssd. My main personal sql server has 2 ssd + 8 velociraptors and I cam challenging that one regularly to the limit. –  TomTom Apr 30 '12 at 8:40
    
Perhaps the first thing you should do is read the MySQL documentation. –  John Gardeniers Apr 30 '12 at 9:52
    
@tomTom : I mean i have a Mini Dedicated Server, just for learn web server, I just install centos5 and LAMP appropriate guidelines. Then my right online. My server uses the default settings of pure lamp. I tried to create a database with 10 tables that each have 10 fields. and I made a php file to query. $q = mysql_query (SELECT * FROM table1) while (mysql_fetch_assoc ($q)) { .... } $q = mysql_query (SELECT * FROM table2) while (mysql_fetch_assoc ($q)) { .... } and so on up to 10. Browser screen loading time .. Then, suddenly, "Can not establishised database connection" . –  Fredy May 1 '12 at 4:15
    
@jhon: thank you.. :) –  Fredy May 1 '12 at 4:15

2 Answers 2

Typically there are 2 steps you should be taking at basic level;

  1. Optimize your query structures to best make use of indexes, caches and mysql features
  2. Configure MySQL to best optimize for your data and query types.

With both steps, there is little to compare to spending some time to go to the web and read about query optimization and tuning mysql for performance.

However for both 1 and 2, you can use the Percona toolkit to advise you on how to improve the current situation. To start with make sure you have enabled the "SLOW Query log" in /etc/my.cnf like so;

# Here you can see queries with especially long duration
log_slow_queries        = /var/lib/mysql/slow.log

Then you can examine the /var/lib/mysql/slow.log file for queries that are particularly bad, like so;

 pt-query-advisor /path/to/slow-query.log

which will provide pretty detailed output on optimizing those slow queries on your server. You can also use the pt-variable-advisor to look at any settings that you might need to change to improve the server speed.

share|improve this answer
    
I'll try it.. :) –  Fredy May 1 '12 at 4:16

Your question pretty much lack details, but here's my general mindflow about MySQL.

  • If your tables are in MyISAM format, then a workload mixed with long-running SELECTs combined with lots of write activity are going to kill your MySQL server for sure. Or, at least, making it veeeeeeeeery slow.

  • "How to fine-tune MySQL" is a completely different beast with different kind of workloads. Do you have lots and lots of short-lived connections/queries, something like thousands of select queries per sec, but with very simple table structure and well made indexes? Or not so much queries and simultaneous connections, but very heavy and complex selects? Optimizing for those requires completely different setups.

  • Lack of proper indexes is often reason for MySQL misbehaviour. Are you sure you have all the necessary indexes in place?

  • MySQL Workbench can be a very useful tool while troubleshooting MySQL. Let it run, see what queries get stuck for prolonged period. Also, of course, MySQL slow query log is a handy thing to enable and so is using mysqltuner.

  • With write-oriented workload MySQL query cache can sometimes be harmful. With just random SELECTs it won't help much and reconstructing the invalidated cache all the time is just another useless performance hit.

  • At application level memcached can help you a lot, as it heavily reduces your database server load.

share|improve this answer
    
Janne forogt to say that if you are using MyISAM engine then top of your list of things to consider is switching to innodb. –  symcbean Apr 30 '12 at 12:15
    
Thank you all. So far I do not really know about the MyISAM or InnoDB, I just know that the database I use is MySQL. Looks like I need to learn more .. –  Fredy May 1 '12 at 4:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.