MySQL Datenbank Optimieren - Problem

MySQL, PostgreSQL, SQLite
RootN
Posts: 6
Joined: 2014-04-13 18:44

MySQL Datenbank Optimieren - Problem

Post by RootN » 2014-04-13 19:08

Hallo - ich habe Probleme beim Optimieren meiner Datenbank.
Vorerst die Datenbank beinhaltet mehrere Millionen Datensätze, jedoch nur 3 Tabellen.

Ich benutze auch kein Linux und hoffe daher das ich hier Trotzdem gut aufgehoben bin...
Zum Einsatz kommt AMPPS mit folgender Config:

Code: Select all

[client]
password   = mysql
port      = 3306
socket      = "{$path}/mysql/mysql.sock"


[mysqld]
port=3306

basedir="{$path}/mysql/"

datadir="{$path}/mysql/data/"

socket      = "{$path}/mysql/mysql.sock"
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 32M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
lower_case_table_names=2

binlog_format=mixed

server-id   = 1

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout


Ich hoffe ihr könnt mir beim Optimieren helfen... der Dienst stürzt oft einfach ab.

Fehler:

Code: Select all

InnoDB: Error: Table "mysql"."innodb_table_stats" not found.



Code: Select all

Zum System:
CPU:                  Intel Xeon E3-1230 @3.3GHz
Ram:                  32GB
Festplatte:        1TB WD-Blue
OS:                    Win7 64Bit


Mit freundlichen Grüßen
RootN

User avatar
Joe User
Project Manager
Project Manager
Posts: 11597
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL Datenbank Optimieren - Problem

Post by Joe User » 2014-04-13 19:20

Lösung für den Error: http://forums.mysql.com/read.php?22,578 ... msg-579891


Zum Tuning bitte den MySQLd, nachdem der Error beseitigt ist, erstmal 48 Stunden unter normaler Last laufen lassen und dann die Ausgaben von tuning-primer.sh und mysqltuner.pl posten.


BTW: Ich hoffe Du setzt das Win7 nicht als Serversystem ein, denn das wäre ein ganz klarer Lizenzverstoss und sehr teuer...
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings 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.

RootN
Posts: 6
Joined: 2014-04-13 18:44

Re: MySQL Datenbank Optimieren - Problem

Post by RootN » 2014-04-13 19:38

Danke ich werde es direkt mal lesen.

Ich benutze ihn nicht als Kommerziellen Server er dient mir lediglich für Private Zwecke. Daher sollte ich eig. gegen keine Lizenz verstoßen?!

User avatar
Joe User
Project Manager
Project Manager
Posts: 11597
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL Datenbank Optimieren - Problem

Post by Joe User » 2014-04-13 19:46

Er darf ausschliesslich nur in Deinem privaten LAN @Home erreichbar sein, dann ist Alles OK. Alles Andere ist ein Lizenzverstoss und Microsofts Rechtsabteilung sieht das überhaupt nicht gern. Kann schnell mehrere tausend Euro kosten (rückwirkende Nachzahlung der entsprechenden Windows Server Lizenz plus Schadenersatz plus Anwalts/Gerichtskosten).

So, das dürfte genug zu diesem Thema sein, wir wollen uns ja um den MySQLd kümmern.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings 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.

RootN
Posts: 6
Joined: 2014-04-13 18:44

Re: MySQL Datenbank Optimieren - Problem

Post by RootN » 2014-04-13 19:57

So ist es - es läuft auch nur so, dass niemand außer mir darauf Zugriff hat. Ich Melde mich dann zurück :)

RootN
Posts: 6
Joined: 2014-04-13 18:44

Re: MySQL Datenbank Optimieren - Problem

Post by RootN » 2014-04-20 20:09

Ich habe nun mal den MySQL Tuner drüber laufen lassen und bekomme folgendes Resulat:

Code: Select all

MySQL Tuner 0.7 - Peter Chapman <peter@conglomo.co.nz>
Currently running supported MySQL version 5.6.16
Operating on 64-bit architecture
Archive Engine Installed
Berkeley DB Engine Not Installed
Federated Engine Not Installed
InnoDB Engine Installed
ISAM Engine Not Installed
NDBCLUSTER Engine Not Installed
Data in InnoDB tables: 9M (Tables: 2)
Data in MyISAM tables: 863B (Tables: 14)
Total fragmented tables: 1
Up for: 44m 43s (29K q [10.000 qps], 156 conn, TX: 11M, RX: 9M)
Reads / Writes: 98% / 2%
Total buffers: 59.0M global + 1.7M per thread (151 max threads)
Maximum possible memory usage: 313.8M (0% of installed RAM)
Slow queries: 1% (4/29K)
Highest usage of available connections: 20% (29/151)
Key buffer size / total MyISAM indexes: 16.0M/139.0K
Key buffer hit rate: 97% (303 cached / 8 reads)
Query cache efficiency: 0% (0 cached / 414 selects)
Query cache prunes per day: 0
Sorts requiring temporary tables: 0% (0 temp sorts / 68 sorts)
Temporary tables created on disk: 12% (64 on disk / 559 total)
Thread cache hit rate: 81% (29 created / 156 connections)
Table cache hit rate: 64% (122 open / 188 opened)
Open file limit used: 1% (74/7K)
Table locks acquired immediately: 100% (9K immediate / 9K locks)
InnoDB data size / buffer pool: 9.5M/16.0M
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
query_cache_limit (> 1M, or use smaller result sets)
Scan Complete



