Dual Quad Core Xeon CPU but still high load

[ali_kiyani]

Hi,

 

I have a dedicated server with following specs:

 

1. Two Intel Xeon-Harpertown 5430-Quadcore[2.66 Ghz]

2. 4GB DDRII RAM

3. 500GB SATAII HD

4. CentOS 5.5 64-bit

 

Problem is that even with such specs MySQL still takes high CPU usage. It almost remain above 150% all the time and most of the time goes above 300%. I came to know about this after running "top" command.

Now the thing is as soon as I run "watch mysqladmin pr" to see what's going on then I don't see any problem. Although there are queries running but they are not like some very heavy queries except may be one or two.

 

I ran "mysqltunner.pl" and it showed me the following:

 

 

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

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

[--] Data in MyISAM tables: 362M (Tables: 255)

[--] Data in InnoDB tables: 880K (Tables: 55)

[!!] Total fragmented tables: 2

 

-------- Performance Metrics -------------------------------------------------

[--] Up for: 3h 26m 51s (1M q [138.122 qps], 43K conn, TX: 3B, RX: 246M)

[--] Reads / Writes: 93% / 7%

[--] Total buffers: 830.0M global + 3.9M per thread (300 max threads)

[OK] Maximum possible memory usage: 1.9G (50% of installed RAM)

[OK] Slow queries: 0% (47/1M)

[OK] Highest usage of available connections: 6% (18/300)

[OK] Key buffer size / total MyISAM indexes: 256.0M/169.9M

[OK] Key buffer hit rate: 100.0% (5B cached / 36K reads)

[OK] Query cache efficiency: 84.2% (1M cached / 1M selects)

[!!] Query cache prunes per day: 338346

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

[!!] Temporary tables created on disk: 38% (160K on disk / 420K total)

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

[OK] Table cache hit rate: 98% (446 open / 452 opened)

[OK] Open file limit used: 1% (663/65K)

[OK] Table locks acquired immediately: 99% (684K immediate / 684K locks)

[OK] InnoDB data size / buffer pool: 880.0K/8.0M

 

-------- Recommendations -----------------------------------------------------

General recommendations:

Run OPTIMIZE TABLE to defragment tables for better performance

MySQL started within last 24 hours - recommendations may be inaccurate

Temporary table size is already large - reduce result set size

Reduce your SELECT DISTINCT queries without LIMIT clauses

Variables to adjust:

query_cache_size (> 64M)

------------------------------------------------------------------------------

 

 

As you can see most of the parameters are correct except a few like 2 defrag tables and small query cache size. Even if I fix this I don't see any noticeable performance increase. So now I have turned my attention to this "Temporary tables created on disk 38%"

Do you think it is because of this MySQL is taking too much CPU time? How can I improve it? Or do you think there is something else after look at above result?

 

Currently my setting regarding temporary tables in MySQL config file are:

 

tmp_table_size=1000M

max_heap_table_size=500M

 

Even if I increase these values, MySQL still created temporary tables on disk. How do I fix it? I can see mysqltuner.pl also says that I need reduce my result set but if I give it plenty of RAM to create temp tables shouldn't the problem go away?

 

 

Thanks

[requinix]

Couple questions:

- Are you creating temporary tables in your queries (like, explicitly)?

- Are you running SELECTs without LIMITs when you shouldn't be?

- Are there actually 310 tables? Pardon the accusation but are you sure you need all of them?

 

Basically, what kind of demand are you putting on it?

[ali_kiyani]

Couple questions:

- Are you creating temporary tables in your queries (like, explicitly)?

- Are you running SELECTs without LIMITs when you shouldn't be?

- Are there actually 310 tables? Pardon the accusation but are you sure you need all of them?

 

Basically, what kind of demand are you putting on it?

 

1. No

2. Yes there may be several queries. But the thing is I am not sure if just some of those queries will create such a problem. Could it be due to something else like I/O wait!?

3. There are other applications also installed like cpanel, mail server e.t.c. who have their own databases so this is total of all tables not just my application.