MySQL Parameter Problem?

MySQL, PostgreSQL, SQLite
Post Reply
t-eddie
Posts: 45
Joined: 2006-03-07 18:05
 

MySQL Parameter Problem?

Post by t-eddie »

Hallo RootForum-Gemeinde,

ich hab jetzt bei phpmyadmin, unter den Laufzeit-Informationen diverse "rote" Werte entdeckt - weiß aber nicht wie kritisch das wirklich ist....hier mal die Veränderung von 15:30Uhr zu 19:45Uhr

Von 15:30Uhr

Code: Select all

Innodb_buffer_pool_reads 12
Handler_read_rnd  	     40 k
Handler_read_rnd_next  	  1.794 k
Created_tmp_disk_tables   192
Select_full_join  	         198  	
Select_range_check  	    13
Opened_tables  	              1.311
Table_locks_waited  	    5
Abgebrochene Verbindungen 3,80%

Von 19:45UHr

Code: Select all

Innodb_buffer_pool_reads    12
Handler_read_rnd  	        77 k
Handler_read_rnd_next  	     4.245 k
Created_tmp_disk_tables     429
Select_full_join  	            692  	
Select_range_check  	        13
Opened_tables  	                  1.392
Table_locks_waited  	        12
Abgebrochene Verbindungen 5,15%
Auf dem Server läufen 5 Joomla-Webseites + 4 Html Webseiten....zwei der Joomla-Sachen sind Webshops mit relativ vielen Klicks. Ich setze MySQL 5.0.23 auf Debian Etch ein...

Vielleicht kann mir wer einen Tip geben, ob das jetzt kritisch ist - bzw. werden kann.

Danke im voraus.
Top
User avatar
Joe User
Project Manager
Project Manager
Posts: 11191
Joined: 2003-02-27 01:00
Location: Hamburg
Contact:
Contact Joe User
 

Re: MySQL Parameter Problem?

Post by Joe User »

Hast Du die my.cnf bereits an Deine Bedürfnisse angepasst? http://www.rootforum.org/forum/viewtopic.php?t=36343 sollte Dir etwas weiterhelfen können.

Das Debian als Basis für MySQL-Server suboptimal ist dürfte ja mitlerweile allgemein bekannt sein...
PayPal.Me/JoeUser ● FreeBSD Remote Installation
Wings for Life ● Wings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top
t-eddie
Posts: 45
Joined: 2006-03-07 18:05
 

Re: MySQL Parameter Problem?

Post by t-eddie »

Ja den Thread von Dir hab ich schon gesehen....ich wollte den MySQL-Server jetzt erstmal 48h laufen lassen & beobachten, danach dann das tuning-primer.sh Skript drüber laufen lassen und die Ergebnisse posten.
Danach wollte ich mich ans anpassen der my.cnf setzen, um die "hoffentlich" eintretende Verbesserung danach mit dem Script auch messen zu können.

Meine Frage war jetzt auch eher in die Richtung, wenn diese Werte weiter steigen ob das zu einem absturz des MySQL-Servers führen kann?

P.S.: Warum ist Debian für MySQL suboptimal?
Top
User avatar
Joe User
Project Manager
Project Manager
Posts: 11191
Joined: 2003-02-27 01:00
Location: Hamburg
Contact:
Contact Joe User
 

Re: MySQL Parameter Problem?

Post by Joe User »

t-eddie wrote:Meine Frage war jetzt auch eher in die Richtung, wenn diese Werte weiter steigen ob das zu einem absturz des MySQL-Servers führen kann?
Normalerweeise nicht.
t-eddie wrote:P.S.: Warum ist Debian für MySQL suboptimal?
http://www.rootforum.org/forum/viewtopi ... 32#p240232
PayPal.Me/JoeUser ● FreeBSD Remote Installation
Wings for Life ● Wings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top
hornox
Posts: 139
Joined: 2005-09-22 23:09
 

OT

Post by hornox »