Zudem muss ich sagen kommen viele "Bulk"-Inserts aka "LOAD DATA LOCAL INFILE..." in die DB welche nicht immer alle erfolgreich eingetragen werden :(
Last edited by RootN on 2014-04-20 20:16, edited 1 time in total.

User avatar
Joe User
Project Manager
Project Manager
Posts: 11597
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL Datenbank Optimieren - Problem

Post by Joe User » 2014-04-20 23:35

10.000 Queries pro Sekunde ist mal eine Hausnummer, was läuft denn da?


Bitte einen vollständigen mysqldump als Backup anlegen und dann mal mit dieser my.cnf versuchen:

Code: Select all

[client]
port                            = 3306
socket                          = "{$path}/mysql/mysql.sock"

[mysql]
no_auto_rehash

[mysqld]
port                            = 3306
socket                          = "{$path}/mysql/mysql.sock"
basedir                         = "{$path}/mysql/"
datadir                         = "{$path}/mysql/data/"
server-id                       = 1
back_log                        = 500
sync_binlog                     = 1
binlog_cache_size               = 4M
binlog_stmt_cache_size          = 4M
max_binlog_size                 = 500M
binlog-format                   = MIXED
expire_logs_days                = 7
lower_case_table_names          = 2
delay-key-write                 = ALL
myisam-recover-options          = FORCE,BACKUP
key_buffer_size                 = 256M
join_buffer_size                = 128K
sort_buffer_size                = 2M
read_buffer_size                = 128K
read_rnd_buffer_size            = 256K
bulk_insert_buffer_size         = 8M
myisam_sort_buffer_size         = 8M
max_allowed_packet              = 64M
max_heap_table_size             = 64M
tmp_table_size                  = 64M
table_open_cache                = 8192
table_definition_cache          = 8192
open_files_limit                = 32768
query_cache_type                = 1
query_cache_size                = 64M
query_cache_limit               = 2M
query_cache_min_res_unit        = 4K
thread_cache_size               = 80
max_connections                 = 100
long_query_time                 = 0.5
local-infile                    = 1
log-warnings                    = 2
skip-external-locking
innodb_thread_concurrency       = 8
innodb_buffer_pool_instances    = 4
innodb_data_home_dir            = "{$path}/mysql/data/"
innodb_log_group_home_dir       = "{$path}/mysql/data/"
innodb_log_buffer_size          = 16M
innodb_log_files_in_group       = 2
innodb_flush_log_at_trx_commit  = 2
innodb_max_dirty_pages_pct      = 90
innodb_file_per_table           = 1
innodb_purge_threads            = 1
innodb_strict_mode              = 1
innodb_old_blocks_time          = 1000
innodb_stats_on_metadata        = 0
innodb_write_io_threads         = 8
innodb_read_io_threads          = 8
innodb_io_capacity              = 200
innodb_open_files               = 8192

[mysqldump]
max_allowed_packet              = 256M
key_buffer_size                 = 256M
quote_names
quick

[isamchk]
key_buffer_size                 = 256M

[myisamchk]
key_buffer_size                 = 256M

[mysqlhotcopy]
interactive_timeout

WARNUNG: Ich habe nie mit MySQL unter Windows gearbeitet, daher können einzelne Optionen dieser my.cnf unter Umständen völlig falsch sein und die Situation verschlechtern, oder im schlimmsten Fall so gar zu Datenverlust führen.
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings 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.

RootN
Posts: 6
Joined: 2014-04-13 18:44

Re: MySQL Datenbank Optimieren - Problem

Post by RootN » 2014-04-24 14:45

Danke für die Config aber mit ihr bekomme ich die db gar nicht zum laufen :(

Die Datenbank ist eine zentrale "Datensammelstelle" für mein P-Netz.

User avatar
Joe User
Project Manager
Project Manager
Posts: 11597
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL Datenbank Optimieren - Problem

Post by Joe User » 2014-04-24 17:55

Was steht denn im mysql.err Log bezüglich meinem my.cnf-Vorschlag?
PayPal.Me/JoeUserFreeBSD Remote Installation
Wings for LifeWings 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.

RootN
Posts: 6
Joined: 2014-04-13 18:44

Re: MySQL Datenbank Optimieren - Problem

Post by RootN » 2014-05-10 15:06

Sorry das ich mich jetzt erst wieder melde - War mein Fehler die Config funktioniert =)

Ich hätte allerdings noch eine andere Frage wie handelt man Masseninserts?

Ich habe eine Tabelle mit Indizes und lasse dort parallel einfügen jedoch kommt es dort schnell zum Deadlock :(
Gedacht habe ich mir nun eine Zwischentabelle zu nehmen ohne Indizes um dort die Inserts vorzunehmen und von dort an wollte ich irgendwie in die Haupttabelle nur weiß ich nicht was ich dort für Möglichkeiten habe um dies zu realisieren bzw. ob MySQL für eine solche Angelegenheit bereits etwas bereitstellt?