FAQ Search Today's Posts Mark Forums Read
» Video Reviews

» Linux Archive

Linux-archive is a website aiming to archive linux email lists and to make them easily accessible for linux users/developers.


» Sponsor

» Partners

» Sponsor

Go Back   Linux Archive > Debian > Debian User

 
 
LinkBack Thread Tools
 
Old 05-05-2012, 06:26 PM
"John W. Foster"
 
Default MySQL seems to be running too slow; LONG

I got this output from mysqltuner:
Any advice is appreciated: The main issues is that this db server
handles a Mediawiki database and when I upload files it takes way to
long to do its job likewise when serving up the data to produce the
webpages.
Output:

> -------- General Statistics --------------------------------------------------
> [--] Skipped version check for MySQLTuner script
> [OK] Currently running supported MySQL version 5.1.61-0+squeeze1
> [OK] Operating on 64-bit architecture
>
> -------- Storage Engine Statistics -------------------------------------------
> [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
> [--] Data in MyISAM tables: 40M (Tables: 100)
> [--] Data in InnoDB tables: 764M (Tables: 71)
> [--] Data in MEMORY tables: 0B (Tables: 1)
> [!!] Total fragmented tables: 73
>
> -------- Performance Metrics -------------------------------------------------
> [--] Up for: 5h 55m 13s (211K q [9.909 qps], 1K conn, TX: 3B, RX: 43M)
> [--] Reads / Writes: 96% / 4%
> [--] Total buffers: 343.0M global + 2.7M per thread (200 max threads)
> [OK] Maximum possible memory usage: 880.5M (22% of installed RAM)
> [OK] Slow queries: 0% (8/211K)
> [OK] Highest usage of available connections: 2% (4/200)
> [OK] Key buffer size / total MyISAM indexes: 20.0M/16.2M
> [OK] Key buffer hit rate: 100.0% (7M cached / 1K reads)
> [OK] Query cache efficiency: 50.1% (92K cached / 185K selects)
> [!!] Query cache prunes per day: 36659
> [!!] Sorts requiring temporary tables: 11% (11 temp sorts / 98 sorts)
> [!!] Temporary tables created on disk: 38% (2K on disk / 6K total)
> [OK] Thread cache hit rate: 99% (4 created / 1K connections)
> [!!] Table cache hit rate: 3% (128 open / 3K opened)
> [OK] Open file limit used: 1% (16/1K)
> [OK] Table locks acquired immediately: 99% (120K immediate / 120K locks)
> [!!] InnoDB data size / buffer pool: 764.0M/285.0M
>
> -------- Recommendations -----------------------------------------------------
> General recommendations:
> Run OPTIMIZE TABLE to defragment tables for better performance
> MySQL started within last 24 hours - recommendations may be inaccurate
> Enable the slow query log to troubleshoot bad queries
> When making adjustments, make tmp_table_size/max_heap_table_size equal
> Reduce your SELECT DISTINCT queries without LIMIT clauses
> Increase table_cache gradually to avoid file descriptor limits
> Variables to adjust:
> query_cache_size (> 16M)
> sort_buffer_size (> 1M)
> read_rnd_buffer_size (> 256K)
> tmp_table_size (> 20M)
> max_heap_table_size (> 20M)
> table_cache (> 128)
> innodb_buffer_pool_size (>= 764M)

One curious thing I have noted is that the actual database has 3 kinds
of table structures: innodb, myisam, & memory;
also collation is mostly binary, but 3 tabls are latin1_swedish.



hardware is:

> Operating system
> Debian Linux 6.0
> Webmin version
> 1.580
> Time on system
> Sat May 5 13:18:01 2012
> Kernel and CPU
> Linux 2.6.32-5-amd64 on x86_64
> Processor information
> AMD Processor model unknown, 6
> cores
> System uptime
> 6 hours, 02 minutes
> Running processes
> 265
> CPU load averages
> 0.53 (1 min) 0.60 (5 mins) 0.52 (15
> mins)
> CPU usage
> 26% user, 2% kernel, 0% IO, 73%
> idle
> Real memory
> 3.87 GB total, 2.13 GB used
>
>
> Virtual memory
> 7.57 GB total, 16.61 MB used
>
>
> Local disk space
> 909.45 GB total, 92.27 GB used
>
>
> Package updates
> All installed packages are up to
> date
>

I know I need to add more memory & will do so:
any other tips are appreciated.
Thanks frosty



--
To UNSUBSCRIBE, email to debian-user-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org
Archive: 1336242393.2212.12.camel@beast.johnwfoster.com">ht tp://lists.debian.org/1336242393.2212.12.camel@beast.johnwfoster.com
 
Old 05-06-2012, 02:09 PM
Camaleón
 
Default MySQL seems to be running too slow; LONG

On Sat, 05 May 2012 13:26:33 -0500, John W. Foster wrote:

> I got this output from mysqltuner:
> Any advice is appreciated: The main issues is that this db server
> handles a Mediawiki database and when I upload files it takes way to
> long to do its job likewise when serving up the data to produce the
> webpages.
> Output:

(...)

> -------- Recommendations-----------------------------------------------------
> General recommendations:
> Run OPTIMIZE TABLE to defragment tables for better performance
> MySQL started within last 24 hours - recommendations may be
> inaccurate Enable the slow query log to troubleshoot bad queries
> When making adjustments, make tmp_table_size/max_heap_table_size
> equal Reduce your SELECT DISTINCT queries without LIMIT clauses
> Increase table_cache gradually to avoid file descriptor limits
> Variables to adjust:
> query_cache_size (> 16M)
> sort_buffer_size (> 1M)
> read_rnd_buffer_size (> 256K)
> tmp_table_size (> 20M)
> max_heap_table_size (> 20M)
> table_cache (> 128)
> innodb_buffer_pool_size (>= 764M)

(...)

It seems the script did a good job by telling you what's what you can
tweak. Have you made any of the recommended changes?

> Real memory
> 3.87 GB total, 2.13 GB used

(...)

> I know I need to add more memory & will do so: any other tips are
> appreciated.

You have still not run out of memory but adding RAM is always of help :-)

