MYSQL :: Slow Performance After Falling Back To MySQL 3

Slow Performance After Falling Back To MySQL 3
Yesterday I have upgraded my linux box with a pair of Xeon 1G CPU and
reinstalled everything (it was a sinle Xeon 700). The default RH9
installation comes with MySQL 3.23.54. After the reinstallation I
found the machine was not as responsive as before. (It's not very
slow, but some lagging is noticed)

I was running Mysql-max 4.0.11 and the performance was ok, except the
slowness during the peak hours, which was regarded as normal. I would
like to know the differences between Mysql 3 and 4. Is the new version
making a significant jump in performance?

I also noticed the system eats up memory faster than before. With the
old setting the system never used up 60% of physical memory, even at
extremely high loading (30+). Now it easily used up 70% of memory.

The server is running Mysql only. It's equipped with 2 Xeon 1G and 2G
of memory. The kernel version is 2.4.20-8smp #1 SMP. Also attached the
my.cnf for a clearer picture: Code:

My MySQL Back-end Works Very Slow
I have installed my MySQL Server in a Window XP platform running in a Pentium4 machine. When I access the database on the machine, it is working in a normal speed. But when I access the database from a client machine (still running in windows platform), it runs very slow.

Slow Performance
On my index.php page, I have a simple query that checks the session_id against a table where I store other session_id'. If it's not there, it records it (unique hit). If it's there, it doesn't record it (not a unique hit.) This usually goes off without a hitch, and every month or so I empty the table.

Right now I only have about 2500 rows, and it's taking forever to load the page. Is there something possibly server related that could be causing this? My host charges an arm and a leg just to see if there's something wrong if I bring up an issue, so I'd like some insight as to whether there's a commonly known server-side issue that can bog down performance.

Slow Performance Using MyODBC
I am creating a pair of applications that essentially transfer data from a remote client to a local server. The trick is that "remote" in this application means "way out in the mountains where it takes an hour by four wheel drive to reach the nearest paved road and don't even talk to me about a cell tower for another hour or so of driving".

Because of this, we are using shoe-leather-net to communicate between the client and the server. The client is collecting data into a MySQL table. Every so often, a person will use the client application to copy the data onto a thumb drive and carry it down the mountain to the server, where the partner application will copy the data into an identical MySQL table. These are distinctly non-technical people I am talking about here - typing a file name is a high-skill activity - so this needs to be a dead-simple user interface.

The data is sensitive, so it is encrypted going on to the thumb drive and decrypted coming off.

Here is the question: The copy process that I have programmed is very slow - on average 2 seconds per row - on both ends of the transaction and this does not seem right. What can I do to improve it?

When the process is running, Task Manager shows that mysqld-nt is using as much of the CPU as it can get and the actual application is only using CPU time once in a while.

The basic client task is this:
-- Read a row from the local table
-- Encrypt the row
-- Write the encrypted row to the file
-- Repeat until all of the unexported data has been copied

The basic server task is the inverse:
-- Read a line from the file
-- Decrypt the line and construct a corresponding INSERT statement
-- Execute the INSERT
-- Repeat until end-of-file

Other possibly pertinent information:
-- Windows XP Pro
-- Visual Basic.Net
-- MySQL 4.1.14-nt
-- MyODBC 3.51.13 Dave Gee

Server Performance Slow
I have designed a service on my site which requires about 30 -40 SQL queries per page to run. Everything works without problems apart from the slowness. The page will load very quickly until it gets to the first query, then it pauses..... and carries on slowly producing the results. (When I say slowly, I mean relative slow compared to the speed this forum loads for instance)All the other web apps with mySQL work without slowing down, but they don't take up as much resources. Even the VB board is very fast.
I'm running a 2.8Ghz 1GB Win2003 server, so I doubt that is a problem.
Is there a guide to optimizing mySQL server or a key "setting" I should be changing?

Memory Maxed Out And Slow Performance
We have two main query types running against a table of some 2 million
rows and have gotten query response down to well under a second by
using the right indexes.

