LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Server (http://www.linuxquestions.org/questions/linux-server-73/)
-   -   MySQL performance opt for a 5000 connection web site? (http://www.linuxquestions.org/questions/linux-server-73/mysql-performance-opt-for-a-5000-connection-web-site-926392/)

explorer1979 01-29-2012 09:34 PM

MySQL performance opt for a 5000 connection web site?
 
Hi all,

I am new on MySQL, and I had my own server host at home with CentOS 6.2 install LAMP by default setting, and it is hosting my Photo Blog.

It using WordPress and NextGen Gallery, special on NextGen Gallery have a big table with hold all my photo id one by one. It will make the MySQL slowly down? or on default cache size too low on the CentOS 6.2 install?

Here are the URL
Jimmy Chan Photo Blog

I am thinking if 5000 user connect to my web site, how to performance MySQL and Apache/PHP etc is good?

I am total new on MySQL.

My server Intel E5200 CPU, 6G RAM.


Thank you very much.

Cedrik 01-30-2012 01:58 PM

For tunning mysql queries, I like to use mysql command line, and check query with
Code:

EXPLAIN SELECT...
Also mysql server is tunnable, see my.cnf file options

Cyrolancer 01-30-2012 06:25 PM

Additionally, you can try mysqltuner.

http://mysqltuner.com/

explorer1979 01-30-2012 10:05 PM

Hi all,

First, thank you the reply, it is useful for me

And I am using phpmyadmin and press the status

it show me some line on RED Text




This MySQL server has been running for 8 days, 11 hours, 58 minutes and 19 seconds. It started up on Jan 22, 2012 at 10:52 PM.
Query statistics: Since its startup, 1,750,319 queries have been sent to the server.Documentation

Innodb_buffer_pool_readsDocumentation 97 The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.

Innodb_row_lock_waitsDocumentation 1 The number of times a row lock had to be waited for.

Handler_read_rndDocumentation 2.5 M The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.

Handler_read_rnd_nextDocumentation 47.3 M The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

Created_tmp_disk_tablesDocumentation 21.2 k The number of temporary tables on disk created automatically by the server while executing statements. If Created_tmp_disk_tables is big, you may want to increase the tmp_table_size value to cause temporary tables to be memory-based instead of disk-based.



Opened_tablesDocumentation 347 The number of tables that have been opened. If opened tables is big, your table cache value is probably too small.

Cedrik 01-31-2012 05:52 AM

Please post your my.cnf config file

Also it seems you use some not optimized queries / tables

You may need to add indexes in some of your tables

see for example:
http://stackoverflow.com/questions/3...ntage-of-index

explorer1979 01-31-2012 08:26 PM

Cedrik,

Hi , thank you

Here are my CentOS 6.2 /etc/my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid



All value are default after install.


All times are GMT -5. The time now is 06:18 PM.