Joe User wrote:Das Debian als Basis für MySQL-Server suboptimal ist dürfte ja mitlerweile allgemein bekannt sein...
Gilt das nur für i386 oder auch für die amd64 Version?
Top
User avatar
Joe User
Project Manager
Project Manager
Posts: 11191
Joined: 2003-02-27 01:00
Location: Hamburg
Contact:
Contact Joe User
 

Re: MySQL Parameter Problem?

Post by Joe User »

Da amd64 ein erweitertes i686 ist, gilt es für amd64 nicht...
PayPal.Me/JoeUser ● FreeBSD Remote Installation
Wings for Life ● Wings for Life World Run

„If there’s more than one possible outcome of a job or task, and one
of those outcomes will result in disaster or an undesirable consequence,
then somebody will do it that way.“ -- Edward Aloysius Murphy Jr.
Top
t-eddie
Posts: 45
Joined: 2006-03-07 18:05
 

Re: MySQL Parameter Problem?

Post by t-eddie »

So hier mal die veränderung von Mo zu Mittwoch

26.05. um 19:45Uhr

Code: Select all

    Innodb_buffer_pool_reads    12
    Handler_read_rnd             77 k
    Handler_read_rnd_next          4.245 k
    Created_tmp_disk_tables     429
    Select_full_join                 692     
    Select_range_check             13
    Opened_tables                       1.392
    Table_locks_waited             12
    Abgebrochene Verbindungen 5,15%
28.05. um 8:30Uhr

Code: Select all

    Innodb_buffer_pool_reads    12
    Handler_read_rnd             713 k
    Handler_read_rnd_next          13 M
    Qcache_lowmem_prunes  	69 k
    Created_tmp_disk_tables     3763
    Select_full_join                 6911     
    Select_range_check             18
    Opened_tables                       8392
    Table_locks_waited             49
    Abgebrochene Verbindungen 6,6%
Und tuning-primer.sh sagt:

Code: Select all

Uptime = 1 days 19 hrs 13 min 43 sec
Avg. qps = 9
Total Questions = 1519380
Threads Connected = 9

Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10 sec.
You have 0 out of 1519401 that take longer than 10 sec. to complete
Your long_query_time may be too high, I typically set this under 5 sec.

BINARY UPDATE LOG
The binary update log is enabled

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 7
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 9
Historic max_used_connections = 22
The number of used connections is 22% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 148 M
Configured Max Per-thread Buffers : 265 M
Configured Max Global Buffers : 90 M
Configured Max Memory Limit : 355 M
Physical Memory : 996.50 M
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 15 M
Current key_buffer_size = 64 M
Key cache miss rate is 1 : 431
Key buffer fill ratio = 13.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 16 M
Current query_cache_used = 10 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 62.90 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 132.00 K
You have had 6911 queries where a join could not use an index properly
You have had 18 joins without keys that check for key usage after each row
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 1134 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
You currently have open more than 75% of your open_files_limit
You should set a higher value for open_files_limit in my.cnf

TABLE CACHE
Current table_cache value = 512 tables
You have a total of 860 tables
You have 512 open tables.
Current table_cache hit rate is 5%, while 100% of your table cache is in use
You should probably increase your table_cache

TEMP TABLES
Current max_heap_table_size = 16 M
Current tmp_table_size = 32 M
Of 16067 temp tables, 23% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Created disk tmp tables ratio seems fine

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 41 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 14183
Your table locking seems to be fine
Jetzt werd ich die my.cnf nach der vorgabe von JoeUser mal anpassen und die hoffentlich postiven veränderungen dokumentieren können.
Top
t-eddie
Posts: 45
Joined: 2006-03-07 18:05
 

Re: MySQL Parameter Problem?

Post by t-eddie »

So, ich hab jetzt diverser Änderungen nach den vorgaben von JoeUser gemacht & im Moment läuft der Server ganz gut, neben der "skip-show-databases" Variable die zu leichten Verwirrungen geführt hat, habe ich aber nun doch noch 2-3 Problemchen.

