Forum: PC Hard- und Software MySQL Experte gesucht um DB auf Raspy zu optimieren


von qler (Gast)


Lesenswert?

Hallo Leute,

Mcöhte die MySQL DB auf meinem Raspy 3 optimieren.
Habe dazu 2 Scripts ausprobiert, die mir schon einige Anhaltspunkte 
gegeben habe.
Leider komme ich nicht mit der Meldung unter den Temp_Tables zurecht.
Ich habe doch schon 512MB zugewiesen !!!

Hier mal das Log:
1
        -- MYSQL PERFORMANCE TUNING PRIMER --
2
             - By: Matthew Montgomery -
3
4
MySQL Version 4.1.22mysql4-log armv7l is EOL please upgrade to MySQL 4.1 or later
5
6
Uptime = 0 days 0 hrs 5 min 58 sec
7
Avg. qps = 4
8
Total Questions = 1624
9
Threads Connected = 1
10
11
Warning: Server has not been running for at least 48hrs.
12
It may not be safe to use these recommendations
13
14
To find out more information on how each of these
15
runtime variables effects performance visit:
16
http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html
17
Visit http://www.mysql.com/products/enterprise/advisors.html
18
for info about MySQL's Enterprise Monitoring and Advisory Service
19
20
SLOW QUERIES
21
The slow query log is NOT enabled.
22
Current long_query_time = 10 sec.
23
You have 0 out of 1638 that take longer than 10 sec. to complete
24
Your long_query_time seems to be fine
25
26
BINARY UPDATE LOG
27
The binary update log is enabled
28
29
WORKER THREADS
30
Current thread_cache_size = 0
31
Current threads_cached = 0
32
Current threads_per_sec = 1
33
Historic threads_per_sec = 0
34
Your thread_cache_size is fine
35
36
MAX CONNECTIONS
37
Current max_connections = 8
38
Current threads_connected = 1
39
Historic max_used_connections = 2
40
The number of used connections is 25% of the configured maximum.
41
Your max_connections variable seems to be fine.
42
43
INNODB STATUS
44
Cannot parse InnoDB stats prior to 5.0.x
45
ERROR 1227 (HY000) at line 1: Access denied; you need the SUPER privilege for this operation
46
Current innodb_buffer_pool_size = 8 M
47
Depending on how much space your innodb indexes take up it may be safe
48
to increase this value to up to 2 / 3 of total system memory
49
50
MEMORY USAGE
51
Max Memory Ever Allocated : 157 M
52
Configured Max Per-thread Buffers : 12 M
53
Configured Max Global Buffers : 154 M
54
Configured Max Memory Limit : 166 M
55
Physical Memory : 925 M
56
Max memory limit seem to be within acceptable norms
57
58
KEY BUFFER
59
Current MyISAM index space = 0 bytes
60
Current key_buffer_size = 16 M
61
Key cache miss rate is 1 : 2
62
Key buffer free ratio = 58 %
63
Your key_buffer_size seems to be fine
64
65
QUERY CACHE
66
Query cache is enabled
67
Current query_cache_size = 128 M
68
Current query_cache_used = 3 M
69
Current query_cache_limit = 1 M
70
Current Query cache Memory fill ratio = 3.04 %
71
Current query_cache_min_res_unit = 4 K
72
Your query_cache_size seems to be too high.
73
Perhaps you can use these resources elsewhere
74
MySQL won't cache query results that are larger than query_cache_limit in size
75
76
SORT OPERATIONS
77
Current sort_buffer_size = 512 K
78
Current read_rnd_buffer_size = 508 K
79
Sort buffer seems to be fine
80
81
JOINS
82
Current join_buffer_size = 132.00 K
83
You have had 0 queries where a join could not use an index properly
84
Your joins seem to be using indexes properly
85
86
OPEN FILES LIMIT
87
Current open_files_limit = 1024 files
88
The open_files_limit should typically be set to at least 2x-3x
89
that of table_cache if you have heavy MyISAM usage.
90
Your open_files_limit value seems to be fine
91
92
TABLE CACHE
93
You are not '1002' or 'root'
94
I am unable to determine the table_count!
95
Current table_cache value = 256 tables
96
You have 195 open tables.
97
The table_cache value seems to be fine
98
99
TEMP TABLES
100
Current max_heap_table_size = 511 M
101
Current tmp_table_size = 512 M
102
Of 50 temp tables, 49% were created on disk
103
Effective in-memory tmp_table_size is limited to max_heap_table_size.
104
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
105
to reduce the number of disk-based temporary tables
106
Note! BLOB and TEXT columns are not allow in memory tables.
107
If you are using these columns raising these values might not impact your
108
ratio of on disk temp tables.
109
110
TABLE SCANS
111
Current read_buffer_size = 252 K
112
Current table scan ratio = 129 : 1
113
read_buffer_size seems to be fine
114
115
TABLE LOCKING
116
Current Lock Wait ratio = 0 : 1790
117
Your table locking seems to be fine
118
119
 >>  MySQLTuner 1.6.12 - Major Hayden <major@mhtx.net>
