MySQL an der Grenze ?

[coolsurfer]

Hallo,

wir haben hier eine MySQL-Datenbank mit 110 Tabellen - 11 Mio Zeilen (ca. 3,6 GB) laufen. In letzter Zeit kommt es immer häufiger vor, dass die DB für einen einfachen Join-Select über max. 6 Tables über 30 Minuten (!) benötigt.

> 3,0 GHz SMP, RAM = 2 GB; mehr geht leider nicht rein :-)

Ist MySQL damit am Ende ? Welche andere DB-Lösung würdet Ihr empfehlen ?

coolsurfer

[outofbound]

Hi,

mysqladmin ver und var machen.

nen top noch dazu, dann kann man mehr sagen

Indizes richtig gesetzt und laufen auch?
my.cnf?

Gruss,

Out

[coolsurfer]

Hi,

1. top:

Code: Select all

  8:01pm  up 49 days,  1:02,  1 user,  load average: 0.62, 0.48, 0.38
81 processes: 80 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states:  0.1% user, 15.0% system,  0.0% nice, 83.1% idle
CPU1 states:  3.0% user,  0.0% system,  0.0% nice, 96.0% idle
Mem:  2061924K av, 2002860K used,   59064K free,       0K shrd,   27192K buff
Swap:  265064K av,   61284K used,  203780K free                 1732496K cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
18855 root      18   0   900  900   688 R    15.2  0.0   0:00 top
18820 mysql     11   0  173M 163M 41496 S     3.3  8.1   0:01 mysqld-max
18732 mysql      9   0  173M 163M 41496 S     0.8  8.1   0:02 mysqld-max
18826 mysql      9   0  173M 163M 41496 S     0.8  8.1   0:00 mysqld-max
    1 root       8   0    84   76    52 S     0.0  0.0   0:50 init
    2 root       9   0     0    0     0 SW    0.0  0.0   0:00 keventd
    3 root      19  19     0    0     0 SWN   0.0  0.0   0:06 ksoftirqd_CPU0
    4 root      19  19     0    0     0 SWN   0.0  0.0   0:07 ksoftirqd_CPU1
    5 root       9   0     0    0     0 SW    0.0  0.0   3:30 kswapd
    6 root       9   0     0    0     0 SW    0.0  0.0   0:00 bdflush
    7 root       9   0     0    0     0 SW    0.0  0.0   3:02 kupdated
    8 root       9   0     0    0     0 SW    0.0  0.0   0:00 scsi_eh_0
    9 root       9   0     0    0     0 SW    0.0  0.0  23:30 kjournald
   56 root       9   0     0    0     0 SW    0.0  0.0   0:00 kjournald
  376 root       9   0   524  508   508 S     0.0  0.0   0:00 dhcpcd
  392 root       9   0   604  580   512 S     0.0  0.0   0:23 syslogd
  395 root       9   0  1292  584   412 S     0.0  0.0   0:07 klogd
  411 root       9   0  1388 1216  1140 S     0.0  0.0   1:26 sshd
  413 root       9   0  4536 1572  1240 S     0.0  0.0   1:17 miniserv.pl
  484 root       9   0   848  780   700 S     0.0  0.0   0:08 xinetd
  501 ntp        9   0  1932 1932  1704 S     0.0  0.0   2:34 ntpd
  555 root       8   0  1432 1176  1116 S     0.0  0.0   0:40 master
  574 postfix    9   0  1592 1252  1172 S     0.0  0.0   0:46 qmgr
  611 at         9   0   540  496   460 S     0.0  0.0   0:00 atd
  626 root       8   0   620  560   512 S     0.0  0.0   0:09 cron
  642 root       9   0   692  664   564 S     0.0  0.0   0:05 nscd
  643 root       9   0   692  664   564 S     0.0  0.0   0:37 nscd
  644 root       9   0   692  664   564 S     0.0  0.0   0:05 nscd
  645 root       9   0   692  664   564 S     0.0  0.0   0:02 nscd
  646 root       9   0   692  664   564 S     0.0  0.0   0:02 nscd
  647 root       9   0   692  664   564 S     0.0  0.0   0:02 nscd
  648 root       9   0   692  664   564 S     0.0  0.0   0:02 nscd
  659 root       9   0   740  576   488 S     0.0  0.0   0:00 3dmd
  660 root       9   0   740  576   488 S     0.0  0.0   0:35 3dmd
  661 root       9   0   740  576   488 S     0.0  0.0   0:24 3dmd
  662 root       9   0   740  576   488 S     0.0  0.0 776:20 3dmd
  663 root       9   0   740  576   488 S     0.0  0.0   0:00 3dmd
  668 root       9   0   456  392   392 S     0.0  0.0   0:00 mingetty
  669 root       9   0   456  392   392 S     0.0  0.0   0:00 mingetty
  670 root       9   0   456  392   392 S     0.0  0.0   0:00 mingetty
  671 root       9   0   456  392   392 S     0.0  0.0   0:00 mingetty
  672 root       9   0   456  392   392 S     0.0  0.0   0:00 mingetty
  673 root       9   0   456  392   392 S     0.0  0.0   0:00 mingetty
  674 root       9   0   444  380   380 S     0.0  0.0   0:00 agetty
