Slow Mysql process and Slow IPB 3

[Mr-Y]

Does anyone know or can explain what the below is ?
Its from the mysql process list
mysql
cpu = 25.3
mem = 13.4

If i kill the process it comes back straight away and builds the load up
It doesn't finish either,
my IPB v3.03 is dreadfully slow, at least 20secs load time on any page in the board
However the test board (same version) is not slow
hope somebody can help :)

Process 14303 attached - interrupt to quit

select(12, [11], NULL, NULL, NULL)      = 1 (in [11])

fcntl64(11, F_SETFL, O_RDWR|O_NONBLOCK) = 0

accept(11, {sa_family=AF_FILE, path=@}, [2]) = 25

fcntl64(11, F_SETFL, O_RDWR)            = 0

getsockname(25, {sa_family=AF_FILE, path="/tmp/mysql.soc"}, [18]) = 0

fcntl64(25, F_SETFL, O_RDONLY)          = 0

fcntl64(25, F_GETFL)                    = 0x2 (flags O_RDWR)

fcntl64(25, F_SETFL, O_RDWR|O_NONBLOCK) = 0

setsockopt(25, SOL_IP, IP_TOS, [8], 4)  = -1 EOPNOTSUPP (Operation not supported)

futex(0x86a2024, FUTEX_WAKE, 1)         = 1

futex(0x86a2020, FUTEX_WAKE, 1)         = 1

futex(0x869ffe0, FUTEX_WAKE, 1)         = 1

select(12, [11], NULL, NULL, NULL)      = 1 (in [11])

fcntl64(11, F_SETFL, O_RDWR|O_NONBLOCK) = 0

accept(11, {sa_family=AF_FILE, path=@}, [2]) = 25

fcntl64(11, F_SETFL, O_RDWR)            = 0

My server info also

Server Information

Processor Information


Processor #1 Vendor: GenuineIntel

Processor #1 Name: Intel® Pentium® 4 CPU 3.06GHz

Processor #1 speed: 3082.796 MHz

Processor #1 cache size: 512 KB


Processor #2 Vendor: GenuineIntel

Processor #2 Name: Intel® Pentium® 4 CPU 3.06GHz

Processor #2 speed: 3082.796 MHz

Processor #2 cache size: 512 KB



Memory Information


Memory: 1032692k/1047488k available (1884k kernel code, 14108k reserved, 759k data, 188k init, 129984k highmem)



System Information


Linux server.wyredbytes.com 2.6.9-55.0.12.ELsmp #1 SMP Fri Nov 2 11:19:08 EDT 2007 i686 i686 i386 GNU/Linux



Physical Disks


hda: ST3120022A, ATA DISK drive

hda: max request size: 1024KiB

hda: 234441648 sectors (120034 MB) w/2048KiB Cache, CHS=16383/255/63, UDMA(100)

hda: cache flushes supported



Current Memory Usage


             total       used       free     shared    buffers     cached

Mem:       1033572     891356     142216          0      20132     510700

-/+ buffers/cache:     360524     673048

Swap:      2096472       6072    2090400

Total:     3130044     897428    2232616



Current Disk Usage


Filesystem            Size  Used Avail Use% Mounted on

/dev/hda3             108G   11G   93G  11% /

/dev/hda1             122M   17M  100M  14% /boot

none                  505M     0  505M   0% /dev/shm

/usr/tmpDSK           485M   11M  449M   3% /tmp

/tmp                  485M   11M  449M   3% /var/tmp

my.cnf if it helps

# The MySQL server

[mysqld]

#port		= 3306

socket		= /tmp/mysql.sock

skip-locking

key_buffer = 256M

max_allowed_packet = 256M

table_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M


join_buffer_size = 256

low_priority_updates=1

concurrent_insert=2


thread_cache = 8

query_cache_size= 5M

tmp_table_size = 16M

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 2

myisam_data_pointer_size = 7

