If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Very laggy server - Assistance appreciated!

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 10-13-11, 07:43
eXDee eXDee is offline
Registered User
 
Join Date: Oct 2011
Posts: 2
Very laggy server - Assistance appreciated!

Hello,
I am in need of assistance of sorting out an overburdened database server. It appears to be doing excessive amounts of table scans.

mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (x86_64) using readline 6.1
load average: 1.61, 2.18, 2.26

Situation: The software running is HLStatsX, which is a community stats system for many online based games. A daemon receives game logs from multiple servers and parses and saves the data into the daemon.
As i am unexperienced with MySQL and did not write this, I am unsure on how to troubleshoot it. Even the slightest bit of assistance is appreciated!

The website is Surf-Infamous.com - Contents for viewing the web front end.

Server spec:
Ubuntu Server
Pentium D 3ghz Dual Core
4gb RAM
2x Hitachi 7200RPM in RAID1

All tables are MyISAM. Here is the status page of phpmyadmin
Code:
http://stats.surf-infamous.com/statuspage.html
I logged queries without indexes for a minute or two. This isn't logging slow queries, just the ones without indexes. I can log slow queries if this would help.
Here is the log
Code:
http://stats.surf-infamous.com/slowquery.log
Contents of my.cnf, apologies for silly mistakes, several people have attempted throwing more resources at the problem but its clearly not working.
Code:
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions are currently parsed.
[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#

#
# * IMPORTANT
#   If you make changes to these settings and your system uses apparmor, you may
#   also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#

user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /var/tmpfs
slave_load_tmpdir       = /tmp
skip-external-locking
#skip-grant-tables
skip-name-resolve
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 96.43.143.2
#
# * Fine Tuning
#
key_buffer              = 256M
max_allowed_packet      = 32M
thread_stack            = 192K
thread_cache_size       = 384
max_connections         = 500
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections        = 100
table_cache            = 20480
#thread_concurrency     = 10
connect_timeout         = 10
interactive_timeout     = 120
join_buffer_size        = 4M
sort_buffer_size        = 6M
read_buffer_size        = 6M
read_rnd_buffer_size    = 8M
innodb_buffer_pool_size = 176M
#
# * Query Cache Configuration
#
query_cache_limit       = 8M
query_cache_size        = 256M

max_heap_table_size     = 64M
tmp_table_size          = 256M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

log_error                = /var/log/mysql/error.log

# Here you can see queries with especially long duration
#log_slow_queries       = /var/tmpfs/slowquery.log
#long_query_time = 20000000
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 16M

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#
!includedir /etc/mysql/conf.d/
Any help is significantly appreciated!
Reply With Quote
  #2 (permalink)  
Old 10-13-11, 11:55
it-iss.com it-iss.com is offline
Registered User
 
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 620
There are several SQL statements which are taking excessively long to complete for the number of rows that are being returned. I have filtered the query times from the slow log and this is what I see:

# Query_time: 4.091963 Lock_time: 0.000097 Rows_sent: 0 Rows_examined: 786335
# Query_time: 10.316334 Lock_time: 0.000123 Rows_sent: 1 Rows_examined: 1458489
# Query_time: 10.327622 Lock_time: 0.000134 Rows_sent: 1 Rows_examined: 1458491
# Query_time: 10.686265 Lock_time: 0.000112 Rows_sent: 1 Rows_examined: 1458489
# Query_time: 10.722302 Lock_time: 0.000116 Rows_sent: 1 Rows_examined: 1458489
# Query_time: 11.319295 Lock_time: 0.000143 Rows_sent: 1 Rows_examined: 1458491
# Query_time: 11.341616 Lock_time: 0.000133 Rows_sent: 1 Rows_examined: 1458491
# Query_time: 12.061641 Lock_time: 0.000104 Rows_sent: 1 Rows_examined: 1458491
# Query_time: 12.220498 Lock_time: 0.000105 Rows_sent: 1 Rows_examined: 1458489
# Query_time: 12.396043 Lock_time: 0.000137 Rows_sent: 1 Rows_examined: 1458491

The rows sent indicates the number of rows sent back to the client. I would start with these queries and try to resolve these one first. They appear to be doing huge amounts of work but returning minimal numbers of rows.

Also your table cache does not appear to be high enough and is still causing problems. Increasing this will speed up access to some of the tables (files).
__________________
Ronan Cashell
Senior Oracle/MySQL DBA
http://www.it-iss.com
Reply With Quote
  #3 (permalink)  
Old 10-13-11, 17:28
eXDee eXDee is offline
Registered User
 
Join Date: Oct 2011
Posts: 2
Is this relevant at all?
table_cache negative scalability - MySQL Performance Blog

I will look at those queries, but i'm unsure how to optimize/fix them as i don't know how this software works exactly.
Reply With Quote
  #4 (permalink)  
Old 10-17-11, 12:45
it-iss.com it-iss.com is offline
Registered User
 
Join Date: Sep 2009
Location: San Sebastian, Spain
Posts: 620
Do you know whether performance has been gradually getting worse over time? Are there lots of inserts and deletes in each of the tables? Perhaps coalescing the table data and rebuilding the indexes might help with this?
__________________
Ronan Cashell
Senior Oracle/MySQL DBA
http://www.it-iss.com
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On