Greetings,

--
Camaleón


--
To UNSUBSCRIBE, email to debian-user-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org
Archive: jo60n6$kir$8@dough.gmane.org">http://lists.debian.org/jo60n6$kir$8@dough.gmane.org
 
Old 05-07-2012, 01:54 AM
"John W. Foster"
 
Default MySQL seems to be running too slow; LONG

On Sun, 2012-05-06 at 14:09 +0000, Camaleón wrote:
> On Sat, 05 May 2012 13:26:33 -0500, John W. Foster wrote:
>
> > I got this output from mysqltuner:
> > Any advice is appreciated: The main issues is that this db server
> > handles a Mediawiki database and when I upload files it takes way to
> > long to do its job likewise when serving up the data to produce the
> > webpages.
> > Output:
>
> (...)
>
> > -------- Recommendations-----------------------------------------------------
> > General recommendations:
> > Run OPTIMIZE TABLE to defragment tables for better performance
> > MySQL started within last 24 hours - recommendations may be
> > inaccurate Enable the slow query log to troubleshoot bad queries
> > When making adjustments, make tmp_table_size/max_heap_table_size
> > equal Reduce your SELECT DISTINCT queries without LIMIT clauses
> > Increase table_cache gradually to avoid file descriptor limits
> > Variables to adjust:
> > query_cache_size (> 16M)
> > sort_buffer_size (> 1M)
> > read_rnd_buffer_size (> 256K)
> > tmp_table_size (> 20M)
> > max_heap_table_size (> 20M)
> > table_cache (> 128)
> > innodb_buffer_pool_size (>= 764M)
>
> (...)
>
> It seems the script did a good job by telling you what's what you can
> tweak. Have you made any of the recommended changes?
actually I made all the suggested changes; My conundrum is that after I
made them, the changes were not indicated as being sufficient. May be
that the script does not do that; Example the original run of mysqltuner
showed:
tmp_table_size (> 16M)
max_heap_table_size (> 16M)

I changed the values to;

tmp_table_size (> 20M)
max_heap_table_size (> 20M)
If I am correctly understanding the values indicated it would seem that
the 20M size is now insufficient. Is that correct?
>
> > Real memory
> > 3.87 GB total, 2.13 GB used
>
> (...)
>
> > I know I need to add more memory & will do so: any other tips are
> > appreciated.
>
> You have still not run out of memory but adding RAM is always of help :-)
>
> Greetings,
>
> --
> Camaleón
>
>




--
To UNSUBSCRIBE, email to debian-user-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org
Archive: 1336355671.28659.7.camel@beast.johnwfoster.com">ht tp://lists.debian.org/1336355671.28659.7.camel@beast.johnwfoster.com
 
Old 05-07-2012, 05:37 PM
Camaleón
 
Default MySQL seems to be running too slow; LONG

On Sun, 06 May 2012 20:54:31 -0500, John W. Foster wrote:

> On Sun, 2012-05-06 at 14:09 +0000, Camaleón wrote:

>> (...)
>>
>> It seems the script did a good job by telling you what's what you can
>> tweak. Have you made any of the recommended changes?

> actually I made all the suggested changes; My conundrum is that after I
> made them, the changes were not indicated as being sufficient. May be
> that the script does not do that; Example the original run of mysqltuner
> showed:
> tmp_table_size (> 16M)
> max_heap_table_size (> 16M)
>
> I changed the values to;
>
> tmp_table_size (> 20M)
> max_heap_table_size (> 20M)
> If I am correctly understanding the values indicated it would seem that
> the 20M size is now insufficient. Is that correct?

If you restarted MySQL service after making the suggested modifications
(which are reflected on the new values) the script is telling you that
the new values are still not enough.

Greetings,

--
Camaleón


--
To UNSUBSCRIBE, email to debian-user-REQUEST@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmaster@lists.debian.org
Archive: jo918g$e2o$11@dough.gmane.org">http://lists.debian.org/jo918g$e2o$11@dough.gmane.org
 

Thread Tools




All times are GMT. The time now is 11:34 PM.

VBulletin, Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO ©2007, Crawlability, Inc.
Copyright ©2007 - 2008, www.linux-archive.org