log-slow-queries = mysqllog

long_query_time = 5

log-queries-not-using-indexes

log-slow-admin-statements


# Don't listen on a TCP/IP port at all. This can be a security enhancement,

# if all processes that need to connect to mysqld run on the same host.

# All interaction with mysqld must be made via Unix sockets or named pipes.

# Note that using this option without enabling named pipes on Windows

# (via the "enable-named-pipe" option) will render mysqld useless!

# 

skip-networking


# Replication Master Server (default)

# binary logging is required for replication

# log-bin=mysql-bin


# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id	= 1


# Point the following paths to different dedicated disks

#tmpdir		= /tmp/		

#log-update 	= /path-to-dedicated-directory/hostname


[mysqldump]

quick

max_allowed_packet = 256M


[mysql]

no-auto-rehash

max_allowed_packet = 256M

# Remove the next comment character if you are not familiar with SQL

#safe-updates


[isamchk]

key_buffer = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M


[myisamchk]

key_buffer = 128M

sort_buffer_size = 128M

read_buffer = 2M

write_buffer = 2M


[mysqlhotcopy]

interactive-timeout

also some of the last entry's from my slow log

SELECT id, member_id, member_name, seo_name, login_type, running_time, member_group, uagent_type FROM ibf_sessions WHERE running_time > 1253823900;

# Time: 090924 22:25:12

# User@Host: komp_komp[komp_komp] @ localhost []

# Query_time: 0  Lock_time: 0  Rows_sent: 56  Rows_examined: 56

SELECT id, member_id, member_name, seo_name, login_type, running_time, member_group, uagent_type FROM ibf_sessions WHERE running_time > 1253823912;

# Time: 090924 22:25:13

# User@Host: komp_komp[komp_komp] @ localhost []

# Query_time: 0  Lock_time: 0  Rows_sent: 56  Rows_examined: 56

SELECT id, member_id, member_name, seo_name, login_type, running_time, member_group, uagent_type FROM ibf_sessions WHERE running_time > 1253823913;

# Time: 090924 22:25:14

# User@Host: komp_komp[komp_komp] @ localhost []

# Query_time: 0  Lock_time: 0  Rows_sent: 20  Rows_examined: 40

SELECT s.*,m.member_id, m.member_group_id, m.members_display_name, m.members_seo_name, mgroup_others FROM ibf_shoutbox_shouts s  LEFT JOIN ibf_members m ON ( m.member_id=s.s_mid )   ORDER BY s.s_date DESC LIMIT 0,20;

# Time: 090924 22:25:22

# User@Host: komp_komp[komp_komp] @ localhost []

# Query_time: 0  Lock_time: 0  Rows_sent: 56  Rows_examined: 56

SELECT id, member_id, member_name, seo_name, login_type, running_time, member_group, uagent_type FROM ibf_sessions WHERE running_time > 1253823922;

also from /tuning-primer.sh

Avg. qps = 11

Total Questions = 967

Threads Connected = 1


Warning: Server has not been running for at least 48hrs.

It may not be safe to use these recommendations


To find out more information on how each of these

runtime variables effects performance visit:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Visit http://www.mysql.com/products/enterprise/advisors.html

for info about MySQL's Enterprise Monitoring and Advisory Service


SLOW QUERIES

The slow query log is enabled.

Current long_query_time = 5 sec.

You have 13 out of 996 that take longer than 5 sec. to complete

Your long_query_time seems to be fine


BINARY UPDATE LOG

The binary update log is NOT enabled.

You will not be able to do point in time recovery

See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html


WORKER THREADS

Current thread_cache_size = 8

Current threads_cached = 3

Current threads_per_sec = 0

Historic threads_per_sec = 0

Your thread_cache_size is fine


MAX CONNECTIONS

Current max_connections = 100

Current threads_connected = 1

Historic max_used_connections = 4

The number of used connections is 4% of the configured maximum.