Problem is that we are running an advertising campaign that brings a
concentrated block of users to the site. When this happens one of the
queries which relies on a particluar index comes severely of the rails
and can take up to 2 minutes filling the slow query log for 15 to 20
minutes. The other query type never appears in the slow log at these
times.

Can anyone explain why this is happening?

The server has 2Gb of ram and a key_buffer of 1Gb. When the problems
occur mysql uses all of the key_buffer but the total value of the MYI
files is under 250Mb. What is in memory - thread related buffers?

It seems as though the index required for the query is not in memory
and the disks are coming into play. Why is only one index affected?

I would really appreciate some pointers, what can I check to pinpoint
the problem and what settings should I implement to preserve
performance?

Front end is 4 cold fusion web servers using persistent connections.
max_connections is currently at 100.

MySql Falling Over
I have reinstalled things, but MySql still giving me headaches. I am trying just to edit 1 record, which in browse looks okay, to add into a field. But all I get is a page of gobbledegook data with no suggestion of what to do, with a message:

MySQL returned an empty result set (i.e. zero rows).

Large Table Performance Problem With MYSQL Performance
I am doing some benchmarking of an application using MYSQL under Windows and I confused about the results.

Environment
Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram

mysql 4.1 with odbc 3.51 MYISAM
windows 2003 server std edition
date, account and invoice number are indexed
Database size 18 million rows

I am querying (selecting) columns of a date and an account

our tester program that opens a socket to the Mysql database and does a select for the above n times
each time the date and the account is randomized to minimize hits on records closeby.
This program will perform over 1000 queries per second.
At the end, the Mysql socket will be closed
Even when the socket is closed each time, I still get 400 queries per second.

When I enter a similar query manually a web interface, I get about 3-9 second response time. This program opens/closes a socket for each query when using EMS I get similar 9 second results.

Does anyone have any suggestions Also in production, this table will be accesse for both read and write will I have problems. My testing showed that Innodb is much slower.

Simple Query? Why Is My Hair Falling Out?
Im trying to pull all records from table a and table b for 14 hrs now. It seems so simple?
both tables are indexed with the fields 'classid' and 'states'
actual results should be 36 records as you can see that is not the case.
Here is what ive tried so far:

SELECT * FROM classifieds_g WHERE states='AZ'

records 4

SELECT * FROM classifieds WHERE states='AZ'

records 32

Samples and results of what I've tried (should return 36 records)

SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds.states = classifieds_g.states

records 0

SELECT classifieds.states, classifieds_g.states FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds.states = classifieds_g.states

records 0

SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' GROUP BY classifieds.states

records 1

SELECT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ'

records 4000

SELECT DISTINCT * FROM classifieds_g, classifieds WHERE classifieds.states='AZ' and classifieds_g.states='AZ'

records 128

SELECT * FROM classifieds_g, classifieds WHERE classifieds_g.states='AZ' and classifieds.states = classifieds_g.states

records 0

SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ'

records 128

SELECT * FROM classifieds_g INNER JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ'

records 128

SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ' GROUP BY classifieds.states

records 1

SELECT * FROM classifieds_g LEFT JOIN classifieds ON classifieds.states = classifieds_g.states WHERE classifieds_g.states='AZ' GROUP BY classifieds.classid

records 32 (all the same)

SELECT DISTINCT * FROM classifieds_g WHERE states IN(SELECT states FROM classifieds WHERE states='AZ' )GROUP BY states

records 1

SELECT DISTINCT * FROM classifieds_g WHERE states IN(SELECT states FROM classifieds WHERE states='AZ' )

records 4

Getting MySQL Back
I am getting relay-bin logs on my master server. They are all empty except
for one line that reads:

þbin

I have no slave settings in my my.cnf file. Any idea where these are coming
from and how to turn them off?

Using MySQL As A Back End
I have a couple MS access aplications that I have developed to be used by clients in managing their data. I have split these applications into a front end (containing only forms, reports and a scratch table) and a back end (containing the data in various tables).

Currently both front ends and back ends are in MS Access, but I am looking too use a bigger back end. I have previously looked at MySQL vs. MS SQL server for a more robust back end, but I didn't have to make the switch at that time. Now I'm pretty sure I do.

