View All Products
Compute
Storage
Networking
Databases
Services
Developer Tools
Industries
Pricing
Community
Engage With Us
Community ▶ Questions ▶ Slow speed for php/msql web based software
Log in to Ask a Question

Slow speed for php/msql web based software

performance

Hi, I'm the webmaster for our companies website. Though I did not set up this account, I am now in charge.

Our company has a employees section to our website where they run an application written in php using mysql database.

Today they have all complained to me that it is running really slowly.

As you can see www.allgreenrecycling.com runs fine. So I'm assuming it has to do with our mysql settings. We have 5-6 employees running the application at the same time.

I opened a new ticket with support and after a few questions they suggested I try the forum. He asked for this info. Maybe you guys would like to see it too:

root@agr-linode:~# free -m

total used free shared buffers cached

Mem: 360 272 87 0 83 110

-/+ buffers/cache: 78 281

Swap: 511 18 493

root@agr-linode:~# vmstat 1 20

procs –---------memory---------- ---swap-- -----io---- -system-- ----cpu----

r b swpd free buff cache si so bi bo in cs us sy id wa

1 0 18476 85600 85932 117140 0 0 0 0 2 2 0 0 99 0

1 0 18476 85476 85932 117268 0 0 0 0 1173 249 20 5 75 0

1 0 18476 85352 85936 117416 0 0 0 568 1108 120 23 2 74 0

1 0 18476 85424 85940 117428 0 0 0 0 1100 131 22 2 75 0

1 0 18476 85416 85940 117444 0 0 0 0 1127 153 20 5 75 0

1 0 18476 85292 85940 117556 0 0 0 0 1067 79 24 0 75 0

1 0 18476 85292 85944 117560 0 0 0 0 1121 176 20 4 75 0

2 0 18476 66308 85944 117704 0 0 0 0 1620 441 21 4 74 0

1 0 18476 84660 85948 117740 0 0 0 684 1423 425 37 0 62 0

1 0 18476 84660 85948 117740 0 0 0 0 1181 268 20 4 75 0

1 0 18476 83048 85948 117892 0 0 0 0 1206 255 22 4 74 0

1 0 18476 83048 85952 117904 0 0 0 268 1205 224 24 0 75 0

1 0 18476 82924 85956 118032 0 0 0 0 1111 139 20 5 75 0

1 0 18476 83048 85960 118044 0 0 0 588 1142 197 21 1 77 0

1 0 18476 83048 85964 118064 0 0 0 0 1112 153 24 4 72 0

1 0 18476 82988 85964 118192 0 0 0 0 1233 382 20 5 75 0

1 0 18476 82988 85964 118296 0 0 0 0 1382 619 25 0 75 0

1 0 18476 82864 85972 118400 0 0 0 0 1428 754 21 4 75 0

1 0 18476 82740 85980 118504 0 0 0 400 1210 327 21 4 75 0

1 0 18476 82804 85980 118520 0 0 0 0 1164 250 24 0 75 0

Support suggested I try this: http://library.linode.com/troubleshooti … y_settings">http://library.linode.com/troubleshooting/memory-networking#mysqllowmemory_settings

That didn't help. And it seemed to slow it down.

I'm not sure what other information you guys need, but I will be watching so I can reply with any bits of information you guys need.

6 Replies

whats your operating system and linode size, mysql version.

You should check your /etc/my.cnf for a "slowquerylog" or "logslowqueries" setting and then check that file it will say what queries are taking a long time to execute.

Also ssh to your server, type wget mysqltuner.pl then perl ./mysqltuner.pl and follow the instructions it will give you a clue what else might be wrong.

Hmm, your CPU usage seems to be at nearly 25%. I suspect there's something sitting and spinning just one CPU. What does your "ps auxwww" look like?

USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND

root 1 0.0 0.1 1720 392 ? Ss Feb15 0:02 /sbin/init

root 2 0.0 0.0 0 0 ? S Feb15 0:05 [migration/0]

root 3 0.0 0.0 0 0 ? SN Feb15 0:00 [ksoftirqd/0]