Ich lasse jeden morgen, um 6:25Uhr das "automysql" Backup Script laufen - die Scripte und CronJobs hab ich auch nicht angefasst, aber vorher hab ich nie eine E-Mail über den täglichen Backupstatus bekommen, was auch so gewollt ist, nach der my.cnf anpassung bekomme ich nun immer den Statusbericht. Kann mir da wer mal nen Tipp geben, warum?

Und genau das Script gibt mir auch folgenden Fehler aus:

Code: Select all

mysqldump: Got error: 1146: Table 'eddiesql3.vt_rss' doesn't exist when using LOCK TABLES
mysqldump: Got error: 1146: Table 'kthsql1.besucher' doesn't exist when using LOCK TABLES
Was soll mir der Fehler sagen???

Das Backup läuft ja augenscheinlich super durch:

Code: Select all

======================================================================
AutoMySQLBackup VER 2.5
http://sourceforge.net/projects/automysqlbackup/

Backup of Database Server - vt-server
======================================================================
Backup Start Time Sun Jun 8 06:25:33 CEST 2008
======================================================================
Daily Backup of Database ( eddiesql1 )
Rotating last weeks Backup...
removed `/root/sql-backup/daily/eddiesql1/eddiesql1_2008-06-01_06h26m.Sunday.sql.gz'


Backup Information for /root/sql-backup/daily/eddiesql1/eddiesql1_2008-06-08_06h25m.Sunday.sql
        compressed        uncompressed  ratio uncompressed_name
           1819779             5033892  63.9% /root/sql-backup/daily/eddiesql1/eddiesql1_2008-06-08_06h25m.Sunday.sql
----------------------------------------------------------------------
Daily Backup of Database ( eddiesql3 )
Rotating last weeks Backup...
removed `/root/sql-backup/daily/eddiesql3/eddiesql3_2008-06-01_06h26m.Sunday.sql.gz'


Backup Information for /root/sql-backup/daily/eddiesql3/eddiesql3_2008-06-08_06h25m.Sunday.sql
        compressed        uncompressed  ratio uncompressed_name
               447                 917  57.5% /root/sql-backup/daily/eddiesql3/eddiesql3_2008-06-08_06h25m.Sunday.sql
----------------------------------------------------------------------
Daily Backup of Database ( eddiesql5 )
Rotating last weeks Backup...
removed `/root/sql-backup/daily/eddiesql5/eddiesql5_2008-06-01_06h26m.Sunday.sql.gz'


Backup Information for /root/sql-backup/daily/eddiesql5/eddiesql5_2008-06-08_06h25m.Sunday.sql
        compressed        uncompressed  ratio uncompressed_name
            142010              812752  82.5% /root/sql-backup/daily/eddiesql5/eddiesql5_2008-06-08_06h25m.Sunday.sql
----------------------------------------------------------------------
Daily Backup of Database ( kthsql1 )
Rotating last weeks Backup...
removed `/root/sql-backup/daily/kthsql1/kthsql1_2008-06-01_06h26m.Sunday.sql.gz'


Backup Information for /root/sql-backup/daily/kthsql1/kthsql1_2008-06-08_06h25m.Sunday.sql
        compressed        uncompressed  ratio uncompressed_name
               443                 909  57.3% /root/sql-backup/daily/kthsql1/kthsql1_2008-06-08_06h25m.Sunday.sql
----------------------------------------------------------------------
Daily Backup of Database ( kthsql2 )
Rotating last weeks Backup...
removed `/root/sql-backup/daily/kthsql2/kthsql2_2008-06-01_06h26m.Sunday.sql.gz'


Backup Information for /root/sql-backup/daily/kthsql2/kthsql2_2008-06-08_06h25m.Sunday.sql
        compressed        uncompressed  ratio uncompressed_name
            504965             3636552  86.1% /root/sql-backup/daily/kthsql2/kthsql2_2008-06-08_06h25m.Sunday.sql
----------------------------------------------------------------------
Daily Backup of Database ( mysql )
Rotating last weeks Backup...
removed `/root/sql-backup/daily/mysql/mysql_2008-06-01_06h26m.Sunday.sql.gz'


