Performance | Forum profile

There are two questions -- * How many users can be connected at any moment (that is, between Connect and Disconnect) * How many queries per second can MySQL sustain. Users should disconnect when finished; it takes some resources to have even an idle connection. With only 2GB of RAM, I would recommend setting max_connections to 500. MySQL, on your config, can execute perhaps 300 queries per second, perhaps 2000. It depends on how...

Hi Rick, thanks for replying. I used ALTER 1_index `id` to convert the INT to TEXT. The redundant key was my mistake :) We have an index on word, not key twice :)

I'm not good at sql, I'm using hibernate. What I propose is simply to have N identical tables, with a suffix in the table name indicating the index. Then based on my criteria that tells me which table the record(s) are in or should be in, I generate the appropriate SQL to reference one of the N identical tables. Will this reduce contention on table lock? Andy

I understand, but the thing is i am using around 600MB of 1GB. I mean there is free memory which could be used, so why is it swapping?

I am attempting to load data via "load data infile" into mysql at a rate of 22k rows / second every second (1.9 billion rows per day) concurrently with user queries. This is a crazy load rate I realize. This data is loaded into 1 table (about 60 columns with a avg row length of 300 bytes) using the innodb engine. The table has 1 index comprised of 4 columns (date and a few number columns) It is running under 64 bit linux on a...

Thank you for your reply Rick. It really seems that SET datatype with the use of binary operators (AND, OR) is very very fast. Although indexes can't be useful with such a schema (myquery) in this case runs in less than 0.06s for 140.000 products which is impressive. I think that this is a good case where you keep a schema not normalized to take advantage of the speed that binary operators offer. Dimitris

We are running MySQL 5.0.86 (RPM install) on our RHEL4 production server, and in the last couple of months, we have been experiencing a couple of crashes, where basically the server comes to a crawl and our website is no longer available. We connect to MySQL through PHP using ext/mysql with mysql_connect(). This is a dedicated MySQL server with 16GB of RAM, and pretty much nothing else runs on that box. When the crash happens, top output shows...

I have a query that takes 14 seconds to execute: SELECT posts.* FROM posts force index (idx_post_date) INNER JOIN follow ON follow.followuserid = posts.userid WHERE follow.userid=’61585′ ORDER BY date DESC LIMIT 0, 20; The table posts has ~1000000 records and the number of records in the posts table belonging to userid 61585 is 0. The table follow has 63537 records. This is how my indexes are set:- mysql> show index from follow;...

My query is not performing like I would expect and the plan is showing that there are lots of rows scanned even though the index is used. Below is the plan and the index. mysql> explain select this_.id as y0_ from MonitoredResult this_ inner join MonitoredItem item1_ on this_.monitored_item_id=item1_.id left outer join MonitoredTweet item1_1_ on item1_.id=item1_1_.id left outer join MonitoredArticle item1_2_ on item1_.id=item1_2_.id...

Thanks a lot for those answers. It seems like tuning the server configuration is quite important, right? I guess my best choice is to start reading some documentation about setting up the server the best possible way. Thanks again :-)