Is MySQL a good platform to use for this? or would I be better off with a MS SQL server?

Mysql Back-up
We have an online catalogue running off a mysql database, which we can back
up. When we back it up, it creates a file online called "database.sql"
which, when opened in Notepad starts...
DROP TABLE IF EXISTS store_category;
CREATE TABLE store_category (
category text NOT NULL,....etc

MySQL Back End
I have an Access application which uses an Access database.
I want to replace the Access back end with a MySQL one.
Can I link the new database?
Can I do it in any other way?
I reckon I should be able to, since MySQL has an ODBC driver but just not sure how

Back Up MySQL Database
Plese tell me how do i back up a perticuler mysql database? can i do it from mysql command line? can i manually copy and past? if what folder?

Auto Back Up Of MySQL
I want to Automaticly dackup my MySQL DB, and then upload it to a FTP site so I have a off site back up.

Anyone know of any programs (open source is preffered) that would allow me to do this. I was think batch file my self in scedualed tasks but I am no too sure of what commands to use. This is on a windows 200 server too.

Mysql, Get Back Your Youth

<div align="center">
 
<a
href="http://www.herbal-medical.biz/projectx/hgh.html"><img
src="http://www.herbal-medical.biz/projectx/hgh.jpg" border="0">
</a> 
 
 
 
<A HREF="http://www.herbal-medical.biz/projectx/hgh.html">Click Here If Image Doesnt Load</A>
 
 
</div>
<CENTER>WdXfuRttYR, yJJZQwQUqY
 
 
<A HREF="http://www.herbal-medical.biz/optout.html">No More Emails Please</A> </CENTER>

Back Up MySQL In A PHP File
I need a script that will find all tables and all rows and all data it makes a file called back.sql with all the info in it. How do you do that. My Web host dont got PHpmyadmin or nothing.

MySQL Takes FOREVER To Start Back Up
Need some help here. I am running MySQL 4.1 on a windows 2003 server. We've had the server for about a year and right now the data file for the database is up to 345mb. Its growing week by week.

Lately if I had to restart my server once windows comes back up it takes 20min for MySQL to start back up.

Easiest Way To Back Up MYSQL Database To Move Over To Another Computer
I've been working through Sitepoint's Build Your Own Database Driven Website and I've decided to change which of my computer's I use as my test server. I installed Apache, PHP and MySql on the new "server" and tried to copy over my database folder, but I can't seem to get it to work in the MySql client. I looked through the documentation for information about maybe making a backup and then restoring it to the new server, but I was a little overwhelmed. Anyone have an easy suggestion to accomplish this. I'm sure it's much easier than I'm making it.

MySQL Performance
i'm running a webserver with the following configuration:

P4 2,4 GHz | 1 GB Reg. ECC RAM PC266 | 2 * 18 GB SCSI RAID 0
Apache 1.3.28 (mod_ssl, mod_gzip) | PHP 4.3.2 | MySQL 4.0.12

This is the current server usage (200 parallel visitors):

9:57pm up 4 days, 1:45, 3 users, load average: 5.84, 5.48, 7.16
393 processes: 392 sleeping, 1 running, 0 zombie, 0 stopped
CPU states: 14.2% user, 85.7% system, 0.0% nice, 0.0% idle
Mem: 1031008K av, 1017828K used, 13180K free, 0K shrd, 29856K buff
Swap: 1044216K av, 248428K used, 795788K free 630368K cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME COMMAND
18508 mysql 15 0 326M 116M 14492 S 43.7 11.5 7:11 mysqld

It seems to me to be a too high load for this hardware configuration.
Sombody an idea?

Performance MySQL With ADO VB
I'm developing an VB application which uses the SAX parser and ADO VB to
insert.update data in in MySQL. The perormance is very bad. If I use the
same data and insert/update to a MS Access database it takes 14 minutes. But
using MySQL it takes about 5 hours! Here's the piece of code which takes the
most time. Does anyone have an explanation for this? Code:

MySQL Performance
I'm facing a performance issue, I'm using JDBC to read rows on a huge mySQL database 1.5 Million rows.

The programme basically read rows by sample of 1000 rows.

select * from <tbl_name> LIMIT i, i+1000

and print result in a text file.

This takes 5 minutes for the first 500000 rows, 10 min for the following 500000 and 18 for the rest. Which seems to me very slow just for reading rows?

I wonder 2 things:
1- is this normal for mySQL to take this time?
2- if not, is using LIMIT this way in the SQL would have an effect on mySQL performance?
2- how can I improve this performance.

If you think of anything that can improve the performance on a select * from , please let me know.

MySQL Performance
Our database contains about 27,000 records only and it is very slow. Can someone please suggest any tips to increase the performance? We used InnoDB.

Mysql Performance
The case is, i need to create a website with about a quarter million records
database.

I'm not sure about the mysql+php performance.

The other option would be asp.net with sql server 2005.

Could anyone give me your opinion about using mysql+php in this case, is it
worth?

MySQL Performance
We ran MySQL (4.0.15-standard, 64-bit, SuSe 8.0) benchmarks
on 2 CPU and 4 CPU Opteron machines. Mostly we tested insertion
times with many simultaneous connections (hundreds). To our
surprise we did not find significant differences.Each of our servers (2 and 4 CPU machine) has one 1Gbit network
connection. I have feelings that the machines are the network
bounded, not the CPU bounded. Could it be true? How to verify it?

Mysql Performance
I cannot find MySQl performance info at linux "top" command. I already
tested on slow MySQL query that took more than 5 minutes (create
fulltext index), but during the 5 minutes, i cannot find any mysql
process at top command. Below is the system information:

Linux version: 2.6.9-1.667smp (Red Hat 3.4.2-6.fc3)
MySQL version: 4.1.11 (source installation)

MySQL Performance
I have a web application which uses MySQL and I wanted to check out the performance of the database.
I have been trying to use JMeter to test the performance of it but I was wondering if there is anything MySQL specific which can give me statistics on performance etc.
Just wondering what other devshedders do

MySQL Slow
I had downloaded a few years back mySQL v3.51 installed but never used it. Now I wanted to convert some B-TREE databases to mySQL and did some testing via ODBC to insert 70,000 records: My results:

MS ACCESS: ~60,000 msecs
MYSQL v3.51 ~18,000 msecs

Impressed with the speed, I went ahead and got the latest MySQL v5.1. Uninstalled the older version, I had nothing there to preserve, so I did a simple new install with MySQL v5.1. I noticed the size of the files and BINEXE increasted by 1,000,000%. Ok, Bulky. Not a problem.

I reran the same ODBC test, and now I got:

MYSQL v5.1: ~450,000 msecs or 7.5 freaking MINUTES!

What the hell happen? Nothing was done. I'm knew to MYSQL. I just installed it with all the defaults. I did choose "developer's machine" for the "optimizer wizard"

I can't redistribute MYSQL v3.51 and force it down people's throats! I have to use what they are using already, if already installed. Not even my current system takes 1 minute to add 70,000 records. Why 7.5 minutes? All it is simple inserts/free statements.

MySQL Very Slow.
I have this one site that slows down all my others because the queries are so massive.

For example one of these queries I use to-do a search by a user's account number. I also get the position he is at on the list, and in order to-do that I need to select ALL the rows.

For example, I filter out the other queries in php.
CODE$num = 1;
$q = mysql_query('SELECT * FROM lists');
while($r = mysql_fetch_assoc($q))
{

 if($r['account']=='accountNum')
 {
 print 'You are at pos. num '.$num.' ';
 }
 $num++;
}

Why Does MySQL So Slow
I just changed to use MySQL few days ago but it was a bad idea. My server now is running very slowly with the database. I'm using Perl5 and DBD::Mysql in my script. The system is Linux9, Apache2.

I looked at these mysql pid and saw a lot of activities (about 400) while there are more 100 users online at this moment and lots of running under a the same pid number.

MySQL Slow Log
I have the long query time set to 15 yet MySQL is still showing results with a query time of 0 in the slow query log.

It says enter time in "seconds" in the MySQL Administrator but did it mean in milliseconds??

Slow MySQL
mySQL has been running very slowly and I am getting errors. First I did 2 things I raised the ServerLimit number (apache) to allow for more connections, I also raised the max conncetions in my.cnf. I do not know if this took effect? That should have worked. But basically in phpmyadmin i get this error frequently. I am getting more traffic so I think it is that.

MySQL said: Documentation
#2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)

Ajax Performance With Mysql?
I'm trying to tweak the performance of a php script, but I don't understand how mysql handles connections, so I'm not sure if I should use standard sql statments, prepared statements or stored procedures.

The php script is called by an ajax file, which will be executed very often. Right now each time the script is called I create a new connection (username, password, etc...) and at the end of the script I specifically close it (mysqli_close($con)).

Profile Performance On MySQL
I'm looking for recommendations on an app/function of mysql that will show a profile of the performance of my interaction with MySQL.

I'm a newb to php/MySQL, but not to programming/db. I'm about to launch a site and I'd like to look at a profile of the queries I'm running and if they are using indexes properly/doing full table scans, and other performance related items.

I've read up on the explain and benchmark functions, but they seem to require that the query is passed through them.

I'd prefer something more global like MS SQL's profiler that captures all of the actions, time expended and execution plan. Then I can look through it and see if any of the actions are taking longer than x time and then investigate further.

Does anything like this exist?

FYI, I'm on a shared hosted db server.

MYSQL Performance Issue
The following prg keeps cpu usage as 79% always .When i kill/stop the
prg,then cpu comes down.

main()
{
//code for mysql conn
while(1)
mysql_real_query (&mysqlcon, "SET CHARACTER SET 'utf8';", 25);
}

What might be the reason? Wil it be any config issues?

Mysql Query Performance
Basically, I need to exectute a query, when I execute the first query (with left join), it takes nearlly 4 mins 29.88 sec and the same query (with right join), it takes nearlly 0.22 sec.

I know I might be wrong somewhere....

MySQL Tables Performance
I have a table where misc data are stored. Right now this table has
about 30 columns, but for sure it will be more in the near future. So I
wonder how the big number (50-100) of table's columns affect for MySQL
DB performance. Maybe it's better to create more tables rather than more
table's columns ??

Optimizing MySQL For Performance
I am going to be running mySQL on a RAID5 array and am wondering if I
need to worry about splitting up the data and log files on to seperate
channels on the RAID array.

I have heard differing arguments. Some people say that you can install
SQL on the same logial drive as long as you are running RAID5 and not
worry about performance issues.

Others say that you need to split everything up even if you are
running RAID5.

Do I need to split it up in to 2 channels and 2 logical drives or is
one drive OK?

MySQL Performance Issue
Im running mysql4.0.13 on FrreBSD 5.1-release. The problem is MySQL is using 95% of the cpu leaving 0% idle and pushing the load avg up to > 1. This is the output of mysqladmin processlist..

$ mysqladmin -uroot -p processlist;
Enter password:
+--------+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+------+-----------+----+---------+------+-------+------------------+
| 387980 | root | localhost | | Query | 0 | | show processlist |
+--------+------+-----------+----+---------+------+-------+------------------+
$ uptime
8:51PM up 82 days, 5:18, 2 users, load averages: 1.03, 1.07, 1.23

I normally would expect a slow query or a table that requires optimisation, but without any hints from the processlist, where can i start looking?

Problem With MYSQL Performance With ASP.NET
Large table performance problem with MYSQL performance with ASP.NET

I am doing some benchmarking of an application using MYSQL under
Windows and I confused about the results.

Environment
Emachine 6417 with amd3200+ 64 bit processor 3.3GB ram

mysql 4.1 with odbc 3.51 MYISAM
windows 2003 server std edition
date, account and invoice number are indexed
Database size 18 million rows

I am querying (selecting) columns of a date and an account

our tester program that opens a socket to the Mysql database and does a
select for the above n times
each time the date and the account is randomized to minimize hits on
records closeby.
This program will perform over 1000 queries per second.
At the end, the Mysql socket will be closed

When I enter a similar query manually a web interface, I get about 3
second response time.
This program opens/closes a socket for each query

Does anyone have any suggestions

Your assistance would be greatly appreciated

Also in production, this table will be accesse for both read and write
will I have problems. My testing showed that Innodb is much slower.

Analysis MySQL Performance
What is the best way to analayse the performance of an operational MySQL server?
Are there any good stats programs out there that show more than the variabels listed by MySQL (And phpMyAdmin)?
Are there any good tutorials about what figures to be alert for, and what should be ignored?In particular I'm facing a number of connections issue and need to isolate what is causing so many connections, and why so many stay in sleep mode.Database amostly use PEAR to connect to MySQL, are there any common pitfalls with PEAR and numerours connections?

MySQL Performance Monitor
Wanting to do some performance testing of MySQL on my Windows 2003 Server, and was wondering if there was anyway to setup some basic MySQL performance counters to monitor and log in perfmon? Things like queries per second, connections, traffic, etc? Doesn't have to be a permon counter, just something that can log usage values to a CSV at one second intervals

MySQL Performance Maintenance
can anyone please tell me how I keep the performance of the data within my MySQL database optimized? Ie, will the data over time become defragmented and therefore need to be optimized via (for example) an unload process, then a sort then a reload?
Most of my selects will need to order the data in alphabetical order (I am storing business names and addresses and returning the data in business name order) and I was wonderring if I needed to perform some process to reorder and reload the data periodically to maintain performance. I am expecting approximately 8000 rows of data.

MySQL Performance Question..
I'm running a server with a Pentium 133 w/32meg ram, 512 pipeline burst,
with a wd 512MB HD and I want to store George Bush in our MySQL database.
As far as table definitions are concerned, should I use a BLOB or should I
store him on disk and make a reference to the physical location in the MySQL
table instead?
Will I run into any performance/storage issues when querying this data?

Slow Response From MySQL
I was wondering if any one can help me out, I am literally tearing my
hair out with an availability search I have written.

Previously I was selecting all the records from two databases but
noticed the response time was very slow anything up to 20 secs.

I tried to streamline the search by only selecting the columns I
needed from the tables and created indexes for each of the tables for
the required rows but now the results are executing in around 50 - 60
secs, which is ultimately alot slower.

The SQL query I am using at the moment is this:

SELECT villas.id, villas.resort, villas.beds, villas.owner,
villas.air_con, villas.walk_beach, villas.walk_shop, pricing.id,
pricing.week, pricing.price, pricing.availability FROM villas LEFT
OUTER JOIN pricing ON villas.id = pricing.id WHERE pricing.week =
1073088000 AND pricing.availability = 1 AND villas.beds > 0 AND
pricing.price > 0 AND ( villas.resort = 'cala_dor' OR villas.resort =
'pollenca' ) GROUP BY villas.id ORDER BY villas.owner DESC ,
villas.beds ASC

MySQL Slow In Windows NT4
I am running the Apache web server, MySQL v4, and PHP on an NT4 server.
Apache runs great, but the auction software I am using (Web2035 Auction
software written in PHP) is very, very slow.

Sometimes it takes 20-30 seconds to bring up an auction page from the items
table which has less than 200 records in it.

Can anyone can give me some pointers on where to start looking? (I don't
know if the bottleneck is with MySQL or PHP or what I might need to look at
to enhance the performance of either package.)

MySQL Is Slow, Trying To Optimize
It just seems that my system is slow, adding records, etc. I tried to optimize it, saw that there was a my-medium.ini file, read that is was for medium sized system. I replaced it with my.ini (yes I did make a backup, thankfully) and restarted mysql.

Pretty much it hangs. I tried to connect with QueryBrower, did a Select Count(*) and just froze. I admit that I didn't wait for ever, yes I know I Select Count(*) takes a long time but I gave up after waiting 3x as long as I normal did. Also the logs screen froze in administrator.