Backup Information for /root/sql-backup/daily/mysql/mysql_2008-06-08_06h25m.Sunday.sql
        compressed        uncompressed  ratio uncompressed_name
             95573              318068  70.0% /root/sql-backup/daily/mysql/mysql_2008-06-08_06h25m.Sunday.sql
----------------------------------------------------------------------
Daily Backup of Database ( sauermannsql1 )
Rotating last weeks Backup...
removed `/root/sql-backup/daily/sauermannsql1/sauermannsql1_2008-06-01_06h26m.Sunday.sql.gz'


Backup Information for /root/sql-backup/daily/sauermannsql1/sauermannsql1_2008-06-08_06h25m.Sunday.sql
        compressed        uncompressed  ratio uncompressed_name
             39664              180258  78.0% /root/sql-backup/daily/sauermannsql1/sauermannsql1_2008-06-08_06h25m.Sunday.sql
----------------------------------------------------------------------
Daily Backup of Database ( syscp )
Rotating last weeks Backup...
removed `/root/sql-backup/daily/syscp/syscp_2008-06-01_06h26m.Sunday.sql.gz'


Backup Information for /root/sql-backup/daily/syscp/syscp_2008-06-08_06h25m.Sunday.sql
        compressed        uncompressed  ratio uncompressed_name
             52920              239215  77.9% /root/sql-backup/daily/syscp/syscp_2008-06-08_06h25m.Sunday.sql
----------------------------------------------------------------------
Daily Backup of Database ( umsonstsql1 )
Rotating last weeks Backup...
removed `/root/sql-backup/daily/umsonstsql1/umsonstsql1_2008-06-01_06h26m.Sunday.sql.gz'


Backup Information for /root/sql-backup/daily/umsonstsql1/umsonstsql1_2008-06-08_06h25m.Sunday.sql
        compressed        uncompressed  ratio uncompressed_name
           1168967             3373600  65.4% /root/sql-backup/daily/umsonstsql1/umsonstsql1_2008-06-08_06h25m.Sunday.sql
----------------------------------------------------------------------
Backup End Sun Jun 8 06:25:37 CEST 2008
======================================================================
Total disk space used for backup storage..
Size - Location
251M /root/sql-backup

======================================================================
If you find AutoMySQLBackup valuable please make a donation at
http://sourceforge.net/project/project_donations.php?group_id=101066
======================================================================
Und trotzdem bekomme ich danach, folgende Fehlermeldung:

Code: Select all

run-parts: /etc/cron.daily/sql-backup exited with return code 1
Ich bin für jeden Tipp dankbar...
Top
oxygen
Posts: 2138
Joined: 2002-12-15 00:10
Location: Bergheim
 

Re: MySQL Parameter Problem?

Post by oxygen »

t-eddie wrote:

Code: Select all

mysqldump: Got error: 1146: Table 'eddiesql3.vt_rss' doesn't exist when using LOCK TABLES
mysqldump: Got error: 1146: Table 'kthsql1.besucher' doesn't exist when using LOCK TABLES
Was soll mir der Fehler sagen???
Ein Schuss ins Blaue: Das Script versucht Tabellen zu sperren die den MEMORY/HEAP Handler nutzten. Das klappt natürlich nicht. Macht auch nicht gerade viel Sinn.
Top
t-eddie
Posts: 45
Joined: 2006-03-07 18:05
 

Re: MySQL Parameter Problem?

Post by t-eddie »

:-D

Endlich geschafft - dabei war der Fehler so blöd...ich habe mir heute die beiden betroffenen DB´s mal genau angeschaut - in phpMyAdmin wurden die Tabellen noch angezeigt - haben aber gar nicht mehr existert, d.h. man konnte nicht darauf zugreifen. Ich habe dann unter /var/lib/mysql/db-name* mir die entsprechende Tabelle gesucht - gelöscht & jetzt funktioniert auch alles wieder - woher die Tabellenleichen aber kamen, kann ich mir nicht erklären.

EDIT: Eine Tabelle war nicht mehr da, bei der anderen war Groß und Kleinschreibung falsch
Top
Post Reply

Return to “Datenbanken”