mysql optimization

umbloaded · 06-13-2013, 09:26 AM

Hello, I'm running a website on a 16G ram Inter core i5 dedicated server but when more than about 60 users are connected in the same time the site become very slow because of mysql. Is my my.cnf correctly tuned ? Why about half of tables aer stored in the disk instead of ram ?
Thanks!

Code:

[mysqld]
user=mysql
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 1G
max_allowed_packet = 128M
thread_stack = 192K
thread_cache_size       = 64
myisam-recover         = BACKUP
max_connections        = 200
table_cache            = 8192
thread_concurrency     = 8
table_open_cache= 100000
join_buffer_size = 16M
#sort_buffer_size = 8M
max_heap_table_size = 512M
max_connect_errors = 10
tmp_table_size = 1G

#*** InnoDB configuration
innodb_buffer_pool_size=4096M
innodb_additional_mem_pool_size=100M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
innodb_log_file_size=25M

innodb_log_buffer_size=80M

#*** MyISAM Specific options
key_buffer_size = 4096M
read_buffer_size = 4M
read_rnd_buffer_size = 16M

myisam_sort_buffer_size = 512M
#
# * Query Cache Configuration
#
query_cache_limit = 2M
query_cache_size        = 256M
query_cache_type=1
#

log_error                = /var/log/mysqld.log
#server-id = 1
#log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size         = 100M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[isamchk]
key_buffer = 16M

thedaver · 06-13-2013, 09:45 AM

http://www.google.com/#q=best+guide+for+mysql+tuning

karim.ouda · 06-14-2013, 01:04 AM

Enable the query cache in MySQL to improve performance.
If you want to get optimized and speedy response from your MySQL server then you need to add following two configurations directive to your MySQL server:

query_cache_size=SIZE
The amount of memory (SIZE) allocated for caching query results. The default value is 0, which disables the query cache.

query_cache_type=OPTION
Set the query cache type. Possible options are as follows:
0 : Don't cache results in or retrieve results from the query cache.
1 : Cache all query results except for those that begin with SELECT S_NO_CACHE.
2 : Cache results only for queries that begin with SELECT SQL_CACHE

You can setup them in /etc/my.cnf (Red Hat) or /etc/mysql/my.cnf (Debian) file:

Quote:

# vi /etc/my.cnf

Append config directives as follows:

Quote:

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

In above example the maximum size of individual query results that can be cached set to 1048576 using query_cache_limit system variable. Memory size in Kb.

umbloaded · 06-14-2013, 01:11 AM

that parameters are already present in mine are they correct ?

karim.ouda · 06-14-2013, 08:57 AM

Yes they are correct.

TB0ne · 06-14-2013, 12:14 PM

Quote:

Originally Posted by umbloaded

Hello, I'm running a website on a 16G ram Inter core i5 dedicated server but when more than about 60 users are connected in the same time the site become very slow because of mysql. Is my my.cnf correctly tuned ? Why about half of tables aer stored in the disk instead of ram ?

There's no way anyone here can tell you, since you're not providing enough details. Things like how big your database is, how your 60 users are connecting TO that database, how many tables, how many disks/controllers the DB is spanning, etc. If your users are connecting via a web interface, are you sure they're only having ONE connection at a time, versus many different tabs/windows? Are the programs releasing connections as they should be? Using a dedicated client-program? And have you examined the queries against the database?? It could be you have a poorly constructed query that works fine in a small test scenario, but will take a LOT of time in production. The side-effect there is that the client-side connection could be broken, leaving the query running...while the user starts ANOTHER session, thinking that it's locked up. More connections=more queries=more MySQL cycles/slow performance.

And how do you KNOW it's MySQL causing the problem? Run MySQLworkbench or a similar tool, to let you see how your DB is layed out, and modify things based on that. And if you're running a big database on a single hard-drive, have you looked at the disk statistics? iostat and other tools can tell you this.
Also, what kind of monitoring do you have on the system now?

Quote:

Originally Posted by karim.ouda

Yes they are correct.

Sorry, there is no way of knowing if those parameters are correct or not. The OP has not provided enough relevant information about their environment, and since they said those parameters/values were ALREADY PRESENT (and they're still having performance issues), then signs point to other issues with MySQL or the associated clients.

umbloaded · 06-14-2013, 11:43 PM

I know because only pages that need database connection are very slow, they connect via web interface using one single db user. The database has 35 tables but inside some of theme there are up to more than 1 million rows.

thedaver · 06-15-2013, 08:00 AM

Which reveals little more than what TB0ne noted as a shortcoming in your post.

TB0ne · 06-15-2013, 01:52 PM

Quote:

Originally Posted by umbloaded

I know because only pages that need database connection are very slow, they connect via web interface using one single db user. The database has 35 tables but inside some of theme there are up to more than 1 million rows.

Ok, so now we know it's a web-based program. You STILL don't address any of the other questions/issues mentioned that could be part of the problem. And you say "one single db user"...do you mean that your whole web-based system uses the same user-id to attach to the DB, or that you've tried booting everyone out BUT one user??? Again, how BIG is the database? 1 million rows of 100 byte data is trivial....one thousand rows with a thousand fields and complex data isn't.

Again, you need to analyze your DB structure, and look carefully at EVERY piece of the system, queries included.

gdr · 06-16-2013, 07:46 AM

Enable slow query log and see if there are any repeating queries, possibly not using indexes. Or analyze the slow log using something like percona tools.

Code:

cd /var/log
touch slow.log
chown mysql:mysql slow.log
mysql -p
set global slow_query_log=ON;
set global slow_query_log_file="/var/log/slow.log";
set global log_slow_queries=ON;
set global long_query_time=1;
set global log_queries_not_using_indexes=ON;