root 4 0.0 0.0 0 0 ? S Feb15 0:01 [migration/1]

root 5 0.0 0.0 0 0 ? SN Feb15 0:02 [ksoftirqd/1]

root 6 0.0 0.0 0 0 ? S Feb15 0:00 [migration/2]

root 7 0.0 0.0 0 0 ? SN Feb15 0:00 [ksoftirqd/2]

root 8 0.0 0.0 0 0 ? S Feb15 0:00 [migration/3]

root 9 0.0 0.0 0 0 ? SN Feb15 0:00 [ksoftirqd/3]

root 10 0.0 0.0 0 0 ? S< Feb15 0:00 [events/0]

root 11 0.0 0.0 0 0 ? S< Feb15 0:00 [events/1]

root 12 0.0 0.0 0 0 ? S< Feb15 0:00 [events/2]

root 13 0.0 0.0 0 0 ? S< Feb15 0:00 [events/3]

root 14 0.0 0.0 0 0 ? S< Feb15 0:00 [khelper]

root 15 0.0 0.0 0 0 ? S< Feb15 0:00 [kthread]

root 17 0.0 0.0 0 0 ? S< Feb15 0:00 [xenwatch]

root 18 0.0 0.0 0 0 ? S< Feb15 0:00 [xenbus]

root 27 0.0 0.0 0 0 ? S< Feb15 0:00 [kblockd/0]

root 28 0.0 0.0 0 0 ? S< Feb15 0:00 [kblockd/1]

root 29 0.0 0.0 0 0 ? S< Feb15 0:00 [kblockd/2]

root 30 0.0 0.0 0 0 ? S< Feb15 0:00 [kblockd/3]

root 31 0.0 0.0 0 0 ? S< Feb15 0:00 [cqueue/0]

root 32 0.0 0.0 0 0 ? S< Feb15 0:00 [cqueue/1]

root 33 0.0 0.0 0 0 ? S< Feb15 0:00 [cqueue/2]

root 34 0.0 0.0 0 0 ? S< Feb15 0:00 [cqueue/3]

root 36 0.0 0.0 0 0 ? S< Feb15 0:00 [kseriod]

root 117 0.0 0.0 0 0 ? S Feb15 0:00 [pdflush]

root 118 0.0 0.0 0 0 ? S< Feb15 0:01 [kswapd0]

root 119 0.0 0.0 0 0 ? S< Feb15 0:00 [aio/0]

root 120 0.0 0.0 0 0 ? S< Feb15 0:00 [aio/1]

root 121 0.0 0.0 0 0 ? S< Feb15 0:00 [aio/2]

root 122 0.0 0.0 0 0 ? S< Feb15 0:00 [aio/3]

root 124 0.0 0.0 0 0 ? S< Feb15 0:00 [jfsIO]

root 125 0.0 0.0 0 0 ? S< Feb15 0:00 [jfsCommit]

root 126 0.0 0.0 0 0 ? S< Feb15 0:00 [jfsCommit]

root 127 0.0 0.0 0 0 ? S< Feb15 0:00 [jfsCommit]

root 128 0.0 0.0 0 0 ? S< Feb15 0:00 [jfsCommit]

root 129 0.0 0.0 0 0 ? S< Feb15 0:00 [jfsSync]

root 130 0.0 0.0 0 0 ? S< Feb15 0:00 [xfslogd/0]

root 131 0.0 0.0 0 0 ? S< Feb15 0:00 [xfslogd/1]

root 132 0.0 0.0 0 0 ? S< Feb15 0:00 [xfslogd/2]

root 133 0.0 0.0 0 0 ? S< Feb15 0:00 [xfslogd/3]

root 134 0.0 0.0 0 0 ? S< Feb15 0:00 [xfsdatad/0]

root 135 0.0 0.0 0 0 ? S< Feb15 0:00 [xfsdatad/1]

root 136 0.0 0.0 0 0 ? S< Feb15 0:00 [xfsdatad/2]

root 137 0.0 0.0 0 0 ? S< Feb15 0:00 [xfsdatad/3]

