System Suddenly Running Slow
I have been running mysql server 4.0.14 standard on a Dual Zeon 2.8GHz machine with 2GB RAM and linux redhat 9 for about two years, then all of a sudden it has started to run at a crawl.
The database contains 26 million rows but 19 million are in a table the is very rarely used but occasionally needs to be accessed reasonably quickly (but performance isn't an issue). Approx 5 million rows are in another table this is used daily. The next biggest table is 444,000 rows.
Is there a likely reason for the sudden slow down, can i do anything about it as it is operationally critical that i fix this quickly.
View Complete Forum Thread with Replies
Related Forum Messages:
MySQL Running Very Slow
I have the following INSERT SQL, which runs very slow (For 2 full days it had not done anything) on a 2 processor Compaq machine running Suse Linux. Code:
View Replies !
Slow Running MySQL?
I'm hoping someone may have an idea what could be causing the slow loading pages of my web-site. I'm not positive, but I'm relatively sure that it's related to a problem with MySQL database. I purchased a program called Sam3 for broadcasting our radio signal over the internet. The software requires the use of MySql in order to set up web pages for our web site. The problem is, that while everything works GREAT (requests can be made from the site, songs can be searched, the history of what we've played shows up) the pages load PAINFULLY slow. Other users of this software have had no such problems so I'm at a loss. I know from testing that the PHP script for the web pages is fine, I have plenty of bandwith both upload and download available on my DSL line, and while the computer running MySQL is behind a router I have tried plugging the DSL line DIRECTLY into the host computer and it makes no difference. For all appearances it seems the problem is that it takes MySQL forever to gather and provide the information needed for these pages. Does anyone have any idea what I should be looking at to solve this problem? I do not know the MySQL program at all, so I get somewhat lost when people start throwing out technical terms, but this has been really frustrating because no one else seems to have the problem and everyone so far has just told me to check the things I've already checked.
View Replies !
Mysql Running Slow On Mandrake Linux 10.0 For AMD 64
I'm having a problem with a new machine running Mysql version 4.0.18 on the AMD64 version of Mandrake 10.0. The new machine has got 64bit AMD processor and 2GB of RAM. Nearly all queries(updates are especially slow) are running 20/30 or more times slower than on an inferior(32 bit processor, 1.5GB RAM) machine runnning Mysql 3.23.56 on mandrake 9.0. As far as I can tell mysql is configured correctly for the power of the machine. The my.cnf file looks like this: Code:
View Replies !
Website Suddenly Freezes
mysql> show processlist; +------+--------------+-----------+--------------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +------+--------------+-----------+--------------+---------+------+-------+------------------+ | 1851 | root | localhost | admin_soccer | Query | 0 | NULL | show processlist | | 2538 | admin_soccer | localhost | admin_soccer | Sleep | 8 | | NULL | +------+--------------+-----------+--------------+---------+------+-------+------------------+ 2 rows in set (0.00 sec)
View Replies !
MySQL Suddenly Won't Start
Suddenly MySQL Server has stopped working and I am looking at a red traffic light. I have restarted my computer several times, I have tried to restart MySQL server many times I get Warning: mysql_connect(): Can't connect to MySQL server on 'localhost' (10061) in c:program filesapache groupapachehtdocszoom|conn.inc on line 6 Unable to select database
View Replies !
Process Stops Suddenly
We are running a Debian Etch 4.0 with Apache 2 and MySQL 5. All is OK and the website is going fine but sometimes the MySQL process stops suddenly and we need to make a force-reload or a start. I don't know what is going on but Apache 2 is still running.
View Replies !
Access Suddenly Denied
Yesterday MySQL was working perfectly. Having rebooted my machine today, None of my local sites or phpmyadmin can connect any longer, and phpmyadmin is giving me #1045 - Access denied for user 'root'@'localhost' (using password: YES). I would like to stress I have not installed anything new, I have not changed any config files anywhere, this is completely out of the blue. I have since reinstalled MySQL, which has made no difference whatsoever. Does anyone have any idea how I can get this up and running again?
View Replies !
Suddenly Mysql Won't Start (LAMP Server)
I use a LAMP-server running on Debian Etch as a virtual host on VMWare. I use mysql for a web gallery, but a couple a days ago it seems to stop working, so I tried to restart mysql and webserver, but mysql then fail to start. The syslog have the following info about it: Jul 22 13:11:12 webserver mysqld_safe[3587]: started Jul 22 13:11:12 webserver mysqld[3590]: ^G/usr/sbin/mysqld: Can't read dir of '/tmp/' (Errcode: 13) Jul 22 13:11:12 webserver mysqld[3590]: ^G/usr/sbin/mysqld: File '/var/log/mysql/mysql-bin.index' not found (Errcode: 13) Jul 22 13:11:12 webserver mysqld[3590]: 080722 13:11:12 [ERROR] Aborting Jul 22 13:11:12 webserver mysqld[3590]: Jul 22 13:11:12 webserver mysqld[3590]: 080722 13:11:12 [Note] /usr/sbin/mysqld: Shutdown complete Jul 22 13:11:12 webserver mysqld[3590]: Jul 22 13:11:12 webserver mysqld_safe[3592]: ended Jul 22 13:11:29 webserver /etc/init.d/mysql[3727]: 0 processes alive and '/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf ping' resulted in Jul 22 13:11:29 webserver /etc/init.d/mysql[3727]: ^G/usr/bin/mysqladmin: connect to server at 'localhost' failed Jul 22 13:11:29 webserver /etc/init.d/mysql[3727]: error: 'Can't connect to local MySQL..
View Replies !
Foreign Characters Suddenly Causing Issues
We recently moved the websites that store their data in this database from a box that runs PHP4 to a box that runs PHP5. The mySQL server is on a separate machine and has not been moved or altered in any way. Now, all of a sudden, when a field contains a foreign character, the content of the field including and following the first occurence is being truncated. For example, 'Seimensstrße' is being truncated to just 'Seimensstr'. The odd thing is, if you grep mysql.log on the server, and view the full query which is being executed, the characters ARE in there. We execute "SET NAMES 'utf8'" on every newly opened connection. This has been working for months, now, all of a sudden, even though the query looks correct, something has broken.
View Replies !
POS System
I am working on a project to develop a POS system. The hardware, pda with barcode scanner, was chosen and i would like to ask how can those data in pda to synchronize the mysql in linux?
View Replies !
System Variable.
i am running mysql version 4.0.16-nt. now, i am connected to the server and i build some tables etc etc. everything is fine. i run the command "show variables". it executes fine and shows me the values of all the variables on the server. now if i try and change the value of the variable tmpdir using the set command, it tells me unknown "error 1193: system variable tmpdir". help needed here........ another point, the variable "tmpdir", points to the dir c:/windows/temp. i put a text file in that dir and try to load some data from that file into a table, and i get an error "file not found".
View Replies !
System Variable
i am connected to the server and i build some tables etc etc. everything is fine. i run the command "show variables". it executes fine and shows me the values of all the variables on the server. now if i try and change the value of the variable tmpdir using the set command, it tells me unknown "error 1193: system variable tmpdir". help needed here........ another point, the variable "tmpdir", points to the dir c:/windows/temp. i put a text file in that dir and try to load some data from that file into a table, and i get an error "file not found".
View Replies !
Booking System
Hi, does anyone know of a site (tutorialised / open source) that offers a PHP-MySQL Online Booking System (similar to a Hotel Booking System)
View Replies !
System Configuration
I'm currently rebuilding my home system - a couple of linux boxes with Debian. Although I don't have a high load on any of my systems, I am going to set up a separate box with my mail server and another for my web services. I'm wondering if there is an arguement for or against putting MySQL over on the mail box or even putting it on its own box?Assuming the arguement is for putting MySQL on a different box, are there any pitfalls, tips, tricks I should consider before starting this project?
View Replies !
Scoring System
Im sure some of you have come across 'neave' games? The scoring system is setup as below: CREATE TABLE games_pacman ( name VARCHAR(20) PRIMARY KEY NOT NULL, score INT(11) DEFAULT '0' NOT NULL, ip VARCHAR(15) ); CREATE TABLE games_banned_ip ( ip VARCHAR(15) PRIMARY KEY NOT NULL ); Clearly, it is possible to ban ip addresses from using the score system. But, can anyone tell me please if it is possible to set it up to ban certain names from being used?
View Replies !
Voting System
*********************** | game_id | discussion_id | *********************** SELECT game_id, discussion_id, count( * ) FROM `games_voting` AS votes GROUP BY votes.game_id, votes.discussion_id using the above query to generate a count of votes grouped by game and then user who got the votes right now it returns all the users that received votes for each game, i'd like to only receive the user with the top vote count
View Replies !
System Log For Triggers
Is there a system log file which can be checked to see the histroy of triggers. I am trying to debug a trigger that is not executing when it should.
View Replies !
Registration System
I have my database and HTML forms made. When I click submit to submit the registration it doesnt go to to the database it comes to a page with all of my database stuff on it. Can someone help? I have the page register.html made with the forms on it. When you click on submit its supposed to go to register.php which will send the info to the database. But is doesnt. Here is the register.php code:
View Replies !
Use This Database System?
I have just downloaded 5.1 and have been following the MySql book by larry Ullman. I have several question in regards to this database. First I would like to know if this is considered a back-end system. I have used ms access before and know that you can print all types of reports and forms to enter data. Is that the same with the mysql. How do business really use this database? Creating a table and colums can be very long is there a way to do this faster. I guess my main question how can this database be utilized besides for websites etc.
View Replies !
System.OutOfMemoryException
sql 2 error: Exception of type 'System.OutOfMemoryException' was thrown. this is the error I am getting using Mysql connect and Visual Studio. My program is set on a loop and after the program has opened and closed around a million connections in my error logs i see this error about 80 or 90 times then it gives me a timeout error until I stop the loop. If i try to restart it it will continue to fail unless I restart the computer containing the database. Does anyone know what is causing this or how I can fix it? Edited 1 time(s). Last edit at 07/18/2007 12:39PM by Joe Gates.
View Replies !
System Error: 113
I've been using MySQL and PHP for serveral years now. Recently we moved our mysql host to another host. Now this is how our system is setup. We have a VPS (Virtual Private Server) in Romania on which MySQL is running on port 3306. Our HTTP host is in USA and we connect to the MySQL server via this script: include("MySQLConfig.php"); // connect to the mysql server $link = mysql_connect($server, $db_user, $db_pass) or die ("Could not connect to mysql because ".mysql_error()); // select the database mysql_select_db($database) or die ("Could not select database because ".mysql_error()); The MySQLConfig.php is this: $server = XXX.XXX.XXX.XXX:3306 $db_user = our user name $db_pass = our password $database = name of the schema we want to connect to. The problem is we get this error: Warning: mysql_connect() [function.mysql-connect]: Lost connection to MySQL server at 'reading initial communication packet', system error: 113 in /home/absoluti/public_html/ProcessRegister.php on line 62 Could not connect to mysql because Lost connection to MySQL server at 'reading initial communication packet', system error: 113 No idea what is going on as this has not happened before.. The script we use have been working before it just happened now after moving MySQL host and setting up with the latest version of MySQL :S can anyone help please? Thanks. -Arosh
View Replies !
System DSN Settings
We have an excel spreadsheet that contains macros to connect to a mysql DB (eroDB) via the ip - 192.168.1.50. the name of the dsn is 'auto_pny', username - dbr, password and all these other details are already defined in the system dsn area. when we try to execute the statement within excel, the connector/odbc popup keeps appearing with a different set of login credentials filled in (i.e. instead of server - 192.168.1.50, it has 192.168.4.67, user - pny ...) I have to change this everytime we need to exceute the query. Is there a way of changing a setting so I don't have to keep doing this all the time?
View Replies !
Getting System Date
I was wondering how to set up table to column to get the system date for a date stamp. Could someone help me with this. I tried date date SYSDATE with no luck
View Replies !
Best Way To Set Up Membership System
My site will have users that sign up and can then become part of multiple groups (think similar to Yahoo Groups or something like that). How would be the best way to set up the database/table structure so queries could be run to find both all the groups that a user is a member of and also get membership lists for each individual group?
View Replies !
System Tables
When I create a new DB it's empty. Are there a base set of system tables / functions and so forth created?
View Replies !
System Tables?
Is there documentation on the system tables? I couldn't seem to find anything when I did a search on system tables. I know about the information schema, but I want to know about the tables the information schema uses to build the views it contains.
View Replies !
Rating System
i want to do a rating system like youtube's where it averages out the total ratings. i'd store each of the ratings per each row and have them seperated by comma's then in my code put them in to an array. i also wanted to store the usernames of the voters in the database in the same fashion (without displaying them though) as above say instead of using cookies (as they can be deleted). would method this be feasible? or would this say overload the database and cause slow down?
View Replies !
Ranking System
I've made a forum and i want to have a ranking system with it. I managed to display a postcount but i also want a few images to be displayed, like if you have made more then 50 posts, it displays 50.jpg or something, how can i accomplish this, in another way then just str_replace()ing all the number of posts? And how do i display 2, like if you have 50 posts, 50.jpg and if you have 60 posts, 60.jpg.
View Replies !
School Mailing System
I am developing a bulk email system to send messages to parents of the children in the school I work at, using php and mysql. The database holding the parents' email addresses and what yeargroup their child is in. I would appreciate any input on the best method of tracking a pupil's yeargroup as they move up through the school. For example, I would like a field in each record to indicate what yeargroup they are currently a part of and when the next academic year comes around there wouldn't be any needs for an update.
View Replies !
Standardized Naming System
is there a common naming system for db objects ? Like: 1) Tables: mytable, tblmytable, tbl_mytable 2) Indices: idx_anindex 3) Columns: int_somenumber, date_lastupdate 4) id for the numerical primary key e.g. table customers.id and then for referencing foreign keys table addresses : addresses.customer_id or addresses.customer_fk OK, I know I could name them the way I want but perhaps there is some kind of common sense in this regard ?
View Replies !
Standardized Naming System ?
is there a common naming system for db objects ? Like: 1) Tables: mytable, tblmytable, tbl_mytable 2) Indices: idx_anindex 3) Columns: int_somenumber, date_lastupdate 4) id for the numerical primary key e.g. table customers.id and then for referencing foreign keys table addresses : addresses.customer_id or addresses.customer_fk OK, I know I could name them the way I want but perhaps there is some kind of common sense in this regard ?
View Replies !
Fulltext Cache System?
I have a table of about 170,000 rows. Its basically a lyrics database in this format : __________________________ | ID | Song-artist | Lyric-text | --------------------------------------- When i do a query that searches song-artist, it take about 4-12 seconds to full match it. Its their a better way to lay this out, to make it much faster? Or will i have to implement a cache system?
View Replies !
Mysql System Administration..
if it is possible in mysql to change such a factory settings like to increase or limit the maximum connection, increase the maximum storage for mysql in the system. and one more thing if we are in mysql environment is it possible to view how many queries per second mysql i already have... by the way the only thing i know is when in mysql.. mysql>s; ->shows or get status information from the server. any advanced mysql administration information about this?
View Replies !
MYSQL System Tables
I have to 2 questions: 1. I am very new to MySQL. I wonder what is the equivalent table for sysobjects in SQL server 2000. 2. I am trying to connect my hosted mysql db, it gave me mysql error number 2013. I dont know what is the problem.
View Replies !
Scoreboard Ranking System
I have created a high score table that will potentially store > 10000 user scores, and need to determine ranking for items pulled out. Ther have been posts detailing how to do this when selecting ALL records, but in my case, I need to pull out specific rows, and determine their rank against the entire table. For instance, a user gets a high score that would rank them at #1200, I need to pull out their score, as well as the 3 above and 3 below. I accomplished this by storing the insert_id, and selecting rows where the score is above and below (in seperate queries), but this makes it quite impossible to get a rank. An alternative method is having a rank column and updating it every time a new score is added, but if it cant be done in one query, it would be a very inefficient method. Does anyone have any solutions on how to approach this? I am using mysql 3.23, so I dont have use of sub-queries.
View Replies !
User Address System
I'm working on a user based data system. I need to store a user's addresses which could be 0 or more. However the addresses could be domestic or foreign. I can tackle creating a multiple domestic address storage solution very easily. However, modifying this to encapsulate both a domestic and foriegn addressing system is boggling my mind. Mainly because foreign addresses don't all share the same format and ordering. Some use county, some don't. Some list a village, some don't, etc.... I'm looking for anything or anyone to provide me with some guidance and/or support to help development the foreign addressing system. I can modify it myself to integrate the domestic addresses.
View Replies !
Posting System With Time
I need to build a system that posts a message on a given time. Example I write a message -> it goes into a db with al the messages posted for the day -> the next day al the messages will be posted in another db for the frontpage at a given time. So you get msg 1 at 12.00 at the fp msg2 at 14.00 at 14.00 at the fp etc.
View Replies !
Recommended System Specs?
I was wondering if anybody could point me to any documentation or could suggest some info on a small MySQL server that I'm setting up. This will be running Windows and IIS (i know, i know) and be for a maximum of 20 users; nothing special. Any suggestions for processor and RAM or things to look out for? It will be running the MySQL Community Server 5.0.
View Replies !
Operating System Error
I have inherited some linux servers running mysql at my company from a prior administrator and have ran into a few problems. For some reason my mysql server daemon stopped running unexpectedly. I attempted to start the daemon with the mysqld command a recieved the following error. 070917 10:22:33 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'open'. InnoDB: Cannot continue operation. I also found another command to start the daemon in some sort of safe mode and it shows the followin error: Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/lib/mysql/mysqlsrvr.pid 070917 10:20:46 mysqld ended I have checked permissions on pretty much every directory I could think of but still I get this error. Can someone please help?
View Replies !
External System Call
Is it possible to trigger external system calls from mysql db? I want to start an external script after a certain value has been set in DB?
View Replies !
System Variable Tx_isolation
The System Variable tx_isolation is used for what? What this actually means, Development Server System Variable Value tx_isolation READ-COMMITTED Production Server trx_isolation REPEATABLE-READ What will be the difference between these two thing?
View Replies !
MySQLdump Crippling System
I'm trying to backup a 3 GB MySQL database on a production web server. I started working on this server the other day and it is quite old and is running ancient packaged in FreeBSD, etc. I also had a look at the backup system and it didn't appear to be working as intended. Can someone with experience just verify for me that I can use mysqldump --flush-logs --all-databases > backup.sql in order to backup all of my databases into that file? And is there a better way of doing this? It seems to take up a lot of system resources on this server.
View Replies !
Disable Privilege System
If I set skip-networking, what is the security risk if I also skip-grant-tables? Is there any performance benefit that might justify leaving the privilege system disabled? This is a dedicated database that runs on the same machine that hosts the application that it supports.
View Replies !
|
TRACKER