You are using less than 10% of your configured max_connections.

Lowering max_connections could help to avoid an over-allocation of memory

See "MEMORY USAGE" section to make sure you are not over-allocating


MEMORY USAGE

Max Memory Ever Allocated : 295 M

Configured Max Per-thread Buffers : 619 M

Configured Max Global Buffers : 271 M

Configured Max Memory Limit : 890 M

Physical Memory : 1009 M

Max memory limit seem to be within acceptable norms


KEY BUFFER

Current MyISAM index space = 177 M

Current key_buffer_size = 256 M

Key cache miss rate is 1 : 19

Key buffer free ratio = 88 %

Your key_buffer_size seems to be fine


QUERY CACHE

Query cache is enabled

Current query_cache_size = 5 M

Current query_cache_used = 2 M

Current query_cache_limit = 1 M

Current Query cache Memory fill ratio = 58.96 %

Current query_cache_min_res_unit = 4 K

MySQL won't cache query results that are larger than query_cache_limit in size


SORT OPERATIONS

Current sort_buffer_size = 1 M

Current read_rnd_buffer_size = 4 M

Sort buffer seems to be fine


JOINS

Current join_buffer_size = 12.00 K

You have had 0 queries where a join could not use an index properly

Your joins seem to be using indexes properly


OPEN FILES LIMIT

Current open_files_limit = 1024 files

The open_files_limit should typically be set to at least 2x-3x

that of table_cache if you have heavy MyISAM usage.

Your open_files_limit value seems to be fine


TABLE CACHE

Current table_cache value = 350 tables

You have a total of 292 tables

You have 293 open tables.

The table_cache value seems to be fine


TEMP TABLES

Current max_heap_table_size = 16 M

Current tmp_table_size = 16 M

Of 228 temp tables, 6% were created on disk

Created disk tmp tables ratio seems fine


TABLE SCANS

Current read_buffer_size = 1 M

Current table scan ratio = 329 : 1

read_buffer_size seems to be fine


TABLE LOCKING

Current Lock Wait ratio = 0 : 1200

Your table locking seems to be fine

[Mr-Y]

did i miss something ?
no-one have anything to say on the above ?

[Bono]

First you need to wait 48h for tuning-primer to examine your settings.
Second try to optimize your database, and then check again if there will be slow queries.
Third do hdparm -t /dev/hda maybe disk switched to pio mode or something so everything is slowed down, with 11qps you shouldn't have any slow queries.
Fourth do also iostat to check if your hdd is too slow, you are really using old hdd.

[Mr-Y]

Hi and thanks for replying,
i will get on that straight away,
i will report back cause it may help others also :)

[Mr-Y]

Gonna try and see if the recommendations below will make any difference
unless anyone has any other ideas ;)
My IPB is 5.5 years old and has gone from v1.3 to 3.03 in that time if it makes any difference.


My database = 133 table(s) 1,300,948 Queries MyISAM Size = 1.0 GiB

hdparm -t /dev/hda


/dev/hda:

 Timing buffered disk reads:  164 MB in  3.02 seconds =  54.24 MB/sec



iostat

Linux 2.6.9-55.0.12.ELsmp (server.wyredbytes.com)       09/26/2009


avg-cpu:  %user   %nice    %sys %iowait   %idle

          15.60    0.01    1.63    0.34   82.42


Device:            tps   Blk_read/s   Blk_wrtn/s   Blk_read   Blk_wrtn

hda               6.25        49.18       186.69   21593748   81972220

hda1              0.00         0.00         0.00        702          4

hda2              0.02         0.05         0.09      21740      40344

hda3             25.39        48.36       186.60   21233642   81931464

/tuning-primer.sh

        -- MYSQL PERFORMANCE TUNING PRIMER --

             - By: Matthew Montgomery -


MySQL Version 5.0.81-community-log i686


Uptime = 2 days 0 hrs 1 min 31 sec