root 746 0.0 0.0 0 0 ? S< Feb15 0:00 [net_accel/0]

root 747 0.0 0.0 0 0 ? S< Feb15 0:00 [net_accel/1]

root 748 0.0 0.0 0 0 ? S< Feb15 0:00 [net_accel/2]

root 749 0.0 0.0 0 0 ? S< Feb15 0:00 [net_accel/3]

root 756 0.0 0.0 0 0 ? S< Feb15 0:00 [kpsmoused]

root 759 0.0 0.0 0 0 ? S< Feb15 0:00 [kcryptd/0]

root 760 0.0 0.0 0 0 ? S< Feb15 0:00 [kcryptd/1]

root 761 0.0 0.0 0 0 ? S< Feb15 0:00 [kcryptd/2]

root 762 0.0 0.0 0 0 ? S< Feb15 0:00 [kcryptd/3]

root 763 0.0 0.0 0 0 ? S< Feb15 0:00 [kmirrord]

root 773 0.0 0.0 0 0 ? S< Feb15 0:18 [kjournald]

root 869 0.0 0.0 2016 284 ? S dhcp 1847 0.0 0.1 2348 564 ? S syslog 2100 0.0 0.1 1848 488 ? Ss Feb15 0:04 /sbin/syslogd - u syslog

root 2121 0.0 0.0 1784 220 ? S Feb15 0:00 /bin/dd bs 1 if /proc/kmsg of /var/run/klogd/kmsg

klog 2123 0.0 0.0 2124 292 ? Ss Feb15 0:00 /sbin/klogd -P /var/run/klogd/kmsg

root 2144 0.0 0.1 5224 652 ? Ss Feb15 0:05 /usr/sbin/sshd

root 2191 0.0 0.0 1636 276 tty1 Ss+ Feb15 0:00 /sbin/getty 384 00 tty1

root 3279 0.0 0.2 2112 796 ? Ss Feb15 0:00 /usr/sbin/cron

root 20404 0.0 0.4 5400 1508 ? Ss Feb16 0:02 /usr/lib/postfi x/master

postfix 20406 0.0 0.4 5448 1640 ? S Feb16 0:00 qmgr -l -t fifo -u

postfix 20413 0.0 0.5 5776 1952 ? S Feb16 0:00 tlsmgr -l -t un ix -u -c

root 1748 0.0 0.0 0 0 ? S Mar02 0:00 [pdflush]

root 10844 0.0 0.1 1776 528 ? S Apr28 0:00 /bin/sh /usr/bi n/mysqld_safe

mysql 10889 2.1 13.4 134544 49524 ? Sl Apr28 25:11 /usr/sbin/mysql d –basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysql d/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld .sock

root 10890 0.0 0.1 1708 560 ? S Apr28 0:00 logger -p daemo n.err -t mysqld_safe -i -t mysqld

root 27982 0.0 2.0 24128 7420 ? Ss Apr28 0:00 /usr/sbin/apach e2 -k start

root 4635 0.0 1.0 24128 3704 ? S 11:04 0:00 /usr/sbin/apach e2 -k start

postfix 4690 0.0 0.4 5412 1644 ? S 11:19 0:00 pickup -l -t fi fo -u -c

root 4966 0.0 1.0 24128 3704 ? S 11:40 0:00 /usr/sbin/apach e2 -k start

root 5060 0.0 1.0 24128 3704 ? S 11:48 0:00 /usr/sbin/apach e2 -k start

root 5087 0.0 1.0 24128 3704 ? S 11:50 0:00 /usr/sbin/apach e2 -k start

root 5120 0.0 1.0 24128 3704 ? S 11:53 0:00 /usr/sbin/apach e2 -k start

root 5145 0.0 1.0 24128 3704 ? S 11:55 0:00 /usr/sbin/apach e2 -k start

root 5147 0.0 1.0 24128 3704 ? S 11:55 0:00 /usr/sbin/apach e2 -k start

root 5149 0.0 1.0 24128 3704 ? S 11:55 0:00 /usr/sbin/apach e2 -k start

