Re: 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 +, 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-requ...@lists.debian.org with a subject of "unsubscribe". Trouble? Contact listmas...@lists.debian.org Archive: http://lists.debian.org/jo918g$e2o$1...@dough.gmane.org
Re: MySQL seems to be running too slow; LONG
On Sun, 2012-05-06 at 14:09 +, 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-requ...@lists.debian.org with a subject of "unsubscribe". Trouble? Contact listmas...@lists.debian.org Archive: http://lists.debian.org/1336355671.28659.7.ca...@beast.johnwfoster.com
Re: 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-requ...@lists.debian.org with a subject of "unsubscribe". Trouble? Contact listmas...@lists.debian.org Archive: http://lists.debian.org/jo60n6$kir$8...@dough.gmane.org
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-requ...@lists.debian.org with a subject of "unsubscribe". Trouble? Contact listmas...@lists.debian.org Archive: http://lists.debian.org/1336242393.2212.12.ca...@beast.johnwfoster.com