120
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
121
 >>  Run with '--help' for additional options and output filtering
122
[--] Performing tests on 127.0.0.1:3047
123
[!!] Successfully authenticated with no password - SECURITY RISK!
124
125
[--] Skipped version check for MySQLTuner script
126
[!!] failed to execute: SHOW GLOBAL STATUS
127
[!!] FAIL Execute SQL / return code: 256
128
[!!] failed to execute: SHOW SLAVE STATUS\G
129
[!!] FAIL Execute SQL / return code: 256
130
[!!] failed to execute: SHOW SLAVE HOSTS
131
[!!] FAIL Execute SQL / return code: 256
132
[!!] Your MySQL version 4.1.22mysql4-log is EOL software!  Upgrade soon!
133
[OK] Operating on 32-bit architecture with less than 2GB RAM
134
135
-------- Storage Engine Statistics -----------------------------------------------------------------
136
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
137
[!!] InnoDB is enabled but isn't being used
138
[OK] Total fragmented tables: 0
139
140
-------- Security Recommendations ------------------------------------------------------------------
141
[!!] failed to execute: SELECT CONCAT(user, '@', host) FROM mysql.user WHERE TRIM(USER) = '' OR USER IS NULL
142
[!!] FAIL Execute SQL / return code: 256
143
[OK] There are no anonymous accounts for any database users
144
[!!] No more password checks for MySQL version <=5.1
145
[!!] MySQL version <=5.1 are deprecated and end of support.
146
147
-------- CVE Security Recommendations --------------------------------------------------------------
148
[--] Skipped due to --cvefile option undefined
149
Argument "du:" isn't numeric in addition (+) at /usr/local/sbin/mysqltuner.pl
150
        line 2103 (#1)
151
    (W numeric) The indicated string was fed as an argument to an operator
152
    that expected a numeric value instead.  If you're fortunate the message
153
    will identify which operator was so unfortunate.
154
155
156
-------- Performance Metrics -----------------------------------------------------------------------
157
[--] Up for: 6m 4s (1K q [5.058 qps], 123 conn, TX: 8M, RX: 153K)
158
[--] Reads / Writes: 97% / 3%
159
[--] Binary logging is enabled (GTID MODE: OFF)
160
[--] Physical Memory     : 925.6M
161
[--] Max MySQL memory    : 678.4M
162
[--] Other process memory: 61.2M
163
[--] Total buffers: 666.0M global + 1.6M per thread (8 max threads)
164
[--] P_S Max memory usage: 0B
165
[--] Galera GCache Max memory usage: 0B
166
[OK] Maximum reached memory usage: 669.1M (72.29% of installed RAM)
167
[OK] Maximum possible memory usage: 678.4M (73.30% of installed RAM)
168
[OK] Overall possible memory usage with other process is compatible with memory available
169
[OK] Slow queries: 0% (0/1K)
170
[OK] Highest usage of available connections: 25% (2/8)
171
[OK] Aborted connections: 0.00%  (0/123)
172
[OK] Query cache efficiency: 26.9% (214 cached / 797 selects)
173
[OK] Query cache prunes per day: 0
174
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 84 sorts)
175
[OK] No joins without indexes
176
[!!] Temporary tables created on disk: 98% (49 on disk / 50 total)
177
[OK] Table cache hit rate: 97% (195 open / 201 opened)
178
[OK] Open file limit used: 37% (386/1K)
179
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
180
[OK] Binlog cache memory access: 0% ( 0 Memory / 0 Total)
181
182
-------- ThreadPool Metrics ------------------------------------------------------------------------
183
[--] ThreadPool stat is disabled.
184
185
-------- Performance schema ------------------------------------------------------------------------
186
[--] Performance schema is disabled.
187
188
-------- MyISAM Metrics ----------------------------------------------------------------------------
189
[!!] Key buffer used: 41.8% (7M used / 16M cache)
190
[!!] Cannot calculate MyISAM index size - re-run script as root user
191
192
-------- AriaDB Metrics ----------------------------------------------------------------------------
193
[--] AriaDB is disabled.
194
195
-------- InnoDB Metrics ----------------------------------------------------------------------------
196
[--] InnoDB is disabled.
197
198
-------- TokuDB Metrics ----------------------------------------------------------------------------
199
[--] TokuDB is disabled.
200
201
-------- Galera Metrics ----------------------------------------------------------------------------
202
[--] Galera is disabled.
203
204
-------- Replication Metrics -----------------------------------------------------------------------
205
[--] Galera Synchronous replication: NO
206
[--] No replication slave(s) for this server.
207
[--] This is a standalone server.
208
209
-------- Recommendations ---------------------------------------------------------------------------
210
General recommendations:
211
    Add skip-innodb to MySQL configuration to disable InnoDB