28545 root       9   0  1800 1604  1452 S     0.0  0.0   0:00 stunnel
28556 root       9   0  1800 1604  1452 S     0.0  0.0   0:22 stunnel
32455 root       9   0  1152  924   924 S     0.0  0.0   0:00 safe_mysqld
32486 mysql      9   0  173M 163M 41496 S     0.0  8.1  18:30 mysqld-max
32488 mysql      8   0  173M 163M 41496 S     0.0  8.1   0:51 mysqld-max
32489 mysql      9   0  173M 163M 41496 S     0.0  8.1  12:15 mysqld-max
32490 mysql      9   0  173M 163M 41496 S     0.0  8.1   0:00 mysqld-max
25174 root       9   0  3668 1660  1644 S     0.0  0.0   0:46 httpd
30189 wwwrun     9   0  4512 2940  2564 S     0.0  0.1   0:12 httpd
30190 wwwrun     9   0  4744 3084  2728 S     0.0  0.1   0:10 httpd
30191 wwwrun     9   0  4544 2876  2692 S     0.0  0.1   0:11 httpd

2. my.cnf:

Code: Select all

[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock

server-id       = 10000

set-variable    = key_buffer=128M
set-variable    = max_allowed_packet=1M
set-variable    = table_cache=512
set-variable    = sort_buffer=1M
set-variable    = record_buffer=1M
set-variable    = net_buffer_length=8K
set-variable    = myisam_sort_buffer_size=64M
set-variable    = max_binlog_size=78643200
set-variable    = max_connect_errors=750
set-variable    = thread_concurrency=8
set-variable    = max_connections=250
set-variable    = long_query_time=10
set-variable    = thread_cache=8

skip-innodb
skip-locking
log-long-format
binlog-ignore-db=tmp

3. mysqladmin ver:

Code: Select all

Server version          3.23.57-Max-log
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 22 days 6 hours 59 min 23 sec

Threads: 6  Questions: 110032103  Slow queries: 26263  Opens: 282  Flush tables: 1  Open tables: 512 Queries per second avg: 57.131

4. mysqladmin var (in lesbare Form gebracht):

Code: Select all

back_log  =>  50
basedir  =>  /
bdb_cache_size  =>  8388600
bdb_log_buffer_size  =>  262144
bdb_home  =>  /var/lib/mysql/
bdb_max_lock  =>  10000
bdb_logdir  => 
bdb_shared_data  =>  OFF
bdb_tmpdir  =>  /tmp/
bdb_version  =>  Sleepycat Software: Berkeley DB 3.2.9a: (June  6, 2003)
binlog_cache_size  =>  32768
character_set  =>  latin1
character_sets  =>  latin1 big5 czech euc_kr gb2312 gbk sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5
concurrent_insert  =>  ON
connect_timeout  =>  5
datadir  =>  /var/lib/mysql/
delay_key_write  =>  ON
delayed_insert_limit  =>  100
delayed_insert_timeout  =>  300
delayed_queue_size  =>  1000
flush  =>  OFF
flush_time  =>  0
have_bdb  =>  YES
have_gemini  =>  NO
have_innodb  =>  DISABLED
have_isam  =>  YES
have_raid  =>  NO
have_openssl  =>  NO
init_file  => 
innodb_additional_mem_pool_size  =>  1048576
innodb_buffer_pool_size  =>  8388608
innodb_data_file_path  => 
innodb_data_home_dir  => 
innodb_file_io_threads  =>  4
innodb_force_recovery  =>  0
innodb_thread_concurrency  =>  8
innodb_flush_log_at_trx_commit  =>  1
innodb_fast_shutdown  =>  ON
innodb_flush_method  => 
innodb_lock_wait_timeout  =>  50
innodb_log_arch_dir  => 
innodb_log_archive  =>  OFF
innodb_log_buffer_size  =>  1048576
innodb_log_file_size  =>  5242880
innodb_log_files_in_group  =>  2
innodb_log_group_home_dir  => 
innodb_mirrored_log_groups  =>  1
interactive_timeout  =>  28800
join_buffer_size  =>  131072
key_buffer_size  =>  134213632
language  =>  /usr/share/mysql/english/
large_files_support  =>  ON
locked_in_memory  =>  OFF
log  =>  OFF
log_update  =>  OFF
log_bin  =>  ON
log_slave_updates  =>  OFF
log_long_queries  =>  OFF
long_query_time  =>  10
low_priority_updates  =>  OFF
lower_case_table_names  =>  0
max_allowed_packet  =>  1047552
max_binlog_cache_size  =>  4294967295
max_binlog_size  =>  78643200
max_connections  =>  250
max_connect_errors  =>  750
max_delayed_threads  =>  20
max_heap_table_size  =>  16777216
max_join_size  =>  4294967295
max_sort_length  =>  1024
max_user_connections  =>  0
max_tmp_tables  =>  32
max_write_lock_count  =>  4294967295
myisam_max_extra_sort_file_size  =>  256
myisam_max_sort_file_size  =>  2047
myisam_recover_options  =>  0
myisam_sort_buffer_size  =>  67108864
net_buffer_length  =>  7168
net_read_timeout  =>  30
net_retry_count  =>  10
net_write_timeout  =>  60
open_files_limit  =>  0
pid_file  =>  /var/lib/mysql/mysqld.pid
port  =>  3306
protocol_version  =>  10
record_buffer  =>  1044480
record_rnd_buffer  =>  1044480
query_buffer_size  =>  0
safe_show_database  =>  OFF
server_id  =>  10000
slave_net_timeout  =>  3600
skip_locking  =>  ON
skip_networking  =>  OFF
skip_show_database  =>  OFF
slow_launch_time  =>  2
socket  =>  /var/lib/mysql/mysql.sock
sort_buffer  =>  1048568
sql_mode  =>  0
table_cache  =>  512
table_type  =>  MYISAM
thread_cache_size  =>  8
thread_stack  =>  65536
transaction_isolation  =>  READ-COMMITTED
timezone  =>  CEST
tmp_table_size  =>  33554432
tmpdir  =>  /tmp/
version  =>  3.23.57-Max-log
wait_timeout  =>  28800

5. Indizies

Ja, die sind 100% richtig gesetzt; mehrfach geprüft und optimiert.


thx

coolsurfer

[outofbound]

Hmmm... sieht eigentlich gar nicht so wild aus... der RAM ist zwar voll,

was für ein Querry isses dann?

werden da Tablescans gemacht?

Was für Slow querries werden da gemacht? (/var/log/irgendwas ;) )

