Hello first of here is my SHOW STATUS
±----------------------------------±----------+
The problem we are having and I am new to MySQL is that we use mysql as a backend to our alerts kinda like syslog repository. Everytime there is an alert the data gets inserted into the DB. Alerts are being generated from about 40 servers. Nightly we run a script that goes through and removes (DELETE) deletes data that is older than 7 days(which =~100k-200k alerts per day). So at any giving day the last event should be <7 days from current date.
During the DELETE procedure that occurs nightly we get large number of dropped alerts because (not sure) the tables that it needs to write to are locked and also being accessed by the script.
What would be the best way to optimize our SQL server for fast INSERTS, DELETES, UPDATES which is mostly what we do. It appears as though INSERTS during this DELETING period are somehow dropped not allowed if we sum up the total that should have been INSERTED with the actual number of successful INSERTS its about about 2 our of 10.
Any help ideas suggestions? As I am all out of ideas.
Peter
February 23, 2007, 1:50pm
2
If you’re using MyISAM I’d use one table per week and use merge table for querying. This way you can simply drop old weekly table instead of running expensive delete.
The problem is tha we have about 40 tables and each server corresponds to a server ID which gets created depending on which one reports first. In other words that wouldn’t even be an option due to the large amount of configuration changes that would have to be done on the server that are reporting to the MySQL server. Are there any changes that can be done to improve INSERT, DELETE, UPDATE that are being done?
Thanks for the quick reply!!
Alexey
February 25, 2007, 9:51am
4
Please post output of SHOW GLOBAL VARIABLES, not just SHOW VARIABLES (it’s behaviour was changed in version 5, SHOW VARIABLES omits some data).
//edited
Alexey
February 25, 2007, 9:53am
5
[B]Quote:[/B]
| Key_blocks_unused | 0 || Key_blocks_used | 14497 || Key_read_requests | 94036894 || Key_reads | 4019574 |
Uh oh, do you have my.cnf file at all?
Humm I am sure I do, is there something that I might be doing wrong? here is a copy of it, it’s located in /etc/my.cnf
When I do a ps -ef |grep mysql here is the output
**root 1555 1 0 Feb23 ? 00:00:00 /bin/sh /usr/bin/mysqld_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid --log-error=/var/log/mysqld.log
**mysql 1592 1555 7 Feb23 ? 03:33:35 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/lib/mysql/mysql.sock
MY.cnf
[mysqld]
old_passwords=1
[mysql.server]
[mysqld]
#[mysqld_safe]
HERE IS SHOW GLOBAL VARIABLES
mysql> show global variables
Alexey
February 25, 2007, 3:52pm
7
[B]mysqluser81 wrote on Fri, 23 February 2007 13:51[/B]
Any help ideas suggestions? As I am all out of ideas.
After you sort out your configuration problems (32MB key buffer for a 8GB machine is too small, I’d start with at least 512MB) you can use “limit” trick. You don’t have to delete all rows with a single query, you can do something like (pseudocode):
do {
Peter
March 6, 2007, 11:13am
8
You still did not mention error message which it terminates with.
There is some tcpdump like tool for MySQL - I do not remember the name but you can check it on forge.mysql.com
also check out MySQL error logs if there is anything in them which could shed some light.
Here is what error show up in the messages file.
database: mysql_error: Lost connection to MySQL server during query