212
    MySQL started within last 24 hours - recommendations may be inaccurate
213
    Enable the slow query log to troubleshoot bad queries
214
    Temporary table size is already large - reduce result set size
215
    Reduce your SELECT DISTINCT queries without LIMIT clauses
216
    Upgrade to MySQL 5.5+ to use asynchronous write


Und hier meine My.cfg:

1
# The following options will be passed to all MySQL clients
2
[client]
3
#password       = your_password
4
port            = 3047
5
socket          = /usr/local/mysql4/var/mysqld.sock
6
7
# Here follows entries for some specific programs
8
9
# The MySQL server
10
[mysqld]
11
port                            = 3047
12
socket                          = /usr/local/mysql4/var/mysqld.sock
13
skip-locking
14
key_buffer                      = 16M
15
max_allowed_packet              = 1M
16
table_cache =                   256
17
sort_buffer_size                = 512K
18
net_buffer_length               = 8K
19
read_buffer_size                = 256K
20
read_rnd_buffer_size            = 512K
21
myisam_sort_buffer_size         = 8M
22
tmp-table-size                  = 512M
23
max-heap-table-size             = 512M
24
query-cache-type                = 1
25
query-cache-size                = 128M
26
max-connections                 = 8
27
28
29
# Replication Master Server (default)
30
# binary logging is required for replication
31
log-bin
32
expire-logs-days               = 14
33
sync-binlog                    = 1
34
35
# required unique id between 1 and 2^32 - 1
36
# defaults to 1 if master-host is not set
37
# but will not function as a master if omitted
38
server-id       = 1
39
40
[mysqldump]
41
quick
42
max_allowed_packet = 16M
43
44
[mysql]
45
no-auto-rehash
46
# Remove the next comment character if you are not familiar with SQL
47
#safe-updates
48
49
[isamchk]
50
key_buffer = 20M
51
sort_buffer_size = 20M
52
read_buffer = 2M
53
write_buffer = 2M
54
55
[myisamchk]
56
key_buffer = 20M
57
sort_buffer_size = 20M
58
read_buffer = 2M
59
write_buffer = 2M
60
61
[mysqlhotcopy]
62
interactive-timeout


Auf dem Raspy 3 läuft nur der MySQL4 Server für ein Buchaltungsprogramm.
Sonst nix.
Es kann also der Speicher so gut wie möglich ausgenutzt werden.

Was kann ich da noch optimieren?

von Peter II (Gast)


Lesenswert?

qler schrieb:
> Was kann ich da noch optimieren?

Ich würde bei der Indizierung anfangen. Nimm dir eine Anfrage die lange 
dauert und schau welche Spalten und Tabellen beteiligt sind.

(Es stellt sich mir aber die Frage, warum man nun den langsamsten PC für 
so etwas verwenden muss, für die Zeit die du jetzt investieren muss, 
könnte man auch einen etwas schnelleren PC hinstellen. Wo auch die 
Datenspeicher sinnvoll angebunden ist)

von qler (Gast)


Lesenswert?

