Alfredo,
It seems funny to me that with 2 servers dealing with 2 sets of data
streams at the same time that you didn't see a performance boost. This is
leading me to think that it's either the application or the SCSI controller
not keeping up.
It makes sense that if you open up a second server that writes to its own
disk that you should get twice the performance if you were Disk R/W bound
(physical response time from the HD) but your second benchmark didn't show
that. Since you were never CPU bound, it can't be the servers themselves
acting as the bottleneck. It has been my experience that if your server is
not maxed out and your disks are not maxed out, that leaves "communication"
and "control" as the remaining suspects.
"Communication" includes the requests from daemon to server and the
server's responses. You already said that your network traffic was not a
factor. "Communication" also involves from the server to the HD (via the
SCSI controller). If you have a single-channel controller, it may be
waiting for one disk to finish writing before telling the other one what to
do. Check to see if your drives each have their own channel or if they
share one. If possible, can you put each drive on their own controller?
"Control" is your daemon application. Can it process enough traffic to
write 35,000+ insert commands per second? If not, there is your culprit.
Have it process your test data again and instead of writing the commands to
the servers, write them to a file. What is the maximum throughput on your
application when writing to a file? Try running top on your daemon server,
too.
I agree with Leonardo's suggestion. Try to determine the max rate of data
inserts for your database servers by using a data file local to the
servers. I would test both of your server instances. Test them alone and
together to see if there is a noticeable difference in the write speed.
Two other things you may be able to try: Modify your daemon application to
use Unix Sockets and run it on the same machine as your database servers.
See if you can compile your application to a stand-alone executable.
I think we are all curious to find out what works for you and what doesn't.
I hope the MySQL developers are monitoring this thread as they could have
better theories than mine.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"Leonardo
Francalanci" To: <[EMAIL PROTECTED]>
<[EMAIL PROTECTED] cc:
tel.ie> Fax to:
Subject: R: info about MySQL Server
06/25/2004 11:27
AM
try removing your client for the moment:
can you try a load data infile and see at what speed mysql loads
the rows?
> -----Messaggio originale-----
> Da: Alfredo Marchini [mailto:[EMAIL PROTECTED]
> Inviato: venerd́ 25 giugno 2004 18.21
> A: [EMAIL PROTECTED]
> Cc: [EMAIL PROTECTED];
> [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Oggetto: Re: info about MySQL Server
>
>
> Hi,
> thank you very much for your reply.
>
> Now I answer you about your additional questions:
>
> Now the daemon is installed on a machine different than DB Server, so I
> use DBServer NIC IP Address for connect to it from the daemon (So I don't
> use Unix Sockets).
>
> But, looking at the Network Bandwidth, there isn't bottleneck (but I have
> read that Unix Socket are more quickly).
>
> My SCSI HD are not in RAID. Actually are fully indipendent and works on a
> RAID controller 160Mb/s (the same speed of the disks).
>
> Probably in the future I'll use RAID tech that improves performance
> (RAID-5, RAID-0+1), but not in this beginning step.
>
> J2SE project source compiled becomes a bytecode (not exe and not source);
> this is runnable only on a Java Virtual Machine.... (It is like
> half-compiled).
>
> Before decide to develope in J2SE I have made some tests about
performance
> differences between JDBC and MySQL client API....
>
> MySQL client API obviously are more quickly, but no so much. Also I have
> read documentations about the MySQL JDBC Connector that are the best Java
> Native DB Connector (also better than other famous DBMS JDBC).
>
> Here is MYISAM table structure.....
>
> create table pippo
> (
> type varchar(10) not null,
> time_1 varchar(8) not null,
> value decimal(8,2) not null,
> state char(2) not null,
> primary key (type, time_1)
> )
> type = MyIsam;
>
> How you can see the table structure is very simple....
>
> The MotherBoard is a GigaByte GA-7VA.
>
> here is the link about it:
>
> http://tw.giga-byte.com/MotherBoard/Products/Products_GA-7VA.htm
>
> Red Hat 9.B info:
>
> the kernel version is 2.4.20-20.9 i686.
>
> on the os are installed and run only
>
> 2 MySQL server,sshd,syslog,cron,atd,anacron.
>
> When I boot the machine, I have only 40Mb Ram used.
>
> All It seems ok.
>
> I don't have anymore to say...
>
> If you need other info, I am here....
> Thank you very much again.
>
> Best Regards
> Alfredo Marchini
> Developer Manager
> SysNet Solutions S.r.l.
> Via Rimini, 49 - Centro Dir. Leonardo da Vinci
> 59100 Prato (PO) - Italy
> Tel. +390574/484822 - Fax +390574/448075
>
> >
> > Alfredo,
> >
> > I have just a few additional questions that you did not already answer
> in
> > your very well-formed post. I normally wouldn't worry about ANY of
these
> issues but you seem to be exploring the envelope of performance on your
> system and every little bit counts:
> >
> > Which communication path are you using to connect to your MySQL servers
> from your application (TCP/IP or sockets or ...)? If TCP/IP are you
> pointing to the IP address of your NIC or to 127.0.0.1?
> >
> > Are your HD's in a RAID configuration? If so which one?
> >
> > Could you post the results of SHOW CREATE TABLE so that we can see what
> indexes, if any, you have on your data?
> >
> > Are there any other applications running on the same machine? I know
you
> said your CPU usage didn't pass 40% but am looking for disk I/O
> congestion
> > or data bus congestion or some other resource contention.
> >
> > Is your application compiled or interpreted? I am not familiar with
J2SE
> but I do know that some Java-based implementations give you the option
> to
> > compile applications into either a stand-alone executable or to object
> code. Object code is interpreted by the Java runtime engine during
> execution which may be slowing you down.
> >
> > (as a last resort) What is the speed of the data bus on your
> motherboard?
> >
> > Thank you for your patience,
> > Shawn Green
> > Database Administrator
> > Unimin Corporation - Spruce Pine
> >
> >
> >
> >
> >
> > "Alfredo Marchini"
> > <[EMAIL PROTECTED] To:
> > [EMAIL PROTECTED]
> > lutions.it> cc:
> > [EMAIL PROTECTED]
> > Fax to:
> > 06/25/2004 03:24 AM Subject: info
> > about MySQL Server
> > Please respond to
> > alfredo.marchini
> >
> >
> >
> >
> >
> >
> > Dear Sir,
> >
> > Sergei Golubchik gives me this e-mail address telling me that you can
> help
> > me about a project that my company is developing.
> >
> > we need to develope a J2SE project that works on N MYSQL 4.0.20 servers
> installed on the same machine.
> >
> > Now I try to explain my problem.
> >
> > The Hardware, actually, is a normal PC:
> >
> > CPU AMD ATHLON XP 2000+
> > RAM 770Mb DDR
> > 2 * HD SCSI 160Mb/s.
> >
> > With a Red Hat Linux 9.B installed.
> >
> > Now (the beginning) I use only 2 MYSQL server, one for SCSI disk.
> >
> > The two MYSQL servers are totally indipendent.
> >
> > In either of these, I have created a database, and a MYISAM table. The
> size of one record is 30 bytes and there is only an index made by two
> fields (varchar(8) and varchar(10)).
> >
> > I need to insert in this table-structrure an amount of data that have
to
> be, at least, the size of 35.000 records for second, 24 hours for day,
> 31
> > days for month.
> >
> > I reached, using only one MYSQL server, 20.000 records for second.
> >
> > Installing two services I hoped that I could reach 40.000 records for
> second (or some minus) because MySQL servers should work in paraller
> mode.
> >
> > But it isn't the truth, the speed doesn't increment.
> >
> > the J2SE project use multithreading technology, and is like a MySQL
> servers load-balancer.
> >
> > Looking at the performance (with top), I see that the CPU never goes
> over
> > 40%, and the RAM is about 40%.
> > These performance are similar using only one MySQL server or two MySQL
> servers.
> >
> > I tried to correctly set MySQL system variables, I also tried to do an
> higher priority to MySQL server processes, but the performance are the
> same.
> > Another strange thing is the thread_concurrencing running on MySQL
> servers, looking at the status, threads running value never oltrepass 2,
> and I set 8 thread_concurrency.
> >
> > Now I explain you how the final project should be:
> >
> > there is a J2SE software that runs as a daemon, it listen to a TCP
port.
> this daemon is connected (as a client with JConnector 3.0.14) to N MySQL
> servers on a DBServer (at least 2 MYSQL services).
> >
> > the DBServer will be a very powerful server (because my customer needs
> more than 35.000 records for second and many clients connected to the
> daemon).
> >
> > A client connected to the daemon can do usually two things:
> >
> > asks the daemon to do insert data in database, or/and read data from
> database.
> > (So the daeamon uses only SELECT .. FROM ... WHERE ... LIMIT ... UNION
> and
> > INSERT INTO ... VALUES (),(),()).
> >
> > The client data requests are sent to the daemon that launchs queries on
> the N MySQL Servers (as a balancer) and eventually gives the replies.
> >
> > The data have to be in real time (so I can't use INSERT DELAYED).
> >
> > the speed of the inserts have to be maximum as possible (at least
35.000
> records for second).
> >
> > During the insert operations another clients, in the same moment, could
> ask the daemon to give him the data inserted in a period.
> >
> > usually the data have about a size of 70Mb (1 day data), and the
maximum
> request is to give data of one entire month.
> >
> > More Clients can do these types of requests.
> >
> > Obviously I will create 4 tables for a day for either MySQL service.
Now
> I
> > don't know if I'll use MySQL commands (MERGE, RAID or others) or I'll
do
> all from my daemon (I have to do test to take this decision).
> >
> > This is all....
> >
> > In your MySQL doc, I read that you have tested MySQL for a big Sweden
> company, but you have not mentioned anything about the size of the data
> that
> > this company store, the clients connected, the size of the queries or
> the
> > number of inserts.
> >
> > First I would like to know if MySQL could reach these high
> performance...
> >
> > Because my actually problem is the Hardware resource that aren't full
> using my little PC.
> > Why have I to buy a powerful server If this hardware isn't full used?
> The
> > bottleneck isn't in the SCSI disk (I installed MYSQL servers on RAMDISK
> partitions with the same results).
> > The bottleneck I think is MySQL, but I also think that it is well
> configured.
> >
> > Here is my.cnf file (I have two equals configurations files, one for
> MySQL
> > server)
> >
> > port = 330[0-1]
> > socket = /ptolle/ssc/db/mysql_[0-1].sock
> > key_buffer = 256M
> > max_allowed_packet = 1M
> > table_cache = 512
> > sort_buffer_size = 70M
> > read_buffer_size = 70M
> > query_cache_size = 70M
> > thread_cache = 8
> > thread_concurrency = 8 ---> no more than 2 threads run!!!
> > max-connections = 200
> > skip-innodb
> >
> > Finally I tried also to compile the MySQL tarball with the gcc (the
> version is 3.2.2-5) compiler parameters that are mentioned in your
> documentation without better results.
> >
> >
> > have you got any parameters that limit MySQL performance (in the header
> libraries or others)?
> >
> > or have you got some informations about MySQL configuration that could
> speed up MySQL or bring MySQL services to work in paraller mode and use
> all hardware resource avalaible?
> >
> > Thank you very much.
> > Best Regards
> >
> >
> > Alfredo Marchini
> > Developer Manager
> > SysNet Solutions S.r.l.
> > Via Rimini, 49 - Centro Dir. Leonardo da Vinci
> > 59100 Prato (PO)
> > Tel. 0574/484822 - Fax 0574/448075
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
> >
> >
> >
> >
> >
> >
> >
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
