ich stehe vor einem wahnsinnigem Problem, welches ich seit fast 2 Wochen nicht in den Griff bekomme. Ein PHP-Script führt in meiner Datenbank einige Abfragen aus. Diese Abfragen werden im Durschnitt ca. mit 0,30 u. 0,02 Sekunden beantworet. Die Datenbank-Tabelle hat keinen INDEX oder auch keinen Primarey-Key!
Hier das PHP-Script:
Code: Select all
<?php
$dbconnect = mysql_connect("1", "2", "3");
mysql_select_db("4", $dbconnect);
$treffer = false;
$besetzt = array();
$check = mysql_query("SELECT ad_id FROM adserver_block WHERE ip = '".$_SERVER['REMOTE_ADDR']."'");
while($c = mysql_fetch_object($check))
$besetzt[] = $c->ad_id;
if(count($besetzt))
{
foreach($besetzt as $b)
{
$not_query .= (!$cd?"WHERE":"AND")." ad_id != '".$b."' ";
$cd ++;
}
}
// Anzahl aller Kampagnen ermitteln
$zufall_query = mysql_query("SELECT ad_url, ad_id FROM adserver_ads ".$not_query." ORDER BY rand() LIMIT 1");
$anzahl = mysql_num_rows($zufall_query);
if($anzahl)
{
// Einen zufälligen Einrag suchen
$zufall_query = mysql_query("SELECT ad_url, ad_id FROM adserver_ads ".$not_query." ORDER BY rand() LIMIT 1");
$zufall = mysql_fetch_object($zufall_query);
$ad_id = $zufall->ad_id;
$ad_url = $zufall->ad_url;
/// Eintrag in die Datenbank zum Blocken schreiben
$insert = mysql_query("INSERT INTO adserver_block (id, ip, timestamp, ad_id) VALUES ('".md5(uniqid(mt_rand(), true))."', '".$_SERVER['REMOTE_ADDR']."', '".time()."', '".$ad_id."')");
header("Location: ".$ad_url);
?>
Nun einige Infos über Server und eingesetzte Software:
Code: Select all
23m1015:~# apache2 -V
Server version: Apache/2.2.9 (Debian)
Server built: Dec 11 2010 21:35:43
Server's Module Magic Number: 20051115:15
Server loaded: APR 1.2.12, APR-Util 1.2.12
Compiled using: APR 1.2.12, APR-Util 1.2.12
Architecture: 64-bit
Server MPM: Worker
threaded: yes (fixed thread count)
forked: yes (variable process count)
Server compiled with....
-D APACHE_MPM_DIR="server/mpm/worker"
-D APR_HAS_SENDFILE
-D APR_HAS_MMAP
-D APR_HAVE_IPV6 (IPv4-mapped addresses enabled)
-D APR_USE_SYSVSEM_SERIALIZE
-D APR_USE_PTHREAD_SERIALIZE
-D SINGLE_LISTEN_UNSERIALIZED_ACCEPT
-D APR_HAS_OTHER_CHILD
-D AP_HAVE_RELIABLE_PIPED_LOGS
-D DYNAMIC_MODULE_LIMIT=128
-D HTTPD_ROOT=""
-D SUEXEC_BIN="/usr/lib/apache2/suexec"
-D DEFAULT_PIDLOG="/var/run/apache2.pid"
-D DEFAULT_SCOREBOARD="logs/apache_runtime_status"
-D DEFAULT_ERRORLOG="logs/error_log"
-D AP_TYPES_CONFIG_FILE="/etc/apache2/mime.types"
-D SERVER_CONFIG_FILE="/etc/apache2/apache2.conf"Code: Select all
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 64M
max_allowed_packet = 16M
thread_stack = 126K
myisam-recover = BACKUP
max_connections = 500
table_cache = 1024
thread_cache_size = 100
sort_buffer_size = 256K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
join_buffer_size = 1M
query_cache_limit = 8M
query_cache_size = 8M
expire_logs_days = 10
max_binlog_size = 100M
skip-bdb
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
!includedir /etc/mysql/conf.d/
Code: Select all
top - 17:24:35 up 23:46, 2 users, load average: 4.44, 5.66, 4.19
Tasks: 117 total, 1 running, 115 sleeping, 0 stopped, 1 zombie
Cpu(s): 39.1%us, 3.7%sy, 0.0%ni, 56.8%id, 0.0%wa, 0.1%hi, 0.3%si, 0.0%st
Mem: 8190452k total, 6472856k used, 1717596k free, 5336324k buffers
Swap: 5855684k total, 0k used, 5855684k free, 535948k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
11482 mysql 20 0 554m 120m 5804 S 160 1.5 154:52.50 mysqld
7481 www-data 20 0 115m 9428 5784 S 1 0.1 0:00.18 php5-cgi
7484 www-data 20 0 115m 9680 6020 S 1 0.1 0:00.20 php5-cgi
7854 www-data 20 0 115m 9424 5784 S 1 0.1 0:00.10 php5-cgi
7483 www-data 20 0 115m 9436 5784 S 1 0.1 0:00.14 php5-cgi
7485 www-data 20 0 115m 9392 5784 S 1 0.1 0:00.20 php5-cgi
7855 www-data 20 0 116m 9404 5784 S 1 0.1 0:00.08 php5-cgi
1 root 20 0 10316 752 624 S 0 0.0 0:02.64 init
Code: Select all
Server Version: Apache/2.2.9 (Debian)
Server Built: Dec 11 2010 21:35:43
Current Time: Wednesday, 24-Aug-2011 17:25:59 CEST
Restart Time: Wednesday, 24-Aug-2011 16:56:03 CEST
Parent Server Generation: 0
Server uptime: 29 minutes 55 seconds
Total accesses: 309492 - Total Traffic: 84.0 MB
CPU Usage: u3.51 s3.61 cu0 cs0 - .397% CPU load
172 requests/sec - 47.9 kB/second - 284 B/request
107 requests currently being processed, 153 idle workers
Hoffe Ihr habt da eine Idede.. Danke euch..
[edit ddm3ve: Bitte zukünftig keine Kommentare oder aufs nötigste Reduzieren. Die Mysql kommentare der konfig. kennen wir i.d.R. Bitte Code Tags verwenden]
