Perfomance Tuning

Perfomance Tuning
I have just installed redhat linux 9 which ships with mysql
3.23.56. Mysql has to be setup so that it can use innodb tables,
and data inserts (blobs) should be able to handle at least 8M
at a time. The machine has two P III 933MHz CPU's,
1.128G RAM (512M*2 + 128M), and a 36 Gig hd with 1 Gig swap and
3 equal size ext3 partitions. What would be the recomended
setup for good performance considering that the db will have
about 15 users for 9 hours in a day, and about 10 or so users
throughout the day who wont be conistenly using the db.

My configuration looks like this so far:

++++++++++++++++/etc/mysql.cnf++++++++++++++++++++++++++++++++
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
max_allowed_packet=16M
#InnoDB
innodb_data_file_path = ibdata/ibdata1:2000M:autoextend
innodb_data_home_dir = /var/lib/mysql


[mysql.server]
user=mysql
basedir=/var/lib


[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
++++++++++++++++/etc/mysql.cnf++++++++++++++++++++++++++++++++

---

Poor Perfomance
I have three tables that I am joining in order to get the query results I want. Each table has roughly 50,000 records. When I first ran the query I got the result in 5 sec (I have the appropriate indexes in place). I inserted about 100 records in each table the same query takes more than 1 minute to run. I made no other change to the databases. Any ideas on how to get the performance I initially had? Is there any memory management I can do to restore performance.

---

Delete Perfomance Issues
Does MySQL use available Indexes to perform Deletion of Multiple Rows or does it do a Table Trawl, Table Size 1.9 Million Records with 3 Indexes. I am trying to justify using MySQL instead of MSSQL for our main Database and MySQL is loosing out big time against MSSQL in my enviroment.

---

Optimize And Tuning
i was reading a post from long ago, and suggest for better performance at the mysql server to check the queries adn then make an optimization. i have this db that's eating lots of resources, and usually reaches 90% for the cpu and almost everything is used by the mysql.

i supposse it´s a matter of tuning vars, wich one would you suggest to tackle first, and what others next in an myisam engine

---

Server Tuning
I run MySQL version 3.23.41 with RedHat v7.2 ... lately I noticed that our server with 768MB is running constantly at between 95% - 98% memory usage ... my question is, are there any generic tuning tips I can apply to reduce this ... e.g. can I direct MySQL to use disk area instead of main memory.
Although I do not notice the actual database applications running slowly, other applications do not appear to be running as quickly or as stable as previously.

---

Monitor / Tuning Of MYSQL
Can anyone tell me of documention/tutorials/software for monitoring and or tuning MYSQL databases. We currently run something called foglight (Quest Software) which tracks and monitors our Oracle Instances (sends us emails about things). I think we can code stuff to monitor the MYSQL databases that we are due to take over at some point, however I am not quite sure what to look for.

---

Performance Tuning For Inserts
i am inserting, usually, about 1-5 million records into a 50-350 million (depends on
which DB i'm using) DB. The table has about 75 columns in it. I am getting, on average,
processing time of about 6 seconds per thousand which is unacceptable. I need to
speed this process up. Right now i am building a string in Java with "insert into
<tableName> values(.......);" but I'm sure there's gotta be a faster way. Would it
be faster to write all the additions to a seperate file and then load it using 'LOAD
DATA LOCAL INFILE...'?

---

Tuning MySQL To Beat MS Access
I use MS Access to analyze survey data and I'm hoping to migrate to MySQL. I'm currently running MySQL 4.1 on a Windows 2000 multi-function machine.

The following query has one of the simplest structures I would ever use, so I'm using it as a speed test before using MySQL for my much more complicated SELECT queries (as many as 45 joins, mostly using the same tables as below):

SELECT count(r.r_id) FROM (r INNER JOIN q ON r.q_id = q.q_id) INNER JOIN a ON r.a_id=a.a_id;

Full SHOW CREATE TABLE and SHOW INDEX FROM output for these tables is below.

MS Access runs this query in just under 2 seconds.

At first, MySQL took about 20 minutes. Indexing the bejeezus out of everything got it down to about 6 minutes; running ANALYZE TABLE on everything got it down to 2.5 minutes; and switching all the tables from InnoDB to MyISAM (duh) knocked it down to 7 seconds. Code:

---

MySQL Slow With 4GB Memory - Tuning Required?
I am using MySQL 4.1 on Linux (IBM Open Power Server). The Server has 4GB memory, 380GB hard drive.

I have a Java based application that runs in Tomcat in a Windows environment using SQL Server. As part of a customer project I have got the application to work on Tomcat on Linux with MySQL.

Both databases have a large amount of data in some of the tables, e.g. 500,000+ records in some of the tables. Some of the tables have indexes on them.

There is a specifc part of the application that is slow when running against MySQL (10 seconds to write a record to a table with 500,000+ records in it.) The same bit of functionality running against SQL Server is instance, e.g. 2 secomds max. The code is doing an insert statement. Once the insert has taken place we need to get the ID for the inserted record. This is using SELECT MAX....... The ID column is set as the Primary Key and is indexed. Code:

---

InnoDB: Right For Write-heavy Table? Server Tuning?
I've decided to merge 4500 identical tables into one. They were previously partitioned with one table per user of the system, but I'm imagining I may not need to do this anymore for this particular table. I'd like to do so to reduce the number of files in this database's directory (currently over 15,000), to reduce backup complexity, and maybe improve performance. That's where I'm still unsure.

This is the summary table for W3Counter with fields website_id, date, unique_visits, return_visits, page_views. The website_id and date uniquely identify each row.

It is a write-heavy table (probably more than 99% INSERT/UPDATE compared to SELECT). I may be making things up, but I believe I remember InnoDB showing superior performance for this type of table. I'd imagine row-level locking would decrease lock contention when being hit by dozens of threads concurrently -- queries trying to update different rows would be allowed since that row isn't locked, unlike MyISAM where the entire table is locked by each query.

Merging all the tables into this one would mean starting with about 1.5 million rows and growing about 4500 per day.

So what do you think? Is InnoDB the right way to go for this table? Should I expect better performance than MyISAM for the inserts/updates? Should I expect the same or better performance on SELECTs that SUM() those counts over various date ranges, and sometimes group by WEEK(), MONTH() or YEAR() on the date column? I'm not sure whether the primary key is sufficient for that type of query, if a key on just the website_id would help those, or what. Any opinions?

My other reason for this thread is to get some pointers on tuning the server variables for InnoDB tables. There's much less written about this than those mainly applicable to MyISAM tables. Which are most important for a table like this? Right now no other tables will be InnoDB as the rest benefit greatly from MyISAM-specific features (like prefix compression on indexes and maintenance of row counts).

---