Gruss,

Out

[alexander newald]

Kommen sich evtl. locks in die quere?

was sagt mysql> status?

[kase]

Ich höre immer wieder von vielen Leuten, die Websites mit viel php/mysql betreiben, und sehr viele Site Impressions haben, dass mysql vor allem bei großen Tabellen extrem zusammenbricht.

Die Komponente, die das System ausbremst, ist immer der mysql Server, in 99,9 % der Fälle.

Viele sind inzwischen auf pgSQL umgestiegen, und meinen, es wäre weit performanter. Die Syntax ist soviel ich weiß, extrem ähnlich, wer mit DB-Klassen programmiert, brauch vermutlich gar nicht viel ändern, als die Klasse auszutauschen.

Allerdings habe ich selbst noch keinerlei Erfahrungen mit pgSQL. Aber werde vermutlich die nächsten Tage mal die ersten Schritte damit versuchen. Debian hat für pgSQL fast alles in Paketen. Hier sollte die Installation änlich einfach sein, wie bei mysql. Allerdings ist mir BISHER noch nicht bewusst, wie man mySQL Daten zu pgSQL umwandeln kann.

[kase]

Ã?brigens, von deinen 2 GB Ram gehen über 1,7 GB nur fürs Cachen von Files drauf, für Anwendungen sollte also auf jeden Fall noch genug frei sein, da normalerweise Linux dafür einfach etwas Cached Memory abzweigt.

[oxygen]

