MySQL Consuming Lots Of Memory During Transactions W/ Many Statements
I am having a problem with MySQL consuming a lot of memory and
eventually throwing an Out of Memory error and restarting itself. The
symptoms are that swap usage continues to rise until some breaking
point. The application is a typical web application w/ 2 web servers
running Apache/Tomcat connecting to a dedicated DB server running only
MySQL.
This seems to occur as a result of running many statements in a single
transaction, both against InnoDB tables and MyISAM tables. In one
case, I'm writing all user actions to an audit table (MyISAM), all from
a single application thread doing approximately 5K inserts (as single
INSERTs on a single connection) every few seconds. In the other, I'm
doing a variety of select/update/insert/delete statements against
InnoDB tables, but again totaling several thousand in one transaction.
Both of these problems occur even when there is relatively low activity
elsewhere on the system. Code:
View Complete Forum Thread with Replies
Related Forum Messages:
Lots And Lots Of Threads :: Slow Server
currently, mySQL threads are using all my CPU - causing site slow down. We have 2x 3.2Ghz processors and only around 10k visitors a day. It seems that the server is creating lots of mysql threads, whereas on another server that I monitor (running completly different setup) no matter how many visitors there is only 1 mysql thread running, sometimes that goes up to 90% CPU usage, but theres only 1 thread, whereas on the new server, the threads seem to take up all the CPU no matter how many visitors we have, we currently have around 6 mysql threads that are using 60% cpu and a few using around 9% (not sure how thats even possible). If there were more threads they would use a lot less, but if there are less then they use a lot more. Basically, I want to know a) why is one server creating lots of mysql threads and why is the other only creating 1 and b) how can I reduce the CPU load.
View Replies !
Lots Of Data Lots Of Users..
If I have around 5,000-10,000 pieces of data (which will get updated daily) that I need to display in a grid and will expect 1000+ visitors a day viewing this data, what is the best way to go about doing this? Would it be better to export all the data to a database and then import to grid or just put the data into text file or something and import to grid from that?
View Replies !
Out Of Memory; Check If Mysqld Or Some Other Process Uses All Available Memory;” Error
We have found that the MYSQL on all Email DB servers starts throwing “Out of memory; check if mysqld or some other process uses all available memory;” error. The error goes once we restart MySQL. But after a week again the same problem occurs. We have 4 GB of physical memory on the server but Mysql utilizes only up to 2.5 GB & starts throwing Out of memory error. Can someone from Database team look into it?
View Replies !
Random MYI MYD Files Generated Consuming Disk
named as follows: #sql_5d7e_0.MYI #sql_5d7e_9.MYI #sql_5d7e_6.MYI #sql_5d7e_2.MYI #sql_5d7e_5.MYI #sql_5d7e_5.MYI etc., etc. In my error log I saw the following: Incorrect key file for table '#sql_2684_1.MYI'; try to repair it (many of these for each "table")
View Replies !
Loading Lots Of Data Into Mysql Table
So I need to load lots of data into my database. So I discover LOAD DATA INFILE. Great! This little gem loads my CSV in blazing times (compared to parsing the file and doing INSERT for each row). Its still slow on large files, but just barely acceptable. Only one problem. It truncates fields to 256 characters, even on a text field.
View Replies !
Analyze Table - Optimizing Time-consuming Queries
I'm starting a website-counter service, so basically my site requires a lot of mysql activity. Here are the two tables I use: CREATE TABLE `traffictrack` ( `id` int(11) NOT NULL auto_increment, `nr` int(11) NOT NULL default Ɔ', `ip` int(10) unsigned NOT NULL default Ɔ', `ndate` datetime NOT NULL default ��-00-00 00:00:00', PRIMARY KEY (`id`), KEY `ndate` (`ndate`) ) CREATE TABLE `tracker` ( `nr` int(11) NOT NULL auto_increment, `email` varchar(70) NOT NULL default '', `password` varchar(220) NOT NULL default '', `mdate` datetime NOT NULL default ��-00-00 00:00:00', `ip` int(10) unsigned NOT NULL default Ɔ', `image` varchar(200) NOT NULL default '', `friendid` int(11) NOT NULL default Ɔ', KEY `mdate` (`mdate`), KEY `nr` (`nr`) ) I have some queries that take up more than 1 second: PHP SELECT INET_NTOA(traffictrack.ip) as ip, tracker.image, UNIX_TIMESTAMP(traffictrack.ndate) as ndate, tracker.friendid, ( UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP(traffictrack.ndate) ) as dif FROM traffictrack LEFT JOIN tracker USING(ip) WHERE traffictrack.nr = ?' AND DATE_FORMAT(traffictrack.ndate, '%Y-%m-%d')=��-07-23' ORDER BY traffictrack.ndate DESC LIMIT 20 also: PHP SELECT COUNT( traffictrack.ndate ) AS total, tracker.image, tracker.friendid, UNIX_TIMESTAMP(traffictrack.ndate) as timest FROM traffictrack LEFT JOIN tracker USING ( ip ) WHERE traffictrack.nr = ?' AND ( UNIX_TIMESTAMP( ) - UNIX_TIMESTAMP( traffictrack.ndate ) ) < 600 GROUP BY tracker.nr LIMIT 0 , 30 I believe it's because my tables are badly indexed, I added an EXPLAIN before these two queries, they both ended up with: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE traffictrack ALL NULL NULL NULL NULL 34246 Using where; Using temporary; Using filesort 1 SIMPLE tracker ALL NULL NULL NULL NULL 20972 If someone knows how to speed up my queries, please help.
View Replies !
Memory Usage :: Swap Memory Not Released?
We have Mysql 5.0 running on RH linux. We have noticed that the swap memory increases but never gets released unless we reboot the server every 2-3 weeks. How would I know whats using this swap memory? Second question which I think maybe related to the first is that mysql is using a lot of memory when I do a top. Is there a way to limit the memory usage of mysql? I have a large table in mysql about 7 GB and I have 8 GB physical memory and swam is set to use 2GB.
View Replies !
MySQL Transactions
I found a pretty good(and simplified) article on transactions for the new people to sql out there, however, you will have to have some prior knowledge of howto use commands, And why you would want to implement something of this nature. So, I guess, Intermediates in MySQL, go here.
View Replies !
Can I Do Transactions In MySQL?
I tried to change my sql queries for a program i originally wrote in SQL Server 2000 into MySQL, what irks me is that if i try to run two consecutive insert queries the first query is run but the second one gets ignored.WHY? The code reads thus: <?php $sql = "INSERT INTO smsusers (FNAME , LNAME , USERNAME , PASSWORD , TITLE , PHONENO) VALUES ('$thisFNAME' , '$thisLNAME' ,'$thisUSERMAIL' , password('$thisPASSWORD') , '$thisTITLE' , '$thisPHONENO')"; $result = MYSQL_QUERY($sql); ?> <?php $sql = "INSERT INTO profilecontents (CONTENTIDID , PROFILEIDNAME , CHECKER ) VALUES ('ACCTINFO' ,'$thisUSERMAIL')"; $result = MYSQL_QUERY($sql); ?> Perhaps there is sumthin' wrong with ma code????
View Replies !
MySQL And Transactions Entries
I need to insert a new entry in a table but one of its field is an order field so i use the MAX of current entries in the table. To lock tables to be sure that 2 persons can't have the same MAX at the same time, I use transactions. Nevertheless, How to be sure to get a WRITE lock on this table if my first statement is a select ? I'm doing this : BEGIN; SELECT MAX(ESSAILOCK_ORDER) AS MAX FROM ESSAILOCK; INSERT INTO ESSAILOCK (ESSAILOCK_TITRE;ESSAILOCK_ORDER) VALUES ('essai3',MAX); COMMIT; If the select is in first, am i just getting a reading lock, which could allow someone else to insert a new entry with higher order maybe.
View Replies !
Transactions With Java And MySQL
I'm trying to port the use of postgresql db to mysql. I'm using Java and having the following problem - I can't seem to write more than 1 query per execution. Easier to explain with an example that worked under postgresql and does not using mysql: aStatement.executeQuery("BEGIN; DELETE FROM table1; DELETE FROM table1; DELETE FROM table3; COMMIT;"); If I separate this into 5 different queries it works fine. Any ideas?
View Replies !
Tracking MySQL Table Transactions
I am trying to understand how I could manage MySQL transactions. I have this database that contains a very important table. Its data changes a lot and contains student grades. I could have another table where I would keep track of the original table's transactions. I make a backup of this table on a weekly basis and I'm thinking that if I keep track of the sql queries I could recreate the data at any point. So, if a user tels me that he/she overwrote the data, I would restore the portion of the table from the weekly backup, and then go to the transaction table and issue the queries up the point where the data is valid. Is this a good way of restoring data? Is there a better way? Does MySQL have a buil-in system of restoing data?
View Replies !
Using MySQL With Hibernate, Transactions For Selects
I have a general question about transactions around reads. I'm using hibernate and mysql 5.0. What are people's thoughts on wrapping select statements with transactions? I don't believe this will add significant overhead, but is there a disadvantage to not using a txn in this case?
View Replies !
Lots Of Dbs
Is there a problem (performance, anything else) with having lots of databases? I'm building a site where each user can have his own forum, which means a single database per user that signs up. This can quickly grow to more than 1000. I need to know if it's sustainable.
View Replies !
Mysql On Flash-memory
I have a question about flash-memory lifespan. My database consists of 1 table, the table has 50 fields of 10 bytes each, thus each record will have 500bytes. There are 1000 records in the table, so roughly the table will have a size of 0.5MByte. The system will update the record sequentially, one record is updated per second. That is 3600 update per hour or 86400 per day. If I store this database on a 64MB flash-memory card then how long does the card last before wearing out.
View Replies !
MySQL 4.0.13 Memory Problem
I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 13-18 threads serving databases. To increase available free memory I've did following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 16Mb This changes helped a bit but still memory is a problem for MySQL. Average MySQL thread used to consume 100-120 Mb memory before changes, now it is consuming 60-70Mb per thread. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 32Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time.
View Replies !
Memory Alignment In MySQL
CREATE TABLE my table (coll_a INT NOT NULL, coll_b SMALLINT, coll_c SMALLINT, INDEX b_index (coll_b) ); Above is the table I will created. I declare coll_b and coll_c, which together will cose me 4 bytes in one row. Is it? What if I declare both of them as INT, will it cost me more memory? How about the memory needed for the index b_index? Not sure how memory alignment will affect MySQL.
View Replies !
MySQL And Memory Consumption
I've done a new install on a Freebsd server (5.1), using the latest ports of MySql 4.1. Everything seems to work great. However when I do a "top", I notice that Mysql is consuming around 321M of memory. This is is a virtually empty database with nothing loaded into it yet. Everything seems to be performing fine. Its just that I don't ever remember MySql using this much memory just after a new installation.
View Replies !
Mysql Is Not Using ENOUGH Memory On Linux
Running mysql 3.23.56-1.9 (yes , upgrading soon!) on red hat. Our database: - data is 5GB or so - has approx 2GB of indices We have 4GB of memory on the machine. At least 2.5GB of that is free+cached [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock set-variable = key_buffer=500M set-variable = myisam_sort_buffer_size = 64M set-variable = table_cache=600 set-variable = join_buffer=1M set-variable = thread_cache = 8 set-variable = sort_buffer =4M set-variable = record_buffer=4M set-variable = thread_cache_size = 64 set-variable = thread_concurrency=8 set-variable = max_allowed_packet = 16M set-variable = tmp_table_size = 64M But, no matter how much memory I set for key_buffer in /etc/my.cnf, mysql seems to use very little of it after running millions of queries: 14951 mysql 15 0 226M 221M 1308 S 3.3 5.5 89:55 0 mysqld
View Replies !
MySQL Client Ran Out Of Memory
I'm using a mysql server with my own TCP/IP server on the same FreeBSD machine. MySQL stores a users database, like logins and passwords. It works fine for a couple of days, but after that my simple queries with one row of result stops working. It says "MySQL Client ran out of memory". If I connect to mysql using other tools or simply restart my server - it works fine again under the same queries. So please don't support I use a huge amount of data, whole database can be selected in one query under normal conditions, its pretty small. My Server Info: sr10# mysql --version mysql Ver 14.7 Distrib 4.1.16, for portbld-freebsd5.4 (i386) using 4.3
View Replies !
MySQL With C++ Causes Memory Leak?
The garbage collector tells me it's using the same amount of memory, but the Task Manager shows it using 4-10KB more each second. Any ideas? If i remove all references to the actual datareader then the mem usage is stable, but as soon as it starts to return records then the leak appears. Code:
View Replies !
MySQL Memory Leak
I appear to have a memory leak problem caused by mysql I currently have 10 instances of /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file ... running but not actually doing anything. These have been running since the 9th (last time I restarted mysql) There are 2 that if you trace process on them are doing something... Process 565 has a subprocess of 567 and then 567 has the rest as subprocesses. But none of those subprocesses are running mysql commands - or doing anything other than eating memory. When I restart the server, applications are using 0.5Gb Memory then that creeps up to currently using 1.1Gb. So what are those dead process and why are they hanging around?
View Replies !
Compiled Mysql++ Memory Leak
I compiled mysql++ with Visual Studio 2003, the problem is I got i memory leak when i debug the application, the leak occurs even when i only use the mysqlpp::Connection class. Note I linked mysqlpp with mysql 5.0
View Replies !
Mysql Eating Big Chunks Of Memory
I'm running version 4.0.16 on solaris 8 as the backend for a web site serving around 12 million pages impressions a month. The performance of the server is monitored using orcollator. I'm finding that the memory usage on the server remains pretty constant for weeks at a time at around 300 Meg but then every once in a while the size of the mysqld process jumps anywhere between another 50 and 250 Meg. It then stays at this new size until mysql is restared (or jumps again). The memory increase appears to remain as resident according to the output of top. The site has a content management system which connects directly to the production data base as well which while it is used daily has some functionality which is only used as frequently as this problem occurs making me suspect it is the culprit. What I could really do with some advice on is - What are the most likely causes of the memory loss. - whether there are tools that allow you to see a breakdown of how the current memory is being used by mysql, or even better monitor it over time, in order to establish the source of the problem. - And if there aren't any such tools how best to approach this.
View Replies !
Lots Of Data With Pictures
i have lots of data for products (with different categories) and pictures that i would need it entered into a database on the website. How would i go about doing so? i was thinking about an excel sheet with each category as a sheet but i have no idea what to do about the pictures that will go with each product, (since i would not want the pictures in the mysql database.)
View Replies !
Insert Lots Of Text
I am trying to use a mysql database with php to create dynamic pages. I have no problem creating a page which holds small bits of information but i would like to have a dynamic page where a lot of text is dynamically stored. I would then be able to alter the pages of my site using the database rather than going into dWmx. at present i can get it to store quite alot of text but it cuts off at a certain limit, also it is also no longer formatted when it is retrieved. the field is set to text. Id be very grateful if someone can tell me if i am going the right way about this.
View Replies !
Lots Of Tables In Database
Lets say that you have alot of tables in your database, but each table doesn't store too much information (just a few small records). Does it still drain process if you have alot of tables?
View Replies !
MySQL 4.0.13 Memory Problem Under Heavy Load
How to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 10-13 threads serving databases. To increase available free memory I've did the following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 128Mb 5- Have increased max_connection from 100 to 150 6- Have increased thread_cache to 5 This changes helped a bit but still memory is a problem for MySQL. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 256Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time.
View Replies !
MySQL 4.0.13 Memory Problem On Heavy Load
I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 13-18 threads serving databases. To increase available free memory I've did following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 16Mb This changes helped a bit but still memory is a problem for MySQL. Average MySQL thread used to consume 100-120 Mb memory before changes, now it is consuming 60-70Mb per thread. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 32Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time.
View Replies !
Memory Allocation Limit On Mysql 4.0.16 Binary For AIX 5.1
I downloaded the mysql 4.0.16 binary for AIX 5.1 from Mysql.com website. How much memory can this binary's mysqld handle? Is it a 32-bit or 64-bit process? It seems that it can only allocate a maximum of 2GB of memeory (by setting the maxdata as suggested on the AIX note in the documentation). I am using mainly innodb tables. Occasionaly, I have encountered "out of memory error". We have 4GB of memeory on our RS6000 (AIX 5.1). It is used as a dedicated mysql server. Is there a way I could allocate more memory for mysqld?
View Replies !
Mysql IF Statements
I looked through the mysql manual but their examples are pretty confusing anyone think they could show me the correct way to write an IF statement inside of a query like this one? SELECT orders.order_id, students.fname, students.lname, order_status.order_status_name, course.name AS course_name, date_format(class.startdate, '%m/%d/%Y') AS startdate, date_format(class.enddate, '%m/%d/%Y') as enddate, order_type.order_type FROM orders LEFT JOIN students ON orders.student_id = students.student_id LEFT JOIN order_status ON orders.order_status = order_status.order_status_id LEFT JOIN class ON orders.class_id = class.class_id LEFT JOIN course ON orders.course_id = course.course_id LEFT JOIN order_type ON orders.order_type = order_type.type_id IF(order_type.type_id == 1) THEN SELECT lm_number FROM lmcharge WHERE orders.order_id = lmcharge.order_id; END IF WHERE orders.order_id = '8'
View Replies !
One Big Query Vs Lots Of Small Queries
I am in the process of migrating a MySQL database from one schema to another and am writing a script to extract the info from one table to be inserted into multiple tables on new new db. My question is this, is it better for me to make one giant query (about 1 million records returned) on the source table, manipulate the data, then enter in the data by cycling through the results, or would it be better to split up the query on the source table into lots of smaller queries with a short pause in between?
View Replies !
Lots Of Aborted Connection In 5.0.18 Version
After a long troubles in installing mysql 5.0 in windows 2k3 (Problem with that cannot connect using localhost) i finally upgraded mysql on November 25th 2006. From that day till today I may have got 100s of aborted threads/connections in my server log. I am not sure about that and not sure about the reason also. Could you please help me in correcting the same. Below is the error message I am recieving always 061206 12:55:00 [Warning] Aborted connection 6130 to db: 'mydatabase' user: 'myusername' host: 'localhost' (Got an error reading communication packets) No Idea why this is happening daily. Per day there will be atleast 5 threads gets disconnected.
View Replies !
Changing Usernames On Lots Of Tables
I have a database intensive web site and we allow people to change their usernames. When someone changes their username, over 32 queries have to be made in order to change their username to the new one, because there's tons of tables. Everytime we add more tables that number only continues to grow. So we decided to use account id's (primary key) instead, replacing the usernames, and using JOINS to grab the username each time it's needed for display. Eventually, to change someones username, only one query would needed. Now TONS of CPU is being used due to all the joins. All indexes are in place and types are always const, ref, and/or eq_ref. What would be the best solution to this problem? Would subqueries be better?
View Replies !
Deleting LOTS Of Duplicate Entires Except Keep 1 Of Them
I have a database that stores all my RPO codes and it's descriptions. Everything was fine until I discovered (assuming) a search engine BOT must have run the script I wrote that imports the entire list into the database. I ran it once to import the list of 23,000+ RPO codes. But now it seems it was run 28 times more. So rather than 23,000+ in my MYSQL Database, I now have almost 652,000!! MOST of each duplicated 28 times or so!! Manually deleting 629,000+ Entries is NOT what I call fun, nor efficiant so I assume you can easily do this using a SQL QUERY.. I want to do the following: 1. Search for 2 fields in my database "RPO_CODES" and "DESCRIPTION" 2. DELETE ALL of the duplicate fields where "RPO_CODES" AND "DESCRIPTION" duplicate other entries 3. However keep 1 FIELD of each.. I only want to delete the DUPLICATE ENTRIES.. Example: Entries. 1. RPO Description 1. L98 5.7Liter V8 Engine for Camaro's and Corvettes 2. L98 5.7Liter V8 Engine for Camaro's and Corvettes 3. L98 5.7Liter V8 Engine for Camaro's and Corvettes 4. L98 5.7Liter V8 Engine for Camaro's and Corvettes 5. L98 5.7Liter V8 Engine for Camaro's and Corvettes 6. L98 5.7Liter V8 Engine for Camaro's and Corvettes 7. L98 5.7Liter V8 Engine for Camaro's and Corvettes 8. L98 5.7Liter V8 Engine for Camaro's and Corvettes 9. L98 5.7Liter V8 Engine for Camaro's and Corvettes 10. L98 5.7Liter V8 Engine for Camaro's and Corvettes So it ends up with just 1 entry ...
View Replies !
Lots Of Updates And Inserts - Use Innodb?
I'm working on a browser game and have been using MyIsam for my tables so far, but I've read about Innodb and it seems like something I probably should use for at least some tables. It's a browser game, so I keep track of players stats in different tables (about 5-10), and each of these tables are updated a lot. Whenever an item or something is bought, equipped, sold, there has to be updates in different tables. There will (hopefully) be lots of players online, each equipping and managing their items and such, playing against each other and so on. I try to "cache" or save static information instead of getting fresh data from the database whenever I can, so I think the number or Inserts/updates will actually be pretty high compared to selects. The tables are locked table wise with MyIsam (?), and row-level with Innodb, or so I have read... I get the impression I should use Innodb on the tables which are updates/inserters frequently, and leave MyIsam for less intensive tables like archiving and player logs. The tables are pretty small, the total size in GB with 1 million players (!) is probably less than 5 gb if they are all MyIsam.
View Replies !
Command That Lowers The Amount Of Memory That The Process MySQL?
Someone knows a command that lowers the amount of memory that the process MySQL (mysqld, mysqld-nt...) uses??? My work is to execute many INSERT instructions based on SELECT instructions. When the execution starts, all be fine, but the time passes and this process "mysqld-nt" start grow and grow and then the performance be really bad. I tried to use "FLASH" and "RESET" between the INSERTS but no sucess. Someone knows?
View Replies !
Logging MySql Statements
My server load goes up to 100 and I don´t know why. What I know, if I shot down the site, the server calms down and it works again. I have figured out that i have a mysql problem but I can t figure it out. Is it possible to log all MySql Statements which are running on my database ? If the server goes strange again I could check the log file for the problem.
View Replies !
"Consuming" Rows - Best Way To Do It?
One part of my application generates rows (think of them as to-do items) in a table. Periodically, an independently-running part of the application "consumes" those rows: after retrieving the rows and acting on them, the rows should be deleted. However, during this "consumption" processing, the first part of the application might add more rows to the table. Can anyone suggest a clean and efficient way to implement this model in MySQL, minimizing the number of queries? I'd prefer not to DELETE each row as it's handled -- rather, I'd like to drop them "in bulk" when processing is through. If I've consumed all the rows, a simple TRUNCATE TABLE would probably be quick, but I'm not sure how my "consumer" would be able to ensure that no additional rows had been added while it was doing its processing, and I don't want to lock the table during the entire process. I could also add an AUTO_INCREMENT ID field to each row, keep track of the highest one I processed, and DELETE FROM table WHERE ID < highestid... not sure if this is an appropriate approach either.
View Replies !
Fulltext Search Lots Of Load On Server
I have 300k rows in my db, 1 gig of ram. I do fulltext indexing on a large field (textual). It appears that fulltext indexing puts a lot of strain on my server. I start bumping up iowait numbers pretty hard. Has anyone seen this, anything I can do. Alternative is to build tables of words as PHPBB does. Any performance tradeoffs. I do know the functionality tradeoffs.
View Replies !
Replace One Word With Another In Lots Of Rows In A Database
Is there in any way (with MySQL or PHP or software) to replace one work with another in all rows in a table? I have about 7000 product descriptions in Swedish and I need to translate them into English. The good thing is that all the description only contain a few words (I think that I just need to replace about 30 words in total to translate everything). The bad thing is that I don't know how to do that. So I want to select all rows with the language id for English (currently all English decriptions are in Swedish) and then replace all occurances of a specific word with another specific word. So UDPATE description ???? WHERE language_id = 5 is what I've figured wold be a start for an MySQL command... the problem is that I don't know what to put instead of ????. Would it be possible to do that in PHP or MySQL? Could you help me with any kind of command (is that how you say it?) or any other help?
View Replies !
Single Quotes In Mysql Statements (PHP)
Should I be using single or double quotes in statements such as $db = mysql_connect ("localhost", "user", "password"); I know double quotes work, but I know PHP doesn't parse single quotes (therefore faster execution) and *most* statements seem to work fine with single quotes.
View Replies !
Limitations In Mysql Select/update Statements
I'm working on several tables at one time and once in a while I need to update them based on a previous select statement. This ends up with 3-5 statements but every single time I do it this way, mysql chokes after the 3rd statement, no matter what order. I'm on Mandrake Linux 9.1, PHP 4.2.3 with mysql 4.0.11a and Apache 1.3.27. Everything else works like a charm but not these 3-more statements.
View Replies !
|
TRACKER
