MySQL Performance: MySQL => Slow MySQL on idle server

Members

Help

Home

Home » Performance » MySQL » Slow MySQL on idle server

Show: Today's Messages :: Show Polls :: Message Navigator

Slow MySQL on idle server [message #3691]

Tue, 04 November 2008 05:21

Marki
Messages: 4
Registered: March 2008
Location: Bratislava, Slovakia

Junior Member

We have new server with 4-core CPU and 3 GB RAM running mysql 5.0, disks are almost idle (iowait 1-5%). /var/lib/mysql has 2.9 GB used, but most used tables fit in memory.
But some queries are still taking almost 1.5 seconds. What can be the reason? CPU usage is about 15-25% only. What can be limiting MySQL if it is not CPU and not DISK?
From slow.log I see for example this:

Count         : 941  (48.06%)
Time          : 1396.313552 s total, 1.483861 s avg, 1.319578 s to 4.09349 s max  (35.55%)
  95% of Time : 1280.66036 s total, 1.43411 s avg, 1.319578 s to 1.710587 s max
Lock Time     : 102.633 ms total, 109 ľs avg, 44 ľs to 16.016 ms max  (3.00%)
  95% of Lock : 74.149 ms total, 83 ľs avg, 44 ľs to 131 ľs max
Rows sent     : 10 avg, 10 to 10 max  (0.34%)
Rows examined : 69.90k avg, 69.75k to 70.14k max  (45.74%)

QC hit        : 0% (0)
Full scan     : 0% (0)
Full join     : 0% (0)
Tmp table     : 100% (941)
Disk tmp table: 100% (941)
Filesort      : 100% (941)
Disk filesort : 0% (0)
Merge passes  : 0 total, 0 avg, 0 to 0 max

So it should not be locking issues. Most tables are myisam, some are InnoDB. Some myisam are because of FULLTEXT indexes, others are mysaim only for historical reasons. I don't know if converting them to InnoDB would have much effect. They are not written too much.

If I understand it well, mysql is multi-threaded, but each query uses only 1 CPU. But I don't believe that this quite simple query would require entire 1 CPU for 1.5 seconds...

Report message to a moderator

Re: Slow MySQL on idle server [message #3699 is a reply to message #3691 ]

Wed, 05 November 2008 09:00

pascalhofmann
Messages: 1
Registered: November 2008

Junior Member

Tmp table : 100% (941)
Disk tmp table: 100% (941)

MySQL creates disk based temporary tables for your queries - either because you are using text/blob fields which the MEMORY storage engine can't handle - or because tmp_table_size/max_heap_table_size are to low.

- Try to avoid text/blob fields.
- If you are using no text/blob fields in your query, increase tmp_table_size/max_heap_table_size.
- If you need to use text/blob fields in your query, set tmpdir to a ramdisk.

Regards

Pascal

Report message to a moderator

Re: Slow MySQL on idle server [message #3700 is a reply to message #3699 ]

Wed, 05 November 2008 09:35

Marki
Messages: 4
Registered: March 2008
Location: Bratislava, Slovakia

Junior Member

Hi,

Thanks for your explanation. But are these on-disk tmp tables so slow on idle machine? I suppose that they should be fully cached by OS (linux kernel 2.6) and they exist only for 1-2 seconds, so won't be flushed to HDD. I will test the tmpdir on ramdisk next time the mysql will be restarted (or can tmpdir be changed online?)

Yes I know it would be best to avoid having tmp tables, but developers are just too lazy to rewrite the queries...

Report message to a moderator

Previous Topic:	Improving time for creating indices in MySQL ?
Next Topic:	kill null state query

Goto Forum:

-=] Back to Top [=-

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 6 21:37:04 EDT 2009

Total time taken to generate the page: 0.01478 seconds

.:: Contact :: Home ::.

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.