root 5151 0.0 1.0 24128 3704 ? S 11:55 0:00 /usr/sbin/apach e2 -k start

root 5153 0.0 1.0 24128 3704 ? S 11:55 0:00 /usr/sbin/apach e2 -k start

root 5176 0.1 0.6 8064 2540 ? Ss 11:55 0:00 sshd: root@pts/ 0

root 5182 0.0 0.4 2916 1616 pts/0 Ss 11:55 0:00 -bash

www-data 5193 0.0 1.1 24128 4376 ? S 11:56 0:00 /usr/sbin/apach e2 -k start

root 5195 0.0 0.2 2368 932 pts/0 R+ 11:56 0:00 ps auxwww~~~~

>> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net>

Bug reports, feature requests, and downloads at http://mysqltuner.com/

Run with '–help' for additional options and output filtering

-------- General Statistics --------------------------------------------------

[–] Skipped version check for MySQLTuner script

[OK] Currently running supported MySQL version 5.0.51a-3ubuntu5.5

[OK] Operating on 32-bit architecture with less than 2GB RAM

–------ Storage Engine Statistics -------------------------------------------

[–] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

[–] Data in MyISAM tables: 10M (Tables: 63)

[–] Data in InnoDB tables: 30M (Tables: 87)

[!!] Total fragmented tables: 14

–------ Performance Metrics -------------------------------------------------

[–] Up for: 19h 14m 20s (171K q [2.480 qps], 2K conn, TX: 503M, RX: 59M)

[–] Reads / Writes: 77% / 23%

[–] Total buffers: 58.0M global + 2.6M per thread (100 max threads)

[!!] Maximum possible memory usage: 320.5M (88% of installed RAM)

[OK] Slow queries: 0% (40/171K)

[OK] Highest usage of available connections: 3% (3/100)

[OK] Key buffer size / total MyISAM indexes: 16.0M/1.5M

[OK] Key buffer hit rate: 99.4% (259K cached / 1K reads)

[OK] Query cache efficiency: 64.2% (89K cached / 139K selects)

[!!] Query cache prunes per day: 11696

[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 37K sorts)

[!!] Joins performed without indexes: 270

[!!] Temporary tables created on disk: 29% (2K on disk / 8K total)

[OK] Thread cache hit rate: 99% (3 created / 2K connections)

[!!] Table cache hit rate: 1% (64 open / 3K opened)

[OK] Open file limit used: 6% (64/1K)

[OK] Table locks acquired immediately: 100% (67K immediate / 67K locks)

[!!] InnoDB data size / buffer pool: 30.3M/8.0M

–------ Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

MySQL started within last 24 hours - recommendations may be inaccurate

Reduce your overall MySQL memory footprint for system stability

Enable the slow query log to troubleshoot bad queries

Adjust your join queries to always utilize indexes

When making adjustments, make tmptablesize/maxheaptable_size equal

Reduce your SELECT DISTINCT queries without LIMIT clauses

Increase table_cache gradually to avoid file descriptor limits

Variables to adjust:

querycachesize (> 16M)

joinbuffersize (> 128.0K, or always use indexes with joins)

tmptablesize (> 32M)

maxheaptable_size (> 16M)

table_cache (> 64)

innodbbufferpool_size (>= 30M)

Hmm, no major smoking guns in the ps output. I would implement some of the advice from the MySQLTuner, especially the slow query log… that will log any query that takes longer than a set amount of time, which can be really handy for troubleshooting database-related slowdowns.

Yep looks like it's a case of the following:

1) Enable slow query log

2) Add appropriate indexes on tables with slow join queries

3) If you don't use innodb add skip-innodb to your my.cnf

4) Setup a cron to run mysqlcheck -A -o -a

That should help.

Reply

Please enter an answer
Tips:

You can mention users to notify them: @username

You can use Markdown to format your question. For more examples see the Markdown Cheatsheet.

> I’m a blockquote.

I’m a blockquote.

[I'm a link] (https://www.google.com)

I'm a link

**I am bold** I am bold

*I am italicized* I am italicized

Log in to Reply
Community Code of Conduct