Codeigniter performance

[eluser]kgirl[/eluser]
Hi everyone I hope somebody can help giving me and idea what's going with my project.

We have A Dedicated Server, 4GB of memory, CEntOS 5.7 x86_64 bits in mediatemple

Recently we use Codeigniter to develop a news portal. This portal uses de last version of Codeigniter. We optimize all queries(use explain to check more detail about each query), use the a CI's library to use cache for some information in our home section (not news), we optimize the whole site, the speed right now is like 5 to 7 seconds and with the old news portal structure was 21- 23 seconds to load the home page.

Well we were excited to try the new version, we did the change on friday night, to do tests and fix and error if appears. Nothing happens with the code only when I check on monday the server, THE CPU was very high , stills high

When I run "top" command on my Linux it shows that MySQL is taking 400%-500% CPU usage. But when I run "watch mysqladmin pr" command to see a list of queries being run by MySQL then it shows just 2, 3 queries and that too only taking 2, 3 seconds.

The "top" command shows MySQL constantly fluctuating CPU usage like 130%, 200%, 340%, 230%, 400%, 440%, 635%, 335% e.t.c.

Anoher thing we notice is WE HAVE A LOT OF SLEEP PROCESS, A LOT, And there are consuming Key buffer

We decide to comment all the code, first one by one query and seems to the cpu decrease but there was a % of cpu yet(20%, 36%,80%, 110%) so we commented all the site, nothing of html or queries, just a blank page and stills that percentage(20%, 36%,80%, 110%) .

We dont have a idea was the problem, if there a CI configuration, we have persitent conection in TRUE and with the old structure we dont have this.

$db['test']['pconnect'] = TRUE;

Can anyone help me!!!

[eluser]Otemu[/eluser]
Have you tried profiling your application

The Profiler Class will display benchmark results, queries you have run, and $_POST data at the bottom of your pages. This information can be useful during development in order to help with debugging and optimization.

[eluser]kgirl[/eluser]
Thanks for answer, we used that tool

[eluser]CroNiX[/eluser]
try turning pconnect off/FALSE. It causes problems.

[eluser]Abel A.[/eluser]
There's so many possible causes for the high cpu...
CI is very light and I highly doubt it's CI.

There's no need to have persistent connections on, 99% of apps don't need it. Set pconnect to false.

For your sleeping processes...I don't think they're the issue here. It's most likely that your app is not optimized correctly.

Have you used CI's query cache feature? very useful if your queries are joining 3+ tables. CI also has full page caching! great for caching say a popular news article.

Have you tried mysqltuner?

Code:

wget http://mysqltuner.pl/mysqltuner.pl
chmod 770 mysqltuner.pl
./mysqltuner.pl

Have you enabled slow query log? how does your my.cnf look like?


I run a site with 10k users and over 1M posts on a VPS with 1GB of RAM and 2 vCPUs

I average 100 queries per second and I constantly have around 200 users online at once. It really all depends on how well your server is optimized.

My setup is the following(which I recommend):
CentOS 32bit (64bit if you have the ram)
PHP-FPM 5.3.x w/ APC
nginx 1.x
mysql 5.5.x

Again, check and see what's the actual root cause of the problem. Check what particular queries are causing the high CPU and optimize that page. Don't run unnecessary queries. Configuring your MySql server can be difficult, and that's a whole new ballgame, but mysqltuner is a great tool for starters. One thing that I found bad with CI is their session table. The text field which stores the session data is not needed, instead change the text field to varchar 500, 1000, etc. depending how big your longest data string is. The session data is actually not stored in the same row when using the text field, instead it points where it's stored because each row can only have approx 65kb of data. Think of it as query join when using text/blob fields.

If money is not an issue, then I would suggest an upgrade. I know MediaTemplate are one of the best quality providers out there, but they are pricey. When it comes to database intense applications, IO and RAM are top priority. Slow hdds such as the 7200RPMs are trash for database applications. I would suggest you get 8 x 15k RPM hard drives in RAID-10 for best results. Cheaper solutions are 2 x 15k RPM in RAID-1, or 2 x SSD in RAID-1 (Intel or Samsung SSDs only). There's raid controllers that support 1 SSD and 1 SATA drive in RAID-1, for best performance for the buck.

Hope that helps.

[eluser]kgirl[/eluser]
Hi thanks for answering

The mysql process is the reason that the CPU IS turning very high.

I used mysqltuner tool, right now these are the results of running


-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.54
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 114M (Tables: 246)
[--] Data in InnoDB tables: 489M (Tables: 223)
[!!] Total fragmented tables: 228