Avg. qps = 4

Total Questions = 739702

Threads Connected = 1


Server has been running for over 48hrs.

It should be safe to follow these recommendations


To find out more information on how each of these

runtime variables effects performance visit:

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

Visit http://www.mysql.com/products/enterprise/advisors.html

for info about MySQL's Enterprise Monitoring and Advisory Service


SLOW QUERIES

The slow query log is enabled.

Current long_query_time = 5 sec.

You have 8868 out of 739723 that take longer than 5 sec. to complete

Your long_query_time seems to be fine


BINARY UPDATE LOG

The binary update log is NOT enabled.

You will not be able to do point in time recovery

See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html


WORKER THREADS

Current thread_cache_size = 8

Current threads_cached = 7

Current threads_per_sec = 0

Historic threads_per_sec = 0

Your thread_cache_size is fine


MAX CONNECTIONS

Current max_connections = 100

Current threads_connected = 1

Historic max_used_connections = 20

The number of used connections is 20% of the configured maximum.

Your max_connections variable seems to be fine.


MEMORY USAGE

Max Memory Ever Allocated : 394 M

Configured Max Per-thread Buffers : 619 M

Configured Max Global Buffers : 271 M

Configured Max Memory Limit : 890 M

Physical Memory : 1009 M

Max memory limit seem to be within acceptable norms


KEY BUFFER

Current MyISAM index space = 178 M

Current key_buffer_size = 256 M

Key cache miss rate is 1 : 83

Key buffer free ratio = 87 %

Your key_buffer_size seems to be fine


QUERY CACHE

Query cache is enabled

Current query_cache_size = 5 M

Current query_cache_used = 2 M

Current query_cache_limit = 1 M

Current Query cache Memory fill ratio = 59.37 %

Current query_cache_min_res_unit = 4 K

MySQL won't cache query results that are larger than query_cache_limit in size


SORT OPERATIONS

Current sort_buffer_size = 1 M

Current read_rnd_buffer_size = 4 M

Sort buffer seems to be fine


JOINS

Current join_buffer_size = 12.00 K

You have had 14 queries where a join could not use an index properly

You should enable "log-queries-not-using-indexes"

Then look for non indexed joins in the slow query log.

If you are unable to optimize your queries you may want to increase your

join_buffer_size to accommodate larger joins in one pass.


Note! This script will still suggest raising the join_buffer_size when

ANY joins not using indexes are found.


OPEN FILES LIMIT

Current open_files_limit = 1024 files

The open_files_limit should typically be set to at least 2x-3x

that of table_cache if you have heavy MyISAM usage.

Your open_files_limit value seems to be fine


TABLE CACHE

Current table_cache value = 350 tables

You have a total of 292 tables

You have 304 open tables.

The table_cache value seems to be fine


TEMP TABLES

Current max_heap_table_size = 16 M

Current tmp_table_size = 16 M

Of 3253 temp tables, 21% were created on disk

Created disk tmp tables ratio seems fine


TABLE SCANS

Current read_buffer_size = 1 M

Current table scan ratio = 2691 : 1

read_buffer_size seems to be fine


TABLE LOCKING

Current Lock Wait ratio = 1 : 75

You may benefit from selective use of MyISAM.

[Bono]

Hmm that looks OK, I would recommend to report this issue with IPS. After upgrade have you rebuild all the stuff IPS recommends and after that have you optimized your database?

[Mr-Y]

HI,
going to check to make sure i did everything for the upgrade
then i think i will report the issue
thanks again :)

[Bono]

Keep us informed, when IPS detect your problem maybe it would help someone in the future.

[Mr-Y]

ok i put a ticket in, but not 2 seconds after i did i found the problem

for me this addon (SOS30) Number of Users per Skin v1.0.0 was a major factor
in slowing the loadtimes of my board,
dont wanna ruffle feathers, just thought best to post my probs and answers.
thanks all for reading.