Weil der Rapsy 3 eben nur um die 5 Watt braucht.

Meine grösste Frage ist eben die folgige:
Wie behebe ich folgende Meldung:
1
[!!] Temporary tables created on disk: 98% (49 on disk / 50 total)

von S R (Gast)


Lesenswert?

MAX_HEAP_TABLE_SIZE und TMP_TABLE_SIZE erhöhen.

Nutze auch mal tuning-primer.sh, das ist etwas detaillierter.

Besser wäre es den Grund für die temporären Tabellen zu beseitigen 
(sinnvoll Indizes vergeben...)

von Peter II (Gast)


Lesenswert?

qler schrieb:
> Weil der Rapsy 3 eben nur um die 5 Watt braucht.

und?

http://www.shuttle.eu/de/produkte/nano/nc01u/spezifikation/

Verlustleistung: Leerlauf: 5,5 W, Volllast: 11,6 / 22,4 W (ohne/mit 
Grafik)


die Raspis sind zwar schön und gut, aber sie sind langsam!. Netzwerk und 
Festplatte hängen an einem gemeinsamen USB 2.0.

Ich mache auch einiges damit, aber wenn man eine Datenbank betreibt wo 
du vermutlich nicht mal an den Datenstrukturen etwas ändern kannst sind 
sie nicht ideal.

von S R (Gast)


Lesenswert?

tmp-table-size                  = 512M
max-heap-table-size             = 512M


das ist doch quatsch. Schau mal, wie die Bezeichner wirklich heißen.

von S R (Gast)


Lesenswert?

qler schrieb:
> MySQL Version 4.1.22mysql4-log armv7l is EOL please upgrade to MySQL 4.1
> or later

Das 4.1 scheint mir auch ein wenig alt?

von Axel S. (a-za-z0-9)


Lesenswert?

qler schrieb:
> Mcöhte die MySQL DB auf meinem Raspy 3 optimieren.

Warum? Ist sie (zu) langsam oder was?

> Leider komme ich nicht mit der Meldung unter den Temp_Tables zurecht.

Ja. Das passiert, wenn man Werkzeuge zu verwenden versucht, die man 
nicht verstanden hat. Freundlicherweise hat Matthew ja noch einen 
möglichen Grund hingeschrieben, warum eine Temptable materialisiert 
wird, obwohl ausreichend RAM dafür konfiguriert wurde. Bist du dem 
eigentlich mal nachgegangen?

Außerdem: es wurden in 5 Minuten Uptime 50 Temptables angelegt. Also 
eine alle 6 Sekunden. Glaubst du ernsthaft, daß das deinen Raspi 
überlastet?

> Was kann ich da noch optimieren?

Falsche Frage.

Die richtige Frage ist: Sollte ich überhaupt etwas optimieren? Und die 
Antort ist in der Regel: Nein. Falls es eine andere Antwort gibt, dann 
kannst du die sicher begründen. Und dann können wir weiter sehen.

von Karli (Gast)


Lesenswert?

Hallo zusammen,

Habe das selbe Problem, das zuviele Temp Tables angelegt werden (um die 
1300). Auf einem Raspi 3. Es wird auch CAO benutzt.
Gibt es da schon eine My.cnf, welche vernüftig optimiert ist (bezüglich 
den Temptbles) ??????

von Karli (Gast)


Lesenswert?

Das ist meine Config:

[mysqld]
port                            = 3007
socket                          = /usr/local/mysql4/var/mysqld.sock
skip-locking
key_buffer                      = 128M
max_allowed_packet              = 1M
table_cache                     = 256
sort_buffer_size                = 4M
read_buffer_size                = 4M
join_buffer_size                = 2M
net_buffer_length               = 8K
read_rnd_buffer_size            = 4M
myisam_sort_buffer_size         = 64M
tmp_table_size                  = 512M
max_heap_table_size             = 512M
query_cache_type                = 1
query_cache_size                = 128M
query_cache_limit               = 128M
thread_concurrency              = 8
max_connections                 = 4

Bitte melde dich an um einen Beitrag zu schreiben. Anmeldung ist kostenlos und dauert nur eine Minute.
Bestehender Account
Schon ein Account bei Google/GoogleMail? Keine Anmeldung erforderlich!
Mit Google-Account einloggen
Noch kein Account? Hier anmelden.