-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 3h 2m 57s (7M q [75.919 qps], 657K conn, TX: 5B, RX: 1B)
[--] Reads / Writes: 61% / 39%
[--] Total buffers: 1.1G global + 6.8M per thread (200 max threads)
[OK] Maximum possible memory usage: 2.4G (46% of installed RAM)
[OK] Slow queries: 0% (83/7M)
[OK] Highest usage of available connections: 39% (78/200)
[OK] Key buffer size / total MyISAM indexes: 32.0M/20.2M
[OK] Key buffer hit rate: 100.0% (64M cached / 26K reads)
[!!] Query cache efficiency: 11.3% (364K cached / 3M selects)
[!!] Query cache prunes per day: 2895
[OK] Sorts requiring temporary tables: 9% (232K temp sorts / 2M sorts)
[!!] Joins performed without indexes: 307926
[OK] Temporary tables created on disk: 5% (27K on disk / 511K total)
[OK] Thread cache hit rate: 99% (158 created / 657K connections)
[!!] Table cache hit rate: 13% (512 open / 3K opened)
[OK] Open file limit used: 35% (432/1K)
[OK] Table locks acquired immediately: 99% (5M immediate / 5M locks)
[OK] InnoDB data size / buffer pool: 489.9M/1.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_limit (> 16M, or use smaller result sets)
query_cache_size (> 32M)
join_buffer_size (> 4.0M, or always use indexes with joins)
table_cache (> 512)

I said right now because I run this tool more than 20 times already :S. I adjust the variables that appears in the REcommendations and again CPU turn very high!...So I have to go back.

Also I turn on the mysql log slow query to see what's happen, and we change a lot of queries, but I guess something is missing. There are a lot of information out there and it's hard to interpret all the mysql variables and the perfomance

[eluser]kgirl[/eluser]
Also I run the mysqlreport and the result are this


MySQL 5.1.54 uptime 1 3:10:18 Wed Jun 6 17:25:07 2012

__ Key _________________________________________________________________
Buffer used 14.34M of 32.00M %Used: 44.80
Current 14.52M %Usage: 45.39
Write hit 92.40%
Read hit 99.96%

__ Questions ___________________________________________________________
Total 7.43M 76.0/s
DMS 4.66M 47.7/s %Total: 62.75
Com_ 1.74M 17.8/s 23.43
COM_QUIT 660.85k 6.8/s 8.89
QC Hits 365.77k 3.7/s 4.92
+Unknown 218 0.0/s 0.00
Slow 10 s 83 0.0/s 0.00 %DMS: 0.00 Log: OFF
DMS 4.66M 47.7/s 62.75
SELECT 2.88M 29.4/s 38.76 61.76
UPDATE 1.78M 18.2/s 23.95 38.17
INSERT 2.46k 0.0/s 0.03 0.05
DELETE 684 0.0/s 0.01 0.01
REPLACE 16 0.0/s 0.00 0.00
Com_ 1.74M 17.8/s 23.43
set_option 777.77k 8.0/s 10.47
change_db 535.40k 5.5/s 7.21
commit 202.71k 2.1/s 2.73

__ SELECT and Sort _____________________________________________________
Scan 3.13M 32.0/s %SELECT: 108.59
Range 7.72k 0.1/s 0.27
Full join 309.83k 3.2/s 10.76
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 2.41M 24.7/s
Sort range 21.58k 0.2/s
Sort mrg pass 233.87k 2.4/s

__ Query Cache _________________________________________________________
Memory usage 17.26M of 32.00M %Used: 53.93
Block Fragmnt 12.39%
Hits 365.77k 3.7/s
Inserts 491.09k 5.0/s
Insrtrune 150.50:1 5.0/s
Hit:Insert 0.74:1

__ Table Locks _________________________________________________________
Waited 17.60k 0.2/s %Total: 0.31
Immediate 5.71M 58.4/s

__ Tables ______________________________________________________________
Open 512 of 512

[eluser]kgirl[/eluser]
I alredy turn off the pconnect variable in CI and the cpu goes down but still high like 150%, 200%, 300% even something 500%.

[eluser]Abel A.[/eluser]
Sorry, I forgot to mentioned that mysqltuner works on a 24 hour clock...Run mysqltuner once, adjust the fields per its recommendation. Wait 24 hours and run mysqltuner again. Basically you need to let your DB run for mysqltuner to give better recommendations.

On that same note, mysqltuner doesn't give accurate advice on the join_buffer_size, since you have 4GB of ram, I suggest you set the join_buffer_size = 1M and not higher.

Your Query cache efficiency is horrible!

Try these:

Code:

key_buffer = 128M
query_cache_limit = 2M
query_cache_size = 256M
join_buffer_size = 1M
table_cache = 2048

Let mysql run for 24 hours before you run the mysqltuner again because it will give you inaccurate recommendations.

Also, can you post a sample of your slow queries?

[eluser]Abel A.[/eluser]
Can you also post these results:

Code:

uptime

And...

Code:

free -m

Install iostat:

Code:

yum install sysstat

then post the output of this:

Code:

iostat

And this:

Code:

cat /proc/cpuinfo

and this too:

Code:

dd if=/dev/zero of=test bs=64k count=16k conv=fdatasync; rm test

When promoted, type yes to delete the test file


You're going to need to use ssh and have root access to perform some of the commands above.