Hm. Also ich würde mal die MySQL 4 probieren und den Query Cache ordentlich aufdrehen. Denn das ist wirklich nicht schön: Queries per second avg: 57.131 Das bringt zumindest etwas wenn immer wieder gleiche Querys auftreten.

[majortermi]

Hm. Also ich würde mal die MySQL 4 probieren und den Query Cache ordentlich aufdrehen. Denn das ist wirklich nicht schön: Queries per second avg: 57.131 Das bringt zumindest etwas wenn immer wieder gleiche Querys auftreten.

57 Queries pro Sekunden sollte der Server eigentlich problemlos wegstecken.

[oxygen]

Eben, so meinte ich das. Da schafft mein Root L auch.

[kase]

Er hat ja auch noch genügend Systemressourcen frei. Es ist immer das gleiche mit mySQL 3, wie sich da 4 verhält, hab ich bisher noch nicht getestet. Gebt mysql 3 ein paar sehr große Tables und eine gewisse Anzahl Querys pro Sekunde, und alles wird zur Schnecke, OBWOHL die entsprechenden Ressourcen noch frei sind. Ich versichere dir, dass du kein Einzelfall bist.

Bei coolsurfer ist das Problem nicht die Anzahl der Querys pro Sekunde, sondern die enorm große und umfangreiche Datenbank, damit ist die 3er Version einfach maßlos überfordert. IMHO gibt es keine andere Lösung als mySQL 4 zu testen oder auf eine andere Datenbank umzusteigen.

Edit: Damit will ich betonen, dass ein weiteres Aufrüsten des RAMs und der CPUs keinen wirklichen Vorteil bringt, dort würdest du am falschen Ende investieren.

[karatekid]

Es kommt definitiv nicht auf die Größe der Datenbank in MB an, auch nicht auf die Anzahl der Tabellen. Das was ausbremst sind die Anzahl der Tupel, sprich Datensätze.
Geht mir ähnlich, eine Tabelle ist vom Speicherverbrauch gar nicht so groß (<150MB), allerdings hat sie knapp 7 Millionen Datensätze. Das bremst!

[kase]

Jo, das stimmt 100 %. Nur eine große Datenbank hat meistens auch Tabellen mit vielen Datensätzen. Und für mySQL 3 gibts halt nicht schlimmeres *ggg*

[gamecrash]

Ich denke, Du könntest es wirklich mal mit PostgreSQL probieren. Ich kenne Berichte, nach denen es wesentlich schneller war, aber auch Berichte, in denen es wesentlich langsamer war. Hängt sehr von der Art der Queries etc. ab...

Mach einfach mal nen Dump der Datenbank und wandel das in einen PostgreSQL-Dump um (da gibt's zwei Perl-Programme für im Contrib). Und wenn Du das Ganze einfach mal testweise laufen lässt bist Du schlauer :)

[kase]

Vielleicht solltest du auch mal mysql 4 probiern. Ist denke ich mal nicht besonders schwer umzustellen, und bringt vielleicht einen enormen Geschwindigkeitsschub. im IRC meinte Sica, dass mysql 4 locker Tabellen mit 8 Mio Datensätzen schafft, da würde mysql 3 vermutlich nicht mehr viel machen ^^

[gamecrash]

Ich weiß ja, dass MySQL 4 performanter sein soll... aber ich würd an Deiner Stelle nicht drauf umstellen... der Table-Corruption-Bug in den letzten zwei Versionen ist nicht so der Hit...

[alexander newald]

Gibts da ne Seite zu, habe ich überlesen...

[gamecrash]

Wozu?

[captaincrunch]

Vermutlich zum Table-Corruption-Bug ?!? ;)

[oxygen]

Der Bug ist doch in 4.0.14 schon korrigiert.

[alexander newald]

Genau das wollte ich wissen.... :-D

Weil: Server version: 4.0.14

[gamecrash]

Ja? Ist wohl irgendwie an mir vorüber gegangen :roll:

[oxygen]

Zumindest steht im Changelog

Bugfixes:
Comparison/sorting for latin1_de character set was rewritten. The old algorithm could not handle cases like "sä" > "Ã?a". See section 4.6.1.1 German character set. In rare cases it resulted in table corruption.

[gamecrash]

Der Bug hat doch nix mit deutsch zu tun, mit dem kämpfen viele Amis auf vBulletin.com ;)

Und laut Bug #563 ist das Problem auch noch nicht gefixt bzw. nur teilweise...

[oxygen]

Achso dann is ja klar wo drans liegt.
Auf welcher Software hosten Amis